Re: [SQL] Request for builtin function: Double_quote
Josh, I'm not sure what you mean by 'builtin C function'. There is one already size_t PQescapeString (char *to, const char *from, size_t length); Or do you mean a String Function like substring(string [from integer] [for integer]) I would rather call it 'builtin sql function'. Regards, Christoph > > Folks, > > Given the amount of qoute nesting we do in Postgres, I thought that we need a > function that handles automatic doubling of quotes within strings. I've > written one in PL/pgSQL (below). I'd really love to see this turned into a > builtin C function. > > -Josh > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] rowtype and ecpg
What exactly is your problem? Calling a plpgsql function from within a C program or retrieving varchar data within a C program? Regards, Christoph > > I have a function that takes as a parameter ROWTYPE: > > create or replace function test_func(test_table) > returns varchar as ' > declare >lv_return varchar; > begin >.. >return lv_return; > end; > ' LANGUAGE 'plpgsql'; > > How do I call this function from the C program (ecpg)? How my > declaration should look like? > > I trued structure and got error: Too many arguments > > Thanks for your help ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Double quotes?
The information you gave is not very helpful. An INSERT command, which caused the error, would help. Anyway, did you enclose the text by single quotes? AFAIK, you do not have to escape double quotes. Regards, Christoph > > Hi everyone, > > I have a column with type text and am trying to pump in a larger amount of > text that has double quotes and also commas in between. I got an error > when I tried to do a simple INSERT. I then tried to escape the double > quote with a backslash but that didn't work. The manual for the 7.1 > database says that I can escape charactes by using the \xxx where xxx is > the octal value of the character. Is this the only way or have I missed > out on something simple and just keep making a small mistake? > > Thanks in advance, > > Archie > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Problem on PostgreSQL (error code, store procedures)
> > I would like to know if there are any global variables > storing the error code or the number of rows affected > after each execution of the SQL statement. You did not mention which interface you are using. In C there are functions available like extern const char *PQcmdTuples(PGresult *res); to learn about the rows affected or extern int PQntuples(PGresult *res); to learn about the number of rows found (SELECT) or extern ExecStatusType PQresultStatus(PGresult *res); to learn about the status of the SQL command. Unfortunately, there are no error codes at all. In PL/pgSQL (which I do not use) I've found GET DIAGNOSTICS variable = ROW_COUNT ; refer to plpgsql-statements.html > > Also, for Postgresql function, I have checked that I > can only call the function by: select function(parms) > or by EXECUTE PROCEDURE function(parms) in the trigger > action. I wonder whether there is any other method for > calling the function which is similar to the stored > procedures in the MS SQL server? I'm afraid NO. Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Variables in PSQL
> > I'm trying to declare a variable in PostgreSQL, so I can save some values in > it. After, I want to calculate with this variable. > For example: > > declare vp integer; > select price into :vp from article where anr = 1; > vp := vp + 1; > update article set price = :vp where anr = 1; AFAIK, you can do stuff like this only within a function. > > Is there a posibility to do that without creating a funktion? What about update article set price = (select price + 1 from article where anr = 1) where anr = 1; Regards, Christoph ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] SQL problem with aggregate functions.
> > I've got a table in which there is a field that can have one amongst 3 > possible values : D, R, X. Is it possible to get in one query the count of > this different values.Please, note that I don't want to have a querry like > this : > "select count (*) from tab group by f1;", cause i want to get all the possible > count values in one row (these data are already grouped on another field). > To give a more accurate example, here is what I want to retrieve : > > Field group | count of D | count of R | count of X. > > Any clues ? > -- What about something like SELECT SUM(f1_d) AS count_d, SUM(f1_r) AS count_r, SUM(f1_x) AS count_x FROM ( SELECT CASE WHEN f1 = 'D' THEN 1 ELSE 0 END AS f1_d, CASE WHEN f1 = 'R' THEN 1 ELSE 0 END AS f1_r, CASE WHEN f1 = 'X' THEN 1 ELSE 0 END AS f1_x FROM tab ) AS foo ; Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] list of tables ?
> > can anyone point me in the right direction ? > > i need to list all the tables in a database. > Steve, Your request reminds me of a similar problem I had. Try the following: CREATE VIEW sesql_usertables AS SELECT UPPER(u.usename) AS tbl_owner, UPPER(c.relname) AS tbl_name, UPPER(a.attname) AS col_name, a.atttypid AS col_type, INT4LARGER(a.attlen, a.atttypmod - 4) AS col_length, CASE WHEN a.attnotnull=TRUE THEN 0 ELSE 1 END AS col_null, a.attnum AS col_seq, CASE WHEN EXISTS(SELECT adsrc FROM pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum) THEN 1 ELSE 0 END AS COL_DEFAULT FROM pg_attribute a, pg_class c LEFT JOIN pg_user u ON (u.usesysid = c.relowner) WHERE c.oid = a.attrelid AND NOT (c.relname ~* 'pg_') AND c.relkind = 'r' AND a.attnum > 0 ; SELECT * FROM sesql_usertables ORDER BY tbl_owner, tbl_name, col_seq ; It should give at least some ideas how to retrieve information from all the tables in a database. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to find out if an index is unique?
Dirk, May be you can use this create view sesql_userindexes as SELECT upper(c.relname) AS TBL_NAME, upper(i.relname) AS IDX_NAME, CASE WHEN x.indisunique=false THEN 0 ELSE 1 END AS UNIQUE_FLAG, 1+ (CASE WHEN x.indkey[1]=0 THEN 0 ELSE 1 END)+(CASE WHEN x.indkey[2]=0 THEN 0 ELSE 1 END)+ (CASE WHEN x.indkey[3]=0 THEN 0 ELSE 1 END)+(CASE WHEN x.indkey[4]=0 THEN 0 ELSE 1 END)+ (CASE WHEN x.indkey[5]=0 THEN 0 ELSE 1 END)+(CASE WHEN x.indkey[6]=0 THEN 0 ELSE 1 END)+ (CASE WHEN x.indkey[7]=0 THEN 0 ELSE 1 END) AS IDXCOL_TOTAL, x.indkey AS COL_SEQ FROM pg_index x, pg_class c, pg_class i WHERE ((c.oid = x.indrelid) AND (i.oid = x.indexrelid)) and not (c.relname ~* 'pg_') ; select * from sesql_userindexes order by tbl_name, idx_name ; gives you detailed information about all user-defined indices. Regards, Christoph > > is there a way to ask the system tables if a given index was created > with the unique qualification? I don't want to insert data to try. > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] determine if a table exists
> > how do i determine if a table exists using select statement > i want to find out if it exists if not ill create it if yes then ill do > some editing with it > TIA > do SELECT relname FROM pg_class WHERE relkind='r' AND relname !~ '^pg_' AND relname !~ '^pga_' AND relname !~ '^pgadmin_'; You should also refer to Chapter 3. System Catalogs in the Documentation. Regards, Christoph ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
> > Christoph Haller wrote: > > Hi pgsql-sql list, > > I did some testing around tables using a column > > timestamp with time zone not null default now(). > > I have noticed a valuable feature: > > As long as being inside a transaction initiated by > > begin; > > the return value of the now() function does not change. > > Is this intended - as written in the documentation? > > Can I rely on it, so future releases of PostgreSQL > > will act the same. > > Yes, this is intended and will not change. > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > I vote the now() behaviour not to change. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL Error
> CREATE TABLE "scheduler_action" (
>"scheduler_action_id" numeric(30) NOT NULL,
>"action_type" numeric(4) NOT NULL,
>"priority" numeric(4) NOT NULL,
>"referenced_id"numeric(30) NOT NULL,
>"invocation_date" numeric(30) NOT NULL,
>"is_done" numeric(1) NOT NULL,
>PRIMARY KEY ("scheduler_action_id")
> );
>
> My quety look so:
>
> select t0_o.scheduler_action_id from scheduler_action t0_o where
> t0_o.is_done = 0 and t0_o.invocation_date <= 1034033214921
>
> And I get following error:
>
> ERROR: Unable to identify an operator '<=' for types 'numeric' and
'double
> precision'
> You will have to retype this query using an explicit cast
>
> But if my query looks like:
>
> select t0_o.scheduler_action_id from scheduler_action t0_o where
> t0_o.is_done = 0 and t0_o.invocation_date <= '1034033214921'
>
> "invocation_date" is numeric with p=30 and s=0.
>
I've got the same error message on
select version();
version
---
PostgreSQL 7.2.1 on hppa-hp-hpux10.20, compiled by GCC 2.95.2
but
select t0_o.scheduler_action_id from scheduler_action t0_o where
template1-# t0_o.is_done = 0 and t0_o.invocation_date <=
1034033214921::numeric ;
did work;
I cannot see why this is necessary, and I cannot understand why nobody
else
replied to both of your requests.
Regards, Christoph
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] if exists (select * from ...
I've seen statements like this if exists (select * from pg_tables where tablename = 'http://archives.postgresql.org
Re: [SQL] Locking that will delayed a SELECT
>Suppose I have a transaction (T1) which executes a > complicated stored procedure. While T1 is executing, > trasaction #2 (T2) begins to execute. > > T1 take more time to execute that T2 in such a way > that T2 finished earlier than T1. The result is that > t2 returns set of data before it can be modified by > T1. > >Given the above scenario. Is there a way such that > while T2 will only read that value updated by T1 (i.e. > T2 must wait until T1 is finished) ? What locks should > I used since a portion of T1 contains SELECT > statements? Should I used the "SERIALIZABLE > isolation". What's wrong about this question? I'm interested in an answer, too. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] 'next' or similar in plpgsql
> > In perl we have 'next' function to skip rest of the statements in the loop > and to start with next iteration. In plpgsql, do we have something > similar? How do we skip rest of the statements in a loop in plpgsql? > The only statement which can be used in a probably tricky way seems to be EXIT [ label ] [ WHEN expression ]; What you are really looking for is something like the C statement "continue;" I'm quite surprised this is not available in plpgsql. Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Accumulated sums in SQL query
> Which is the simplest way to create an SQL query to get accumulated sums of > records like this (from the table containing the numbers): > > numbersums > --- > 1 1 > 2 3 > 3 6 > 4 10 > SELECT number, SUM(your_sum_column) FROM your_table GROUP BY number ; Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Accumulated sums in SQL query
>
> OK I have a table named bank_account_movements containing two columns
=
> date and amount:
>
> date amount (in USD)
> -
> 2002-10-01 20
> 2002-10-02 30
> 2002-10-03 -15
> 2002-10-04 -5
> 2002-10-05 -3
> 2002-10-0610
>
> my goal is to create a view from it adding an extra column named =
> balance!
>
> date amount (in USD) balance
> ---
> 2002-10-01 20 20
> 2002-10-02 30 50
> 2002-10-03 -1535
> 2002-10-04 -5 30
> 2002-10-05 -3 27
> 2002-10-0610 17
>
> The balance is 0+20=20, 0+20+30=50, 0+20+30-15=35 and so on...
> how would you write the SQL query?
>
My first approach is write a small plpgsql function
(based on the table definition below) like
CREATE TABLE amountlist (date TIMESTAMP,amount INTEGER);
INSERT INTO amountlist VALUES ('2002-10-01 00:00:00', 20 ) ;
INSERT INTO amountlist VALUES ('2002-10-02 00:00:00', 30 ) ;
INSERT INTO amountlist VALUES ('2002-10-03 00:00:00',-15 ) ;
INSERT INTO amountlist VALUES ('2002-10-04 00:00:00', -5 ) ;
INSERT INTO amountlist VALUES ('2002-10-05 00:00:00', -3 ) ;
INSERT INTO amountlist VALUES ('2002-10-06 00:00:00', 10 ) ;
CREATE FUNCTION calc_balance(TIMESTAMP) RETURNS INTEGER AS '
DECLARE balance INTEGER;
BEGIN
SELECT INTO balance SUM(amount) FROM amountlist WHERE date <= $1 ;
RETURN balance;
END;
' LANGUAGE 'plpgsql' ;
SELECT date,amount,calc_balance(date) FROM amountlist;
date | amount | calc_balance
++--
2002-10-01 00:00:00+02 | 20 | 20
2002-10-02 00:00:00+02 | 30 | 50
2002-10-03 00:00:00+02 |-15 | 35
2002-10-04 00:00:00+02 | -5 | 30
2002-10-05 00:00:00+02 | -3 | 27
2002-10-06 00:00:00+02 | 10 | 37
(6 rows)
Looks like what you are looking for, except the last value which
appears to be a typo.
Regards, Christoph
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] query optimization question
Now that I've given your problem more thoughts (and searched for similar stuff), I think what you need is generating a cross table resp. pivot table. Related to this, I am thinking of a query using Conditional Expressions like COUNT ( CASE WHEN ... THEN 1 ELSE NULL) in order to use GROUP BY. Together with Richard's idea of using a function age_range(date) it seems realizable. I'm not yet ready to make a more detailed proposal, but you might want to think about it in the meantime, too. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] query optimization question
> SELECT
> project_id,
> marketing_name,
> COUNT(lots.lot_id) AS def_count,
> COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-07'}
> THEN lots.lot_id ELSE NULL END) AS
def_count_less_30,
> COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-10-07'}
> AND dt.days_old_start_date < {d '2002-09-07'}
> THEN lots.lot_id ELSE NULL END) AS
def_count_30_60,
> COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-09-07'}
> AND dt.days_old_start_date < {d '2002-08-08'}
> THEN lots.lot_id ELSE NULL END) AS
def_count_60_90,
> COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-08-08'}
> THEN lots.lot_id ELSE NULL END) AS
def_count_greater_90,
> COUNT(DISTINCT(CASE WHEN
> dt.days_old_start_date < {d '2002-10-07'}
> THEN lots.lot_id ELSE NULL END )) AS
lot_count_less_30,
> COUNT(DISTINCT(CASE WHEN
> dt.days_old_start_date >= {d '2002-10-07'}
> AND dt.days_old_start_date < {d '2002-09-07'}
> THEN lots.lot_id ELSE NULL END )) AS
lot_count_30_60,
> COUNT(DISTINCT(CASE WHEN
> dt.days_old_start_date >= {d '2002-09-07'}
> AND dt.days_old_start_date < {d '2002-08-08'}
> THEN lots.lot_id ELSE NULL END )) AS
lot_count_60_90,
> COUNT(DISTINCT(CASE WHEN
> dt.days_old_start_date >= {d '2002-08-08'}
> THEN lots.lot_id ELSE NULL END )) AS
lot_count_greater_90,
> COUNT(DISTINCT lots.lot_id) AS lot_count
> FROM
> (SELECT * FROM deficiency_table
> WHERE assigned_supplier_id = '101690') AS dt,
> (SELECT * FROM deficiency_status
> WHERE is_outstanding) AS ds,
> (SELECT * FROM projects
> WHERE division_id = 'GGH') AS proj,
> lots
> WHERE
> dt.lot_id = lots.lot_id
> AND lots.division_id = proj.division_id
> AND lots.project_id = proj.project_id
> AND dt.deficiency_status_id = ds.deficiency_status_id
> AND NOT EXISTS
>(SELECT 1 FROM menu_group_projects
> WHERE menu_code = 'WA'
> AND division_id = proj.division_id
> AND project_id = proj.project_id
> AND status = 'I')
> ORDER BY proj.project_id ;
What about simply replacing ORDER BY proj.project_id ; by
GROUP BY project_id, marketing_name ;
Regards, Christoph
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Generating a cross tab (pivot table)
Regarding to Terry's request on multiple aggregates and Shahbaz's request for generating a cross tab ( pivot table ) in September, I've found an excellent example on a german inet page http://www.itrain.de/ I've translated it and think it's useful for many who subscribed (I hope so, maybe it was told before, but I couldn't find anything about this topic in the techdecs). Objective: There is a relation "sales", holding the sales of different products of different vendors. The task is to generate a report which shows the sales of every vendor and every product. Consider the following table populated with some data: CREATE TABLE sales ( product TEXT, vendor TEXT, sales INTEGER ); INSERT INTO sales VALUES ( 'milk' , 'mr. pink' , 12 ) ; INSERT INTO sales VALUES ( 'milk' , 'mr. brown' , 8 ) ; INSERT INTO sales VALUES ( 'honey' , 'mr. green' , 2 ) ; INSERT INTO sales VALUES ( 'milk' , 'mr. green' , 34 ) ; INSERT INTO sales VALUES ( 'butter', 'mr. pink' , 17 ) ; INSERT INTO sales VALUES ( 'butter', 'mr. brown' , 2 ) ; INSERT INTO sales VALUES ( 'honey' , 'mr. pink' , 19 ) ; The following query generates the report: SELECT product, SUM(CASE vendor WHEN 'mr. pink' THEN sales ELSE 0 END) AS "mr. pink ", SUM(CASE vendor WHEN 'mr. brown' THEN sales ELSE 0 END) AS "mr. brown", SUM(CASE vendor WHEN 'mr. green' THEN sales ELSE 0 END) AS "mr. green", SUM(sales) AS "sum of sales" FROM sales GROUP BY product ; product | mr. pink | mr. brown | mr. green | sum of sales -+---+---+---+-- butter |17 | 2 | 0 | 19 honey |19 | 0 | 2 | 21 milk|12 | 8 |34 | 54 (3 rows) The example is based on MS SQL Server 7.0 and it appears to be there is a valuable feature called CUBE which completes the report. SELECT CASE WHEN GROUPING(product) = 1 THEN 'sum of sales' ELSE product END, SUM(CASE vendor WHEN 'mr. pink' THEN sales ELSE 0 END) AS "mr. pink ", SUM(CASE vendor WHEN 'mr. brown' THEN sales ELSE 0 END) AS "mr. brown", SUM(CASE vendor WHEN 'mr. green' THEN sales ELSE 0 END) AS "mr. green", SUM(sales) AS "sum of sales" FROM sales GROUP BY product WITH CUBE ; product | mr. pink | mr. brown | mr. green | sum of sales --+---+---+---+-- butter |17 | 2 | 0 | 19 honey|19 | 0 | 2 | 21 milk |12 | 8 |34 | 54 sum of sales |48 |10 |36 | 94 (4 rows) I would like to hear from the core team whether they think this feature is worthy to be implemented, or even better, is there a similar one or an easy workaround already. It's obvious this approach is most inflexible. As soon as there is a new vendor, one has to re-write the query and add SUM(CASE vendor WHEN 'mr. new' THEN ... , In an advanced example it is shown how to deal with cross tabs in general using a stored procedure. I am going to translate this and re-write it for postgres, too (ok, I will try). Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] how to get the source table & field name of a view field
> Could you tell me how to get view field's source table and field name? > another word, how could I know the view field come from? Within psql, use \d to learn about the view's column names and types and the view definition. If you were thinking about querying system tables to get this information, start psql with the "-E" option to see how this \d command is implemented. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] PLpgSQL FOR IN EXECUTE question
Consider the following PLpgSQL code fragment FOR this_record IN EXECUTE ''SELECT '' || quote_ident($1) || ''FROM '' || quote_ident($2) LOOP list := list || '', '' || this_record.$1 ; END LOOP; As expected, accessing a field via this_record.$1 does not work. Can it be done otherwise? Regards, Christoph ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] how to get the source table & field name of a view field
> > if I want to write a function for getting the view.field's source > table.field > how could I achieve it? > This sounds like a real challange. Throughout the years I've had to learn it's always a pain to retrieve system catalog information - no matter which DBMS is in use. On the other hand, you have to face the fact that view.field's sources are not necessarily directly derived from a table.field. A view.field's source may be any expression including aggregates. So, the only way I can think of achieving that is SELECT definition from pg_views where viewname=''; and then your function has to parse the result. Good luck. Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] PLpgSQL FOR IN EXECUTE question
Thanks to Josh, Richard, Tom
EXECUTE ''SELECT CAST(''
|| quote_ident($1)
|| '' AS TEXT) AS foo FROM ''
|| quote_ident($2)
and then
list := list || '', '' || this_record.foo ;
works perfectly.
Regards, Christoph
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] query optimization question
>
> This is the final query, can anyone see anything wrong with it?:
> SELECT projects.project_id, projects.marketing_name,
> COUNT(lots.lot_id) AS def_count,
> COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-08'}
> THEN lots.lot_id ELSE NULL END
> ) AS def_count_less_30,
> COUNT(CASE WHEN dt.days_old_start_date >= {d
'2002-10-08'}
> AND dt.days_old_start_date < {d
'2002-09-08'}
> THEN lots.lot_id ELSE NULL END
> ) AS def_count_30_60,
> COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-09-08'}
> THEN lots.lot_id ELSE NULL END
> ) AS def_count_greater_60,
> COUNT(DISTINCT(CASE WHEN dt.days_old_start_date < {d
'2002-10-08'}
> AND
dt.deficiency_status_id = ds.deficiency_status_id
> THEN lots.lot_id ELSE
NULL END)
> ) AS lot_count_less_30,
> COUNT(DISTINCT(CASE WHEN dt.days_old_start_date >= {d
'2002-10-08'}
> AND
dt.days_old_start_date < {d '2002-09-08'}
> THEN lots.lot_id
ELSE NULL END)
> ) AS lot_count_30_60,
> COUNT(DISTINCT(CASE WHEN dt.days_old_start_date >= {d
'2002-09-08'}
> THEN lots.lot_id ELSE
NULL END)
> ) AS lot_count_greater_60,
> COUNT(DISTINCT lots.lot_id) AS lot_count
> FROM
> (SELECT * FROM deficiency_table) AS dt,
> (SELECT * FROM deficiency_status WHERE is_outstanding) AS ds,
> (SELECT * FROM projects WHERE division_id = 'GGH') AS proj,
> (SELECT * FROM lots) AS lots
> WHERE proj.division_id = 'GGH'
> AND lots.division_id = proj.division_id
> AND lots.project_id = proj.project_id
> AND dt.lot_id = lots.lot_id
> AND dt.deficiency_status_id = ds.deficiency_status_id
> AND ds.is_outstanding
> AND lots.project_id = 'EM16'
> AND NOT EXISTS (SELECT 1 FROM menu_group_projects WHERE
menu_code = 'WA'
> AND division_id = proj.division_id AND project_id = proj.project_id
AND
> status = 'I')
> GROUP BY projects.project_id, projects.marketing_name
>
First thing I would try
change
> SELECT projects.project_id, projects.marketing_name,
to
SELECT proj.project_id, proj.marketing_name,
and
> GROUP BY projects.project_id, projects.marketing_name
to
GROUP BY proj.project_id, proj.marketing_name
because I think the sub-SELECT (SELECT * FROM projects WHERE division_id
= 'GGH') AS proj
should be referenced instead of the table projects.
If you still receive the startling result, I'd like to suggest another
approach.
Why not generate a view or sub-SELECT first which shows all the columns
you need to refer to resp. count, and then
SELECT project_id, marketing_name,
COUNT( ... ,
... ,
FROM < the view or sub-SELECT>
-- no WHERE-clauses at all
GROUP BY project_id, marketing_name ;
It should at least make it more easy to track down what's wrong.
Regards, Christoph
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Generating a cross tab (pivot table)
> I saw something that might somewhat a bit more > flexible solution using SQL. I don't know if it works > in PostgreSQL. I saw it at the MySQL site. > > The following is the URL: > http://www.mysql.com/articles/wizard/index.html > > Has anyone tried this on a PostgreSQL database ? No, not me. But as far as I can tell the SQL statements can quite easily be re-written in PostgreSQL: e. g. mysql> SELECT location, SUM(IF(gender='M',1,0)) AS M, SUM(IF(gender='F',1,0)) AS F -> FROM locations INNER JOIN employees USING (loc_code) GROUP BY location; becomes SELECT location, SUM(CASE WHEN gender='M' THEN 1 ELSE 0 END) AS "M", SUM(CASE WHEN gender='F' THEN 1 ELSE 0 END) AS "F", FROM locations LEFT JOIN employees ON (locations.loc_code=employees.loc_code) GROUP BY location; And this goes for the perl script as well. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Generating a cross tab II (pivot table)
Based on the e-mails on "Generating a cross tab (pivot table)",
I can give you a PLpgSQL procedure to automatically generate a
cross tab from any relation now.
It's my first steps in PLpgSQL. I am pretty sure this is not the
best way to implement, but I wanted to get some experience, so I
did it this way.
For all, who missed it last week, again the objective:
There is a relation "sales",
holding the sales of different products of different vendors.
The task is to generate a report which shows the sales
of every vendor and every product.
Consider the following table populated with some data:
CREATE TABLE sales (
product TEXT,
vendor TEXT,
sales INTEGER
);
INSERT INTO sales VALUES ( 'milk' , 'mr. pink' , 12 ) ;
INSERT INTO sales VALUES ( 'milk' , 'mr. brown' , 8 ) ;
INSERT INTO sales VALUES ( 'honey' , 'mr. green' , 2 ) ;
INSERT INTO sales VALUES ( 'milk' , 'mr. green' , 34 ) ;
INSERT INTO sales VALUES ( 'butter', 'mr. pink' , 17 ) ;
INSERT INTO sales VALUES ( 'butter', 'mr. brown' , 2 ) ;
INSERT INTO sales VALUES ( 'honey' , 'mr. pink' , 19 ) ;
The following query generates the report:
CREATE VIEW sales_report AS
SELECT product,
SUM(CASE vendor WHEN 'mr. pink' THEN sales ELSE 0 END) AS "mr. pink ",
SUM(CASE vendor WHEN 'mr. brown' THEN sales ELSE 0 END) AS "mr. brown",
SUM(CASE vendor WHEN 'mr. green' THEN sales ELSE 0 END) AS "mr. green",
SUM(sales) AS "sum of sales"
FROM sales GROUP BY product ;
SELECT * FROM sales_report ;
product | mr. pink | mr. brown | mr. green | sum of sales
-+---+---+---+--
butter |17 | 2 | 0 | 19
honey |19 | 0 | 2 | 21
milk|12 | 8 |34 | 54
(3 rows)
It's obvious this approach is most inflexible.
As soon as there is a new vendor, one has to re-write the query and add
SUM(CASE vendor WHEN 'mr. new' THEN ... ,
So what we need is a tool to automatically adapt the view to new vendors
resp. new products.
Here it is (choosing good mnemonics is not my favourite discipline):
CREATE OR REPLACE FUNCTION
create_pivot_report(TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS INTEGER AS '
DECLARE
pg_views_rtype pg_views%ROWTYPE;
vname_paramALIAS FOR $1;
pivot_column ALIAS FOR $2;
select_column ALIAS FOR $3;
pivot_tableALIAS FOR $4;
aggregate_func ALIAS FOR $5;
aggr_columnALIAS FOR $6;
pivot_record RECORD;
create_viewTEXT;
BEGIN
SELECT INTO pg_views_rtype * FROM pg_views WHERE viewname = vname_param;
IF FOUND THEN
EXECUTE ''DROP VIEW '' || quote_ident(vname_param) ;
END IF;
create_view :=
''CREATE VIEW '' || quote_ident(vname_param) ||
'' AS SELECT '' || quote_ident(select_column) ;
FOR pivot_record IN
EXECUTE ''SELECT DISTINCT CAST(''
|| quote_ident(pivot_column)
|| '' AS TEXT) AS col1 FROM ''
|| quote_ident(pivot_table)
|| '' order by '' || quote_ident(pivot_column)
LOOP
create_view :=
create_view || '','' || aggregate_func ||
''(CASE '' || quote_ident(pivot_column) ||
'' WHEN '' || quote_literal(pivot_record.col1) ||
'' THEN '' || quote_ident(aggr_column) ||
'' ELSE 0 END) AS "'' || pivot_record.col1 || ''"'' ;
END LOOP;
create_view :=
create_view || '','' || aggregate_func ||
''('' || quote_ident(aggr_column) || '') AS "'' || aggregate_func ||
'' of '' || aggr_column || ''" FROM '' || quote_ident(pivot_table) ||
'' GROUP BY '' || quote_ident(select_column);
EXECUTE create_view ;
RETURN 0;
END;
' LANGUAGE 'plpgsql' ;
-- where
-- vname_paramALIAS FOR $1; -- the view's name to create
-- pivot_column ALIAS FOR $2; -- the pivot column (entries to be CASEd)
-- select_column ALIAS FOR $3; -- the select column (entries to be grouped)
-- pivot_tableALIAS FOR $4; -- the name of the table to work on
-- aggregate_func ALIAS FOR $5; -- the name of the aggregate function
-- aggr_columnALIAS FOR $6; -- the aggregate column (entries to be aggregated)
First try:
SELECT create_pivot_report
('sales_report2','vendor','product','sales','sum','sales');
SELECT * FROM sales_report2 ;
gives you 'sales_report2' as a copy of 'sales_report'.
Now add another data set:
INSERT INTO sales VALUES ( 'butter', 'mr. blue' , 11 ) ;
Re-write the view by:
SELECT create_pivot_report
('sales_report2','vendor','product','sales','sum','sales');
And here we go
SELECT * FROM sales_report2 ;
product | mr. blue | mr. brown | mr. green | mr. pink | sum of sales
-+--+---+---+--+--
butter | 11 | 2 | 0 | 17 | 30
honey |0 | 0 | 2 | 19 | 21
milk|0 | 8 |34 | 12 | 54
(3 rows)
More examples:
SELECT create_pivot_report
('sales_report3','vendor','product','sales','avg','sales');
SELECT create_pivot_report
('sales_report4'
Re: [SQL] CREATE VIEW (dynamically)
> CREATE FUNCTION "requests_insert_after" () RETURNS opaque AS ' > DECLARE > view_name text; > BEGIN > view_name := ''request_'' || NEW.id; > CREATE VIEW view_name AS select * from groups; > return NEW; > END' LANGUAGE 'plpgsql'; > > CREATE TRIGGER "requests_insert_after" AFTER INSERT ON "requests" FOR EACH ROW > EXECUTE PROCEDURE "requests_insert_after" ();>> > > This code after an insertion on table "requests" give me this message : > << > PostgreSQL said: ERROR: parser: parse error at or near "$1" > >> > > Why ? > You should check the documentation of plpgsql for the section Executing dynamic queries (which also means commands like update, etc.) e.g. to drop a view within plpgsql you have to code EXECUTE ''DROP VIEW '' || quote_ident( view_name) ; Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Output of function into a file
>
> I am writing a small function to manipulate records in multiple
> tables. Since the function cannot return more than one value, I would
> like to get all the outputs of the queries and append them into a text
> file.
>
> Is there any way to do it inside the function. I came across many
> postings which tell me how to use it in pgsql but not inside the
> function.
>
You did not tell which function language you're talking about.
I'm assuming it's plpgsql.
First, did you check the cursors section?
Rather than executing a whole query at once, it is possible to set up a
cursor that encapsulates the query, and then read the query
result a few rows at a time. One reason for doing this is to avoid
memory overrun when the result contains a large number of rows.
(However, PL/pgSQL users don't normally need to worry about that, since
FOR loops automatically use a cursor internally to avoid
memory problems.) A more interesting possibility is that a function can
return a reference to a cursor that it has set up, allowing
the caller to read the rows. This provides one way of returning a rowset
from a function.
Taken from "PostgreSQL 7.2.1 Documentation Chapter 23. PL/pgSQL - SQL
Procedural Language"
Second, if this is not what you want to use,
I have not seen anything within plpgsql which could be used
to write to a file. But, what about a database table to be used as a
file?
e.g.
CREATE TABLE file_replacement(one_line TEXT);
then populate it by inserts like
INSERT INTO file_replacement VALUES('');
and to get them back in FIFO order
SELECT one_line FROM file_replacement ORDER BY oid;
Regards, Christoph
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] averaging interval values
> > 1:01:01 > 0:25:15 > 7:09:01 > 8:09:05 > > Is there any way to average or sum those above values using a > function? > > i could not type cast the varchar(40) to interval type. > > How to change the datatype of field from varchar to interval? > Try SELECT CAST(CAST (t2 AS TEXT) AS INTERVAL) FROM test; SELECT AVG(CAST(CAST (t2 AS TEXT) AS INTERVAL)) FROM test; SELECT SUM(CAST(CAST (t2 AS TEXT) AS INTERVAL)) FROM test; It works fine on my machine, I'm using PostgreSQL 7.2.1. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Passing OLD/NEW as composite type PL/PGSQL
> >Can I pass the the variables OLD and NEW (type > OPAQUE) to another function is expecting a composite > type as parameter? > >Are opaque considered as composite type? > Did you receive any other response? Did you check if it works by simply trying? As far as I understand the documentation, OPAQUE can be considered (among others) as composite type. If it's not working, did you think of copying the OLD resp. NEW to a rowtype variable? I am thinking of something like this (taken from the Trigger Procedure Example within the documentation) CREATE TABLE emp ( empname text, salary integer, last_date timestamp, last_user text ); CREATE FUNCTION process_old_emp_row(emp%ROWTYPE) RETURNS ... ; CREATE FUNCTION process_new_emp_row(emp%ROWTYPE) RETURNS ... ; CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS ' DECLARE old_emp_row emp%ROWTYPE; new_emp_row emp%ROWTYPE; BEGIN -- copy OLD to old_emp_row, call process_old_emp_row old_emp_row.empname := OLD.empname; old_emp_row.salary := OLD.salary; old_emp_row.last_date := OLD.last_date; old_emp_row.last_user := OLD.last_user; process_old_emp_row(old_emp_row); -- Check that empname and salary are given IF NEW.empname ISNULL THEN RAISE EXCEPTION ''empname cannot be NULL value''; END IF; IF NEW.salary ISNULL THEN RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname; END IF; -- Who works for us when she must pay for? IF NEW.salary < 0 THEN RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname; END IF; -- Remember who changed the payroll when NEW.last_date := ''now''; NEW.last_user := current_user; -- copy NEW to new_emp_row, call process_new_emp_row new_emp_row.empname := NEW.empname; new_emp_row.salary := NEW.salary; new_emp_row.last_date := NEW.last_date; new_emp_row.last_user := NEW.last_user; process_new_emp_row(new_emp_row); RETURN NEW; END; ' LANGUAGE 'plpgsql'; Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] PL/SQL trouble
> I really don't understand following PostgreSQL 7.2.3 behaviour:
>
> $ psql mydb
> mydb=> CREATE FUNCTION MONDAY(timestamp) RETURNS DATE AS '
> DECLARE
> var1 date;
> BEGIN
> select into var1 to_date($1::date-(case when extract(DOW from
> timestamp $1) = 0 then 6 else (extract(DOW from timestamp $1)-1)
end));
> RETURN var1;
> END'
> language 'plpgsql';
>
> CREATE
> mydb=> select MONDAY('now'::timestamp);
> NOTICE: Error occurred while executing PL/pgSQL function MONDAY
> NOTICE: line 4 at select into variables
> ERROR: parser: parse error at or near "$2"
> mydb=> \q
>
> But I've not inserted any $2 there.
> I've rewritten the same function in other ways but I've got the same
error.
>
Something like the following works (as Richard already pointed out):
CREATE OR REPLACE FUNCTION MONDAY(timestamp) RETURNS DATE AS '
DECLARE
ts_paramALIAS FOR $1;
var1 date;
BEGIN
select into var1 to_date(ts_param::date-
(case when extract(DOW from ts_param) = 0
then 6 else (extract(DOW from ts_param)-1) end),''DD'');
RETURN var1;
END'
language 'plpgsql';
Me personally would prefer another approach:
CREATE OR REPLACE FUNCTION MONDAY(timestamp) RETURNS DATE AS '
DECLARE
ts_paramALIAS FOR $1;
var1 date;
var2 double precision;
BEGIN
var2 := extract(DOW from ts_param);
IF var2 = 0 THEN
var2 := 6;
ELSE
var2 := var2 - 1;
END IF;
var1 := to_date(ts_param::date - var2,''DD'');
RETURN var1;
END'
language 'plpgsql';
because it's easier to read, but that's only a matter of taste I
suppose.
Regards, Christoph
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] retrieving specific info. from one column and locating it in another
>
> I'm trying to retrieve some info from one column and
> put it in another. I have a column that has a bunch
> of information in it called 'Route'. I don't need to
> show all of that information. Instead I need divide
> that single column into two seperate columns called
> 'Sender' and 'Receiver'. How do I divide this
> information up into these two columns. I know of
> methods called charindex and patindex. I need to do
> something like that but instead of returning the
> position of the info, to just return the selected
> info.
> Ex) I have a column named Routewith info in it
> similar to 'UPS NS Ground'
>How do I create a second column called
> 'Delivery' and pull only the 'NS' out of the Route
> column and put it into the 'Reciever' column?
>Similarly how would I pull just the UPS part
> out of Route and put it into 'Sender'?
>
Marc,
I've seen some tricky stuff to split column values yesterday on the
list.
It was sent by Peter Childs Subject Re: [SQL] Question on SQL and
pg_-tables.
He found something like a split.
Adapted to your needs it would result in something similar to (supposed
blanks are your separators)
SELECT SUBSTRING(route,1,POSITION(' ' IN route)-1) AS ups,
SUBSTRING(route,POSITION(' ' IN route)+1,POSITION(' ' IN
SUBSTRING(route,POSITION(' ' in ROUTE)+1))) AS ns
FROM ...
Hope this helps.
Regards, Christoph
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Big query problem
>
> DELETE FROM table WHERE col1='something' AND col2 IN
> ('aasdoijhfoisdfsdoif','sdfsdfsdfsadfsdf', ... );
>
> In the parantheses I have 6400 names, each about 20 characters. I'm
> using libpq from C. This did not work very well, but the result was
> very unexpected.
>
The conditional operator IN is or at least was known to be slow.
Did you think of an alternative approach as
CREATE TABLE to_delete_col2 ( ref_col2 ... ) ;
populate it with 'aasdoijhfoisdfsdoif','sdfsdfsdfsadfsdf', ...
and then
DELETE FROM table WHERE col1='something' AND
EXISTS (SELECT ref_col2 FROM to_delete_col2 WHERE table.col2 =
to_delete_col2.ref_col2 );
> My application has several threads, each opening its own connection to
> the database. The above query was run in a transaction followed by a
> COMMIT. There was no error from running the above query, but instead,
> it seems that the query was never run at all. As a side effect, every
> other connection to the database always got:
>
> NOTICE: current transaction is aborted, queries ignored until end of
> transaction block
>
> when trying to run a query. I thought that the transactions in
> different connections didn't have anything to do with each other.
>
>
> If I limited the number of names in the failing query to 3200, it
> worked well and as expected.
>
>
> Is there a limit in libpq of the length of a query? And if this is
> exceeded, shouldn't PQexec() give an error?
I agree!
>
I think you should post this to the HACKERS list. Maybe you'll get
a reply from there.
Regards, Christoph
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Query about table and catalog
> I've got a table with several fields. Among others there are the fields > 'soil1', 'soil2', and 'soil3' that are char type. A row can have an empty > value in one of these fields, or the three fields can have valid values: > > cod_grass |suelo1|suelo2 | suelo3 > ---+--++- > 2590 | Xerosoles petrocalcicos| | >181 | Xerosoles calcicos | | >265 | Xerosoles petrocalcicos | | >593 | Zona urbana | | > 1112 | Cambisoles calcicos | | > 2 | Litosoles | | > 3 | Xerosoles calcicos|| > 4 | Litosoles | Rendsinas aridicas | > 5 | Xerosoles petrocalcicos | | > 6 | Litosoles | | > 7 | Regosoles calcaricos | Xerosoles calcicos > ... > > In other table I've got a catalog of posible soil types, assigning an integer > value to each of possible soil types. > > tipo_suelo | cod_tipo_suelo > -+ > Arenosoles albicos | 1 > Cambisoles calcicos | 2 > Cambisoles eutricos | 3 > > Is it possible to prepare a query that show the contents of the table of > soils and aditional columns after each of the soils fields, showing the > corresponding numerical code for that soil, extracted from the catalog? > > I just know how to do this for one of the soils: > > SELECT cod_grass, suelo1,cod_tipo_suelo AS cod_suelo1 FROM > suelos,suelos_catalogo WHERE suelo1=tipo_suelo ORDER BY cod_grass; > > But I would like to do the same for the three at a time. > Try this (untested) or something similar: SELECT cod_grass, suelo1, st1.cod_tipo_suelo AS cod_suelo1 suelo2, st2.cod_tipo_suelo AS cod_suelo2 suelo3, st3.cod_tipo_suelo AS cod_suelo3 FROMsuelos, suelos_catalogo st1, suelos_catalogo st2, suelos_catalogo st3 WHERE suelo1=st1.tipo_suelo AND suelo2=st2.tipo_suelo AND suelo3=st3.tipo_suelo ORDER BY cod_grass; I'm curious why you did not design the tables vice versa. Table "suelos" just holding "cod_tipo_suelo", so queries like the above would run much faster, because only integers have to be compared instead of strings. Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Question
> Can I get a table structure on a function using pgsql?? I think so. What are your intentions? You should be more specific. Regards, Christoph ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] sql query
> > Hi I have a problem forming a query > I have 3 tables salary(hrs, clock_in , clock_out) > Break ( date, employe_id, net_time, break_in, break_out) > Employee(employee_id, pay_rate, name) > > I need to get an hourly report till the current time for that day > stating name, hour, no of hours, salary > Example > > Rob 3-448min $6.56 > Min 2-323min $2.34 > Min 3-454min $7.67 > > And so on..Would appreciate help on this.since I cant come up with the > query > How do you intend to find out which entry in your table "salary" is related to which employee as there is no id to refer to. Am I right in assuming your column "hour" with entries 3-4, 2-3, ... means from 3 til 4 o'clock and so on? Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Question II
> > I would like to get all field name of a table within a stored procedure > using pgsql. How to do it?? > SELECT a.attname FROM pg_class c, pg_attribute a WHERE c.relname = '' AND a.attnum > 0 AND a.attrelid = c.oid ORDER BY a.attnum ; gives you the field names of . If you start a psql session with the -E option, you can see how \d is sql-generated. If you are asking for support how to write this pgsql function (table name parameter, query, etc.), then refer to the documentation or send another request. Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Stored Procedure Problem
> CREATE FUNCTION b_function() RETURNS int4 AS ' > DECLARE >an_integer int4; > BEGIN >select emp_id from employee; >return an_integer; > END; > ' > LANGUAGE 'plpgsql'; > Try SELECT INTO an_integer emp_id from employee; Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Performance Problems
> >I have performance problems with a huge database > (there a 2 tables with 40 millions of records) and > many users doing updates and queries on it. I 've > perform severals VACUMM on the database with poor > results. >Each table have an unique index and I added other > indexes to improve the performance. But when there are > inserts (not too many) the performance fall. >What can I do to improve the performace? I hear any > opinion. > Bear in mind, every index has to be updated when records are inserted. An unique one makes it even worse, because a search for duplicates must be performed. You may give the idea some thought, if this index really has to be unique. I sometimes use a 'timestamp-insert' column to allow duplicates and retrieve unique entries by a SELECT DISTINCT ON construct. Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] UPDATE with a SELECT and subSELECT(About comparing dates and non dates data)
> My problem is that I need to fill in the gaps (the available rain data in the > corresponding stations), and this would be a very good output for me. > I've prepared an UPDATE but it doesn't work. Perhaps someone could tell me > where is the error (I've not a very good knowledge of Postgresql). The UPDATE > is based on the results of the query: > > > UPDATE series_lluvia SET st7237=rain FROM > > /* here begins the SELECT to obtain the series for one rain gauge station; > and it works right > from here to the next comment */ > SELECT cod_station, year, month, day, rain FROM ( > > SELECT cod_variable, cod_station, year, month, 1 as day, rain_day1 as rain > FROM pluviometria WHERE ten=1 > UNION ALL > ... > SELECT cod_variable, cod_station, year, month, 31 as day, rain_day11 as rain > FROM pluviometria WHERE ten=3 AND rain_day11 IS NOT NULL > ORDER BY cod_station, year, month, day) AS temp WHERE cod_station=7238 > > /* the SELECT has finished here */ > WHERE series_lluvia.year=temp.year AND series_lluvia.month=temp.month AND > series_lluvia.day=temp.day; > --- > > Please can you tell me if the syntax of this UPDATE is correct? (Can I use > the results of a SELECT to UPDATE a table?) > Javier, I've seen something similar on the list in January this year. Maybe it works. Try UPDATE series_lluvia SET st7237=( SELECT rain FROM ( SELECT cod_variable, cod_station, year, month, 1 as day, rain_day1 as rain FROM pluviometria WHERE ten=1 UNION ALL ... SELECT cod_variable, cod_station, year, month, 31 as day, rain_day11 as rain FROM pluviometria WHERE ten=3 AND rain_day11 IS NOT NULL ORDER BY cod_station, year, month, day) AS temp WHERE cod_station=7238 ) WHERE series_lluvia.year=temp.year AND series_lluvia.month=temp.month AND series_lluvia.day=temp.day; Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] join and dynamic view
> > is it possible to make a dynamically declare a view based on a table? > Yes, by all means. > > Is it possible to now define a view such that it returns: > > select * from myview; > sid | Name| OPS | MPD > -+-+-+- > 1 | Rod | | 3 > 2 | Jayne | 2 | 5 > 3 | Freddie | 3 | > > and if I add another row to depts, that the new row would be included? > you mean column, don't you? The closest query I can get so far is SELECT staff.*, CASE dsdesc WHEN 'OPS' THEN rrank ELSE NULL END AS "OPS", CASE dsdesc WHEN 'MPD' THEN rrank ELSE NULL END AS "MPD" FROM staff,depts,ranks WHERE sid=rsid AND did=rdid ; sid | sname | OPS | MPD -+-+-+- 1 | Rod | | 3 2 | Jayne | | 2 2 | Jayne | 5 | 3 | Freddie | 3 | (4 rows) but sid | sname | OPS | MPD -+-+-+- 1 | Rod | | 3 2 | Jayne | 5| 2 3 | Freddie | 3 | (3 rows) is what you want (I suppose Jayne's 2 in OPS and 5 in MPD is a mismatch of yours). As soon as you are somebody else can tell me how to merge Jayne's two rows into one, I'm sure I can write a plpgsql function to dynamically create the view you're looking for. Regards, Christoph ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] join and dynamic view
> As soon as you or somebody else can tell me how to merge Jayne's two
> rows into one,
> I'm sure I can write a plpgsql function to dynamically create the view
> you're looking for.
Ok, got it:
SELECT sid, sname, SUM("OPS") AS "OPS", SUM("MPD") AS "MPD" FROM (
SELECT staff.*,
CASE dsdesc WHEN 'OPS' THEN rrank ELSE 0 END AS "OPS",
CASE dsdesc WHEN 'MPD' THEN rrank ELSE 0 END AS "MPD"
FROM staff,depts,ranks WHERE sid=rsid AND did=rdid ) as foo
GROUP BY sid, sname ;
sid | sname | OPS | MPD
-+-+-+-
1 | Rod | 0 | 3
2 | Jayne | 5 | 2
3 | Freddie | 3 | 0
(3 rows)
Gary,
I'm going to write the plpgsql function to dynamically amend the view.
In the meantime you may think about creating a trigger which fires every
time a new department is entered and which calls the function then.
Regards, Christoph
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] join and dynamic view
> > I've now started amending your plpgsql script to create this, but as you can > see I've cocked up somewhere. I wonder if you could have a peek at it for > me. > Gary, CREATE OR REPLACE FUNCTION create_users_view() returns integer as ' DECLARE pg_views_rtype pg_views%ROWTYPE; vname_paramTEXT; ranks_record RECORD; create_viewTEXT; join_text TEXT; BEGIN vname_param:=''users''; SELECT INTO pg_views_rtype * FROM pg_views WHERE viewname = vname_param; IF FOUND THEN EXECUTE ''DROP VIEW '' || quote_ident(vname_param) ; END IF; create_view := ''CREATE VIEW '' || quote_ident(vname_param) || '' AS SELECT s.* ''; join_text:='' from staff s ''; FOR ranks_record IN EXECUTE ''SELECT did, dsdesc from depts ORDER BY did;'' LOOP create_view := create_view || '', '' || ranks_record.did || ''.rrank AS '' || ranks_record.dsdesc; join_text := join_text || '' left outer join ranks '' || ranks_record.did || '' ON '' || ranks_record.did || ''.rsid = s.sid and '' || ranks_record.did || ''.rdid = '' || quote_literal(ranks_record.did) ; END LOOP; create_view := create_view || join_text || '';''; EXECUTE create_view ; RETURN 0; END; ' LANGUAGE 'plpgsql' ; should work. > I don't think it's good idea to do this, but you can recreate views > inside trigger on insert/update into depts. Tomasz, Could you please point out why this is not a good idea. Thanks. Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] join and dynamic view
> > Christoph Haller wrote: > > > Tomasz, > > Could you please point out why this is not a good idea. Thanks. > > How often do you change structure of this view? What happens when during > querying this view someone recreates it? > > What happens to your reports? Do you have them already dynamic? > Usually I create A4-paper based reports, so it is difficult to fit them > if horizontal structure changes. > > Maybe creating dynamic view is not so bad idea. I think you should > watch them carefully so they don't surprise you. > > Tomasz Myrta > Good Points. Thanks again. Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: Upgrade question - was Re: [SQL] join and dynamic view
> > That explains it - the server I'm developing on is quite old - I didn't > realise how old. I'm about to do an upgrade from 7.1.3 to 7.2.1-5 over > christmas in fact. > > Will I need to dump/restore the database for this upgrade? > I'm not sure. But I think it's never ever a bad idea to do a dump before any kind of upgrade. Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] handling error in a function
> > i made desperate efforts with handling errors in a function. > > I am using functions for encapsulating a few sql-statements. Please have a > look at this: > > CREATE FUNCTION sp_fdelce(int4) RETURNS int4 AS ' > DECLARE > id ALIAS FOR $1; > BEGIN >DELETE FROM f_ces WHERE fce_id = id; > >-- "virtual code follows" >IF ERROR > RETURN 0; >ELSE > RETURN 1; > END; > ' > LANGUAGE 'plpgsql'; > > Not difficult. I know. But fce_id is used as a foreign key by other tables. > When executing this and violating that constraint (i mustn't delete that > row), the function aborts with "unknown error" and i have no way to return 0 or > something like that. > > I am programming with PHP and PEAR, each time, the result set is an object > of type error, the script jumps to an error page, to calm the angry customers. > > > Especially in this case I don't want to jump to the error page, i want to > tell the user with a normal Messageline: Sorry, you mustn't delete that > element. > > I can't handle this error? Is that right? I really have no way to catch that > foreign key violence? > Sure you can, but not directly. Before deleting you should check for the error condition, possibly by querying system tables especially pg_relcheck. So, if the error condition matches, don't delete but generate your message line. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Dynamic sql program using libpq
>
> I am able to find couple
> of examples for dynamic sql programming through ecpg. But i want to
> do dynamic sql programming through libpq.
> If anyone has programs doing the dynamic sql programmming using the
> libpq libraries please mail to me.
>
Attached you'll find my encapsulated library of postgres functions,
which I use for all queries resp. commands.
Regards, Christoph
/*-
*
* libpq-myfe.h
*
*
* Christoph Haller, D.T.I.
*
* Created Sep 2001
*
*-
*/
#include "libpq-fe.h"
#ifndef LIBPQ_MYFE_H
#define LIBPQ_MYFE_H
int PGSQL_command(PGconn *thisconnec, PGresult *thisresul,
char *thiscommand);
int PGSQL_process(PGconn *thisconnec, PGresult **thisresul,
char *thiscommand);
int PGSQL_begin(PGconn *thisconnec);
int PGSQL_declare_cursor1(PGconn *thisconnec, PGresult *thisresul,
char *thisselect);
int PGSQL_fetch_all1(PGconn *thisconnec, PGresult **thisresul);
int PGSQL_close_cursor1(PGconn *thisconnec, PGresult *thisresul);
int PGSQL_commit(PGconn *thisconnec);
int PGSQL_rollback(PGconn *thisconnec);
int PGSQL_declare_cursor2(PGconn *thisconnec, PGresult *thisresul,
char *thisselect);
int PGSQL_fetch_all2(PGconn *thisconnec, PGresult **thisresul);
int PGSQL_close_cursor2(PGconn *thisconnec, PGresult *thisresul);
int PGSQL_declare_bincsr1(PGconn *thisconnec, PGresult *thisresul,
char *thisselect);
int PGSQL_fetch_binall1(PGconn *thisconnec, PGresult **thisresul);
int PGSQL_close_bincsr1(PGconn *thisconnec, PGresult *thisresul);
int PGSQL_binprocess(PGconn *thisconnec, PGresult **thisresul,
char *thiscommand);
#endif /* LIBPQ_MYFE_H */
#include
#include
#include
#include "libpq-myfe.h"
char *strdup(const char *s);
#ifdef PostgreSQL_6_5_3
const char *PGSQL_UNIQUE_INDEX_VIOLATED = /* PostgreSQL 6.5.3 */
"ERROR: Cannot insert a duplicate key into a unique index" ;
#endif /* PostgreSQL_6_5_3 */
const char *PGSQL_UNIQUE_INDEX_VIOLATED = /* PostgreSQL 7.1.2 */
"ERROR: Cannot insert a duplicate key into unique index " ;
/*
** procedure: trim_sequence
** purpose: delete space sequences within a given string
** delete spaces only within the given length
** the given string does not have to be null terminated
** this function is secure:
** - one space always remains
** - no space, no action
** - several space sequences are processed
** - deleting stops if a null character is found
**and the given length is not reached
**
** parameters: seq - the string to trim
** seq_len - the length of string to trim
** returns: the trimmed string
*/
# if defined(__STDC__) || defined(__cplusplus)
char *trim_sequence(char *seq,size_t seq_len)
# else /* __STDC__ || __cplusplus */
char *trim_sequence(seq,seq_len)
char *seq;size_t seq_len;
# endif /* __STDC__ || __cplusplus */
{
char space=' '; /* the trim character */
char *first=seq; /* ptr to the 1st space */
char *last; /* ptr to the last space */
size_t seq_pos=0;/* index within sequence */
/* while not end of sequence ... */
while(seq_pos continue search */
while(last&&*last==space&&last-seqfirst) {
*first=NULL; /* set temporary termination */
strcat(seq,last); /* append the following subsequence */
}
seq_pos=last-seq; /* update sequence index */
}
return seq; /* return the trimmed sequence */
} /* trim_sequence() */
int PGSQL_command(PGconn *thisconnec, PGresult *thisresul,
char *thiscommand)
{
int result = EXIT_SUCCESS;
trim_sequence(thiscommand, strlen(thiscommand));
thisresul = PQexec(thisconnec, thiscommand);
if (!thisresul || PQresultStatus(thisresul) != PGRES_COMMAND_OK)
{
fprintf(stderr, "'%s' command failed\n%s\n", thiscommand,
PQresultErrorMessage(thisresul));
result = -EXIT_FAILURE;
}
else
{
result = atoi(PQcmdTuples(thisresul));
}
/* PQclear PGresult whenever it is no longer needed to avoid memory leaks */
PQclear(thisresul);
return result;
}
int PGSQL_process(PGconn *thisconnec, PGresult **thisresul,
char *thiscommand)
{
int result = EXIT_SUCCESS;
trim_sequence(thiscommand, strlen(thiscommand));
*thisresul = PQexec(thisconnec, thiscommand);
if (!*thisresul || PQresultStatus(*thisresul) != PGRES_TUPLES_OK)
{
fprintf(stderr, "'%s' command failed\n%s\n", thiscommand,
Re: [SQL] unsubscribe
David and all others on the list, who want to turn off mailing over xmas and new year, please send your unsubscribe to [EMAIL PROTECTED] Otherwise it will not be processed. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] UPDATE with a SELECT and subSELECT(About comparing dates and non dates data)
> > Typing what you told me there was an errror; I' ve changed it slightly an it > seems that this sintax is correct. I say "it seems" because the computer > begins to process the query but doesn't finish it. I've leaved it working for > more than half an hour, before cancel it, with no result. > > Thank you anyway. This is what I think is a good sintax for UPDATE - SELECT - > SUBSELECT. Perhaps in mor simple cases it works. May someone is interested in > it. > > -- > Javier > > > UPDATE series_lluvia SET st7237=( > SELECT rain FROM ( > SELECT cod_variable, cod_station, year, month, 1 as day, rain_day1 as > rain > FROM pluviometria WHERE ten=1 > UNION ALL > ... > SELECT cod_variable, cod_station, year, month, 31 as day, rain_day11 as > rain > FROM pluviometria WHERE ten=3 AND rain_day11 IS NOT NULL > ORDER BY cod_station, year, month, day) AS temp WHERE cod_station=7237) AS > temp2 WHERE series_lluvia.year=temp2.year AND > series_lluvia.month=temp2.month AND series_lluvia.day=temp2.day); > -- > Javier, I've seen several queries which seemed to run for ages before. In many cases it helped to generate temporary tables and / or split up into "smaller" commands. Have you tried it by removing the union clauses as UPDATE series_lluvia SET st7237=( SELECT rain FROM ( SELECT cod_variable, cod_station, year, month, 31 as day, rain_day11 as rain FROM pluviometria WHERE ten=3 AND rain_day11 IS NOT NULL ORDER BY cod_station, year, month, day) AS temp WHERE cod_station=7237) AS temp2 WHERE series_lluvia.year=temp2.year AND series_lluvia.month=temp2.month AND series_lluvia.day=temp2.day); If this runs in an acceptable time, split up into several UPDATEs. If not, think of using temporary tables for SELECT - SUBSELECT. It's probably useful to do this in a transaction block started by BEGIN; so you can ROLLBACK; if the result is wrong. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] plpgsql: return results of a dynamic query
>
> I'm trying to retrieve a row count from several tables (40) and would
like
> to create a function that does this automatically for the 40 and
displays
> the results. So, I loop through the tables:
>
> DECLARE
> obj RECORD;
> BEGIN
> FOR obj IN SELECT relname AS name FROM pg_class
> WHERE relkind IN ('r')
> AND relname like '%_random' AND relname != 'tout_random'
> LOOP
>
>
> then I need to do the select count for each table in the lines of
>
> SELECT count(t1.id) AS total FROM || obj.name || AS t1 JOIN
tout_random
> AS t2 ON t1.id=t2.id WHERE t2.ok=TRUE;
>
> and return the result of each of these select counts.
>
> Could someone indicate how to return the results of these queries ?
> Am I right that in order to do this dynamic query, I have to use an
> EXECUTE statement ? Can I return the results of an EXECUTE statement ?
>
You are on the right track.
The documentation says:
The results from SELECT queries are discarded by EXECUTE, and SELECT
INTO is not currently supported within EXECUTE.
So, the only way to extract a result from a dynamically-created SELECT
is to use the FOR-IN-EXECUTE form described later.
So something like the following should do the trick:
DECLARE
obj RECORD;
obj2 RECORD;
countresult BIGINT;
BEGIN
countresult := 0;
FOR obj IN SELECT relname AS name FROM pg_class
WHERE relkind IN ('r')
AND relname like '%_random' AND relname != 'tout_random'
LOOP
FOR obj2 IN
EXECUTE ''SELECT count(t1.id) AS total FROM '' ||
quote_ident(obj.name) ||
'' AS t1 JOIN tout_random AS t2 ON t1.id=t2.id WHERE t2.ok=TRUE''
LOOP
countresult := countresult + obj2.total;
END LOOP;
END LOOP;
RETURN countresult;
END;
' LANGUAGE 'plpgsql' ;
I doubt this is exactly what you wanted.
It looks like you were asking for the results of every count.
The only quick solution I can see for this is
populate a table with the name and count of your 40 tables.
Replace the "countresult := countresult + obj2.total;" line by
INSERT INTO countresults VALUES ( obj.name , obj2.total ) ;
and don't forget to reset the table before by
DELETE FROM countresults ;
I hope this helps for now.
Regards, Christoph
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] double linked list
I've seen CELKO's reply and find it very useful. But I cannot find anything about > BEGIN ATOMIC > DECLARE rightmost_spread INTEGER; > > SET rightmost_spread > = (SELECT rgt > FROM Frammis > WHERE part = 'G'); > ... Is this PostgreSQL at all? Any hints welcome. Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] double linked list
> > On Thursday 30 January 2003 07:10, Christoph Haller wrote: > > I've seen CELKO's reply and find it very useful. > > But I cannot find anything about > > > > > BEGIN ATOMIC > > > DECLARE rightmost_spread INTEGER; > > > > > > SET rightmost_spread > > > =3D (SELECT rgt > > > FROM Frammis > > > WHERE part = 'G'); > > > ... > > > > Is this PostgreSQL at all? Any hints welcome. > > Mr Haller > > No, this is a dialect SQL-92 (SQL-99?) that Mr Celko uses for his > examples since his solutions are vendor neutral. He is big on standards, > so posting using the standard is his way of boosting them. > > BEGIN ATOMIC is BEGIN in PG. > > I am not sure how to declare a variable in PG in normal SQL. I don't do > it that often, but when I do I do this: > > CREATE TEMPORARY TABLE Rightmost_Spread > AS SELECT rightmost_spread > FROM Frammis > WHERE part = 'G'; > > I wonder what the alternatives are? > plpgsql is the best I can think of. And thanks for the quick reply. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Question about passing User defined types to functions
> > CREATE TYPE dumby_type AS (dumby_id int4, dumby_name text); > > create function kick_dumby(dumby dumby_type) returns INTEGER AS ' > DECLARE > somenumber integer; > BEGIN > return 1; > END; > ' language 'plpgsql'; > > > Is there some way of doing this, because the above doesn't work. > After having a look into the documentation on CREATE TYPE I would say the statement looks very wrong. But I haven't done any user defined type so far, so I can't be of any help in this case. The CREATE FUNCTION statement is not considered to accept parameter names within the parameter list. So use create function kick_dumby(dumby_type) returns INTEGER AS ' DECLARE dumby ALIAS FOR $1; ... Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] "function has no parameter $1" - help.
> > I'm trying to create a trigger to enforce a constraint onto two possible > foreign tables > my function is defined as: > > <-- snip --> > CREATE OR REPLACE FUNCTION > sp_check_tranitem_productid(tbl_tranitem.fld_tranitem_id%TYPE, > tbl_tranitem.fld_tranitem_type%TYPE) RETURNS > OPAQUE AS ' > DECLARE > ---[Parameters]--- > pl_product_id ALIAS FOR $1; > pl_product_type ALIAS FOR $2; > <-- snip --> > > but when I call the function I get the error: > > function has no parameter $1 > > What is going wrong? > > (I am using PostgreSQL 7.3.1) > The documentation says about trigger procedures: PL/pgSQL can be used to define trigger procedures. A trigger procedure is created with the CREATE FUNCTION command as a function with no arguments and a return type of OPAQUE. Note that the function must be declared with no arguments even if it expects to receive arguments specified in CREATE TRIGGER --- trigger arguments are passed via TG_ARGV, as described below. Refer to that chapter for an example. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] cannot EXPLAIN query...
> > Sorry Postgresql has really made my VIEWS ugly. > It wasnt' so when i fed them. > > I wish pgsql stores the create view defination some day , > just like it does for indexes (pg_get_indexdef) > Did you ever try SELECT * FROM pg_views ; It definitely has all view definitions. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] iceberg queries
> > Does PostgreSQL optimizer handle iceberg queries well? > What do you mean by "iceberg query" ? I've never heard this term. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] pg_views
> > I'm wondering if PostgreSQL actually reparses the view definition on > each invocation or if it stores the required information in some > accessible place. > The documentation says: Whenever a query against a view (i.e. a virtual table) is made, the rewrite system rewrites the user's query to a query that accesses the base tables given in the view definition instead. > > My goal is to take a view name as input and output the tables and > columns composing the view. > I very much doubt this is possible, unless you step deep into the parser defined in gram.y and scan.l, which is as you mentioned beyond your scope. Regars, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] bytea
> > I have a table containing a field of type bytea: > > CREATE TABLE a_table ( > a_field bytea > ); > > How can I import a file in a SQL script? What function I can use? > The documentation says as in PostgreSQL 7.2.1 (I doubt this changed significantly since) Octets of certain values must be escaped (but all octet values may be escaped) when used as part of a string literal in an SQL statement. In general, to escape an octet, it is converted into the three-digit octal number equivalent of its decimal octet value, and preceded by two backslashes. In general it goes like this INSERT INTO a_table ( a_field ) VALUES ( '\\000\\001\\002\\003' ) ; to load the first four ASCII characters. You did not mention how your file looks like. There is also a C function available called PQescapeBytea which does all the required escaping to store memory areas in bytea columns. Refer to Command Execution Functions within libpq - C Library for details. Regards, Christoph ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] bytea
> > Thanks for your reply. But what I want to do is loading a file of a particular path with a sql > statement in psql. Why I need to care about how the file looks like? Thanks. > Because "non-printables" might not be properly escaped. If they are, just use the SQL COPY command as described in the doc. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Inserting a tab character
>
> --- Luke Pascoe <[EMAIL PROTECTED]> wrote:
> > I have a table which defines various possible file
> > delimiters (CHAR(1) NOT
> > NULL), for the moment it'll only contain comma and
> > tab. Inserting a comma is
> > easy, but inserting a tab is proving somewhat more
> > difficult.
> >
> > How do I do it in 'psql'?
> >
>
> --> Try using '\t' for tab.
> Example :
>INSERT INTO table1(f1) values ('\t');
>
>I'm not sure if inserting a TAB character will
> cause some side-effects for commands like COPY FROM /
> TO since these commands use tab to delimit fields.
>
If you want to be on the safe side on COPY commands,
you could change to BYTEA type.
'\t' still works on INSERT, only on retrieval it would show up as
'\011'.
Regards, Christoph
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Lock timeout detection in postgres 7.3.1
> > I have recently migrated my database from MS Sql > Server to postgresql 7.3.1. In MS SQL SERVER, it is > very easy to set the lock time equals to zero on ROW > LEVEL. So that if some other user try to access the > same data, he/she will get the error immediately. I > have tried to run the same code through VB 6.0 > (windows) using pgsql as database on RED HAT LINUX > 8.0, the only problem i am facing is when ever a user > try to access a pre LOCKED ROW, the program goes into > halt until the first user executes ROLLBACK or COMMIT. > > Is there any way to set the LOCK TIME equals to ZERO > in postgresql 7.3.1? > I'm working on PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.2 and found a similar behaviour. T1 (within psql): BEGIN; DELETE FROM ; DELETE n T2 (within psql): BEGIN; DELETE FROM ; The documentation says (within Server Runtime Environment) DEADLOCK_TIMEOUT (integer) This is the amount of time, in milliseconds, to wait on a lock before checking to see if there is a deadlock condition or not. The check for deadlock is relatively slow, so we don't want to run it every time we wait for a lock. We (optimistically?) assume that deadlocks are not common in production applications, and just wait on the lock for awhile before starting to ask questions about whether it can ever get unlocked. Increasing this value reduces the amount of time wasted in needless deadlock checks, but slows down reporting of real deadlock errors. The default is 1000 (i.e., one second), which is probably about the smallest value you would want in practice. On a heavily loaded server you might want to raise it. Ideally the setting should exceed your typical transaction time, so as to improve the odds that the lock will be released before the waiter decides to check for deadlock. This option can only be set at server start. If I get this right, the T2 psql process should terminate within one second, shouldn't it? The postgresql.conf file is as it was right after the installation #deadlock_timeout = 1000 So, I doubt this a bug, but still, there must be a misunderstanding or something else I don't know about. Could someone please enlighten us. Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Lock timeout detection in postgres 7.3.1
> > > > T1 (within psql): > > BEGIN; DELETE FROM ; > > DELETE n > > > > T2 (within psql): > > BEGIN; DELETE FROM ; > > > > ... > >I don't think there is a deadlock in the example > given above. If I'm not mistaken a deadlock occurs if > both transactions are waiting for each other to > release the lock (i.e T1 waits for T2 to release > locks/resources while T2 is also waiting for T1 to > release locks/resources. In the above example, T1 > doesn't wait for T2 to do something before finishes > the transaction (Only T2 is waiting for T1 to finish), > hence the condition for deadlock is not met. > Yupp, I agree. But from former DBMS I was dealing with, I know this SET TIMEOUT called feature, which if properly set terminated processes like that hanging on T2. Is there something comparable within Postgres? Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] SET TIMEOUT equivalent / was: Lock timeout detection
> > > > > > > T1 (within psql): > > > BEGIN; DELETE FROM ; > > > DELETE n > > > > > > T2 (within psql): > > > BEGIN; DELETE FROM ; > > > > > > > ... > > > >I don't think there is a deadlock in the example > > given above. If I'm not mistaken a deadlock occurs if > > both transactions are waiting for each other to > > release the lock (i.e T1 waits for T2 to release > > locks/resources while T2 is also waiting for T1 to > > release locks/resources. In the above example, T1 > > doesn't wait for T2 to do something before finishes > > the transaction (Only T2 is waiting for T1 to finish), > > hence the condition for deadlock is not met. > > > Yupp, I agree. > But from former DBMS I was dealing with, > I know this SET TIMEOUT called feature, which if properly set > terminated processes like that hanging on T2. > Is there something comparable within Postgres? > Sorry to bother again with my question. Is it too stupid or trivial to this list? Should I send it to NOVICE? Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] conversi ms-sql7 vs postgresql 7.3
> > I have table xx: > id debet credit balance > 1 10000 0 > 2 2000 0 0 > 3 0 2500 0 > 4 0 100 0 > > command in ms-sql 7 can use calculate field (column) balance from id=1 to > id=4: > "update xx set bal=balance=bal+debet-credit" > result: > id debet credit balance > 1 10000 1000 > 2 2000 0 3000 > 3 0 2500 500 > 4 0 100 400 > > How command sql can use in psotgresql 7.3? > Try UPDATE xx SET balance=balance+debet-credit ; Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Lock timeout detection in postgres 7.3.1
> > PostgreSQL 7.3 Documentation > 3.4. Run-time Configuration > STATEMENT_TIMEOUT (integer) > Aborts any statement that takes over the specified number of milliseconds. A value of zero turns off the timer. > DEADLOCK_TIMEOUT (integer) > This is the amount of time, in milliseconds, to wait on a lock before checking to see if there is a deadlock condition > > > In this case I suppose 2 things: > - table has a lot of records and you should just wait to finish operation. > - another query locked the table and it is realy a deadlock > Thanks Tomasz for pointing this out. STATEMENT_TIMEOUT is exactly what I was looking for. I should have had a look into the 7.3 Doc on my own. Regards, Christoph ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Porting from db2 problem
> > I am trying to port an app that currently runs over db2 and oracle (on windows > os/2 linux hpux aix etc) to also run over postgres. > I am currently porting the windows client (using odbc). > I am having major problems because of the lack of with hold cursors (cursors > that remain open over a commit) in postgres. There are a lot of places where > the app will - open a cursor with hold - fetch the first 20 or so rows, allow > the user to scroll thru the result set, updating and commiting changes to > particular rows as they go. When the user gets to the end of the current set of > fetched rows - the app will fetch the next 20 or so during a scroll down > operation etc. The user may hold the read-only cursor over the result-set all > day, and it is important that rows that they do update, be immediately > available for other users. > > What do folk do to support this kind of scenario in the abscence of with hold > cursors ? Have a look at SELECT ... FOR UPDATE ... LIMIT ... OFFSET ... ; > Is implementation of with hold cursors likely in the near future? > I have no idea. Sorry. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Help with query involving aggregation and joining.
> > ERROR: > SELECT DISTINCT ON expressions must match > initial ORDER BY expressions may be gotten over by > wrapping the first query result in a subselect. > > not sure though if its proper. > regds > mallah. > > test=# SELECT * from ( SELECT distinct on (a.id) b.id > ,courseid,name,submission from course a join history b on > (a.id=b.courseid) ) as results order by results.submission desc; > I'm currently working with select version(); version --- PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.2 (1 row) I've used your table definitions and sample data you've sent earlier and do not receive an error message on the above SELECT statement. Did you? Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] using Avg()
> > I have a table with a column named SwErr (Switch Error) with int values > date with datetime values and SwID with char(3) > I am trying to get a subset back where the folowing is true > > select the avg(SwErr) for the last 30 days where SwId = 'XX1' > Select the value of todays value of SwErr where SwId = 'XX1' > if todays value of SwErr is greater than 4 times the SwErr Average return in > the value > Looks like you could use a plpgsql function, something like (untested) CREATE OR REPLACE FUNCTION get_dated_SwErr(CHAR) RETURNS INTEGER AS ' DECLARE thisSwID ALIAS FOR $1; todaysSwErrINT; avgSwErr INT; avg4SwErr INT; dateLimit TIMESTAMP; BEGIN SELECT INTO dateLimit current_date - ''30 days''::interval ; SELECT INTO todaysSwErr SwErr FROM WHERE SwID = thisSwID AND SwDate = current_date ; SELECT INTO avgSwErr AVG(SwErr)::int FROM WHERE SwID = thisSwID AND SwDate BETWEEN current_date AND dateLimit ; avg4SwErr := avgSwErr * 4 ; IF todaysSwErr > avg4SwErr THEN RETURN todaysSwErr; ELSE RETURN avgSwErr; END IF; END; ' LANGUAGE 'plpgsql' ; then SELECT SwID, get_dated_SwErr(SwID) FROM WHERE SwID = 'XX1' AND SwDate = current_date ; should bring up the result. Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to notice column changes in trigger
> > >So you want the trigger executed only if assignments to b and/or c do > >not appear within the update command. Right? > > > > Right, that's what I want. > I'm afraid I have no idea how to accomplish that. Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Create function statement with insert statement
Hello Susan,
>
> The relevant code for creating the hexorders table (and associated
> constraints) is:
>
>
> DROP TABLE HEXORDERS ;
>
> DROP SEQUENCE HEXORDERS_SEQ ;
>
> CREATE SEQUENCE HEXORDERS_SEQ START 1 ;
>
> CREATE TABLE HEXORDERS (
>ORDER_ID INTEGER DEFAULT NEXTVAL('HEXORDERS_SEQ') NOT NULL,
>CUSTOMER_ID INTEGER NOT NULL,
>ORDER_AMOUNT NUMERIC(12,2),
>ORDER_DISCOUNT_CODE CHARACTER(1),
>ORDER_KEY VARCHAR(255),
>DISTRIBUTOR_ID INTEGER,
>ORDER_GST NUMERIC(12,2),
>ORDER_SHIPPING_COST NUMERIC(12,2),
>ORDER_DATE DATE DEFAULT CURRENT_DATE,
>ORDER_VALID BOOLEAN DEFAULT 'FALSE',
>ORDER_SHIPPING_DATE DATE,
>ORDER_DELIVERY_DATETIME TIMESTAMP,
>ORDER_FREIGHT_COMPANY VARCHAR(30),
>ORDER_CLOSE_DATE DATE );
>
>
> ALTER TABLE HEXORDERS ADD CONSTRAINT HEXORDERS_CONSTRAINT_PK
> PRIMARY KEY ( ORDER_ID );
>
>
> ALTER TABLE HEXORDERS ADD CONSTRAINT
> HEXORDERS_CONSTRAINT_FK1 FOREIGN KEY ( CUSTOMER_ID ) REFERENCES
> HEXCUSTOMERS ( CUSTOMER_ID ) MATCH FULL ;
>
>
> ALTER TABLE HEXORDERS ADD CONSTRAINT HEXORDERS_CONSTRAINT_FK2
> FOREIGN KEY ( DISTRIBUTOR_ID ) REFERENCES HEXDISTRIBUTORS
> ( DISTRIBUTOR_ID ) MATCH FULL ;
>
Within my postgres environment
PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.2
I've created table HEXORDERS and added HEXORDERS_CONSTRAINT_PK,
and did not add HEXORDERS_CONSTRAINT_FK1 nor HEXORDERS_CONSTRAINT_FK2,
because of no idea how HEXCUSTOMERS resp. HEXDISTRIBUTORS look like.
Then I did successfully
CREATE OR REPLACE FUNCTION orderinsert(INTEGER, VARCHAR) RETURNS
INTEGER AS '
INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID,
ORDER_AMOUNT, ORDER_GST ) VALUES ( $1, $2, 1, 0, 0 ) ;
SELECT 1 ;
' LANGUAGE SQL ;
Even a
SELECT orderinsert( 123,'abcdef' );
worked as intended (one row inserted).
Nothing about "parse error at or near ;"
So you find me pretty clueless about what's going wrong on your side.
Did you search the archives for hints on strange parser errors?
Regards, Christoph
PS Keep on posting to the list, maybe somebody else knows more.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] [SQL] What's wrong with this group by clause?
>
> On Tue, 4 Nov 2003 18:28:12 -0300, Franco Bruno Borghesi
> <[EMAIL PROTECTED]> wrote:
> >Below you can find a simplified example of a real case.
> >I don't understand why I'm getting the "john" record twice.
>
> ISTM you have found a Postgres 7.3 bug.
>
> I get one john with
> PostgreSQL 7.1.3 on i686-pc-cygwin, compiled by GCC 2.95.3-5
> and
> PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.7.2.1
>
> but two johns with
> PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.7.2.1
>
> >/*EXAMPLE*/
> >CREATE TABLE people
> >(
> > name TEXT
> >);
> >INSERT INTO people VALUES ('john');
> >INSERT INTO people VALUES ('john');
> >INSERT INTO people VALUES ('pete');
> >INSERT INTO people VALUES ('pete');
> >INSERT INTO people VALUES ('ernest');
> >INSERT INTO people VALUES ('john');
> >
> >SELECT
> > 0 AS field1,
> > 0 AS field2,
> > name
> >FROM
> > people
> >GROUP BY
> > field1,
> > field2,
> > name;
> >
> > field1 | field2 | name
> >++
> > 0 | 0 | john
> > 0 | 0 | pete
> > 0 | 0 | ernest
> > 0 | 0 | john
> >(4 rows)
>
PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.2
SELECT 0 AS field1, 0 AS field2,name FROM people GROUP BY
field1, field2, name;
field1 | field2 | name
++
0 | 0 | ernest
0 | 0 | john
0 | 0 | pete
(3 rows)
PostgreSQL 7.3.2 on hppa-hp-hpux10.20, compiled by GCC 2.95.2
SELECT 0 AS field1, 0 AS field2,name FROM people GROUP BY
field1, field2, name;
field1 | field2 | name
++
0 | 0 | john
0 | 0 | pete
0 | 0 | john
0 | 0 | pete
0 | 0 | john
0 | 0 | ernest
(6 rows)
I doubt this is a bug in 7.3.2 but in prior versions.
I've cross-checked how another DBMS (HP's ALLBASE) handles GROUP BY
without an aggregate, and it acts like 7.3.2.
Regards, Christoph
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Formatting intervals..
> >
> > Is it possible to customize interval display.
> >
> > eg,
> >
> > tradein_clients=# SELECT cast ('10-10-1999'::timestamp -
'1-1-1999'::timestamp AS interval);
> > +--+
> > | interval |
> > +--+
> > | 282 days |
> > +--+
> > (1 row)
> >
> > can i display it in months and days..
> It doesn't make sense. You don't have whole date, so how long should
> month be? 28? 29? 30? 31?
>
It does. As long the months between January and October are.
So
select age ('10-10-1999'::timestamp , '1-1-1999'::timestamp ) ;
age
---
9 mons 9 days
(1 row)
Regards, Christoph
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] the best way to get some records not in another table
> > Try to get some records not in another table. As the following, please > advise which one will be the best way to do. Or is there any other way to do > better? > > SELECT DISTINCT a.c1 > FROM test_j2 a > WHERE a.c1 NOT IN (SELECT DISTINCT b.c1 FROM test_j1 b); > > SELECT a.c1 FROM test_j2 a > EXCEPT > SELECT b.c1 FROM test_j1 b; > IN resp. NOT IN clauses are known to be slow. SELECT DISTINCT a.c1 FROM test_j2 a WHERE NOT EXISTS (SELECT b.c1 FROM test_j1 b WHERE b.c1 = a.c1) ; Can't tell if EXISTS performs better than EXCEPT, have a look at the EXPLAIN output. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Casting with character and character varying
> > Hi all. > Recently I face some problem with casting character type variable and > varchar variable. > The situation was like: I had 2 table, on table A, the user_name is defined > as character(32), and table B uses varchar(32). I have 1 function and a > trigger to manipulate with these data. > > Here's the function: (NEW = tableB) > -- > create or replace function prepaid () returns trigger as ' > declare Rec tableA%ROWTYPE; > > begin > if NEW.status != 2 then >return NEW; > else > select into Rec * from tableA where user_name = trim(trailing '' '' from > cast(NEW.user_name as varchar)) and user_type = ''T''; >if not found then > return NEW; >end if; > >insert into temptable values (tableA.FieldA); > end if; > return NEW; > end; > ' language 'plpgsql'; > - > supposingly the insert will insert the value of field A in table into > temptable (declare as varchar(100)), instead of inserting single row, the > insert actually insert all data from tableA to temptable (if there's 10 row > in tableA, the insert statement will insert all to temptable), that's weird. > > Then i tried with cast(trim(trailing '' '' from NEW.user_name)::varchar as > text), and it's returns me with nothing (suppose there'll be 1 record > matched). > Don't know what's actually right now: If tableA uses character and tableB varchar, you'll have to trim the user_name from tableA not tableB, because varchar is already trimmed. But what you're doing within the function code is trimming a varchar field. Second is, what is "tableA.FieldA"? Is it a column name of tableA? Looks that way, because I can't see a variable of this name. I'm not sure what happens on an insert statement like this, but it's very well possible this causes all row-columns FieldA from tableA to be inserted into temptable. And it would be useful to see the CREATE TRIGGER statement too. Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Seeking help with a query....
> > Hi folks, seeking help with a query that I thought was simple, but > apparantly isn't, at least for someone with my knowledge level. > > Given a table : > > create table atable ( > code1 char, > code2 char, > costint > ); > > And the rows > > code1code2cost > - > ab2 > de4 > ba6 > fg1 > > I need a ( preferably single ) query that will sum the costs for any > matching pairs of codes regardless of order. That is, row 1 and row 3 > concern the same pair of unordered codes (a,b), and the result should show > that the (a,b) pair had a summed cost of 8. I am not able to change any of > the environment or preconditions other than the query itself. I have tried > so many approaches that they aren't even worth listing. Any suggestions > would be very much appreciated. > My approach is inspired by PostgreSQL 7.3 Set Returning Functions by Stephan Szabo ( http://techdocs.postgresql.org/guides/SetReturningFunctions ) Use a set returning function to get ordered pairs and do a group by then as create type aholder as ( code1 char, code2 char, costint ); create or replace function aordered() returns setof aholder as ' declare myrow aholder%ROWTYPE; codex char; begin for myrow in select code1,code2,cost from atable loop if myrow.code1 > myrow.code2 then codex := myrow.code1; myrow.code1 := myrow.code2; myrow.code2 := codex; end if; return next myrow; end loop; return; end ' language 'plpgsql'; select code1,code2,sum(cost) from aordered() group by code1,code2 ; code1 | code2 | sum ---+---+- a | b | 8 d | e | 4 f | g | 1 (3 rows) In addition if you want only matching pairs (eliminate single pairs) try create or replace function amatched() returns setof aholder as ' declare myrow aholder%ROWTYPE; codex char; begin for myrow in select t1.code1,t1.code2,t1.cost from atable t1,atable t2 where t1.code1=t2.code2 and t1.code2=t2.code1 loop if myrow.code1 > myrow.code2 then codex := myrow.code1; myrow.code1 := myrow.code2; myrow.code2 := codex; end if; return next myrow; end loop; return; end ' language 'plpgsql'; select code1,code2,sum(cost) from amatched() group by code1,code2 ; code1 | code2 | sum ---+---+- a | b | 8 (1 row) Hope this helps. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] query 2 database
> > Can anybody help me, can't i make "query > 1 table in different database > (but not schema)" in postgre 7.3? > see $PGSQLD/contrib/dblink/ Regards, Christoph ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How to show timestamp with milliseconds(3 digits) in Select
> > This is the query and result: > > select h323_setup_time from pv_legs_new where h323_setup_time = '2003-01-01 > 00:43:59.996-05'; > > h323_setup_time > --- > 2003-01-01 00:43:60.00-05 > > Actually, the real data of second and millisecond is 59.996-05, but it shows > 60.00. I failed to load it when I use this result in other query. > I'm using 7.3 and it works as expected: create table tstamptest ( tstampcol timestamp ); insert into tstamptest values ( '2003-01-01 00:43:59.996-05' ); select * from tstamptest ; tstampcol - 2003-01-01 00:43:59.996 The 7.1 doc does not say anything about microsecond representation nor storage, 7.3 has Note: When timestamp values are stored as double precision floating-point numbers (currently the default), the effective limit of precision may be less than 6, since timestamp values are stored as seconds since 2000-01-01. Microsecond precision is achieved for dates within a few years of 2000-01-01, but the precision degrades for dates further away. When timestamps are stored as eight-byte integers (a compile-time option), microsecond precision is available over the full range of values. So I can only offer the standard advice: upgrade to 7.3. Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] howto/min values
> My postings seem to double on me. It's standard procedure to reply twice, one to the list and one to the sender, because you don't need to subscribe to the list to get the answers. But if you are subscribed and ask a question you usually get two. > > Now this works. So going to the next question, if distance is a calculated = > field how can I use it in the subquery. How do you add another alias in: > > select p.points, p.id, (x-x) as distance=20 > from table as p > where distance =3D (select min(distance)=20 > from table as p1 > where p.points=3Dp1points) > order by points, id > =20 > This of course doesn't work, because of the referring to the calculated fie= > ld wich is not actually a part of the table. Can you do this somehow or is = > it impossible? > No, not at all. Something like select p.points, p.id, p.distance from (select points, id, (x-x) as distance from table) as p where p.distance = (select min(p1.distance) > from table as p1 > where p.points=p1.points) > order by p.points, p.id ; should do the trick. The FROM clause allows any kind of subqueries within parentheses. Regards, Christoph ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Insert multiple Rows
>
> If you use a 'prepared' insert within a transaction, would that speed
things
> up - maybe by defering index updates?
>
I doubt it.
From: Neil Conway <[EMAIL PROTECTED]>
... I'd suspect that for most INSERT queries, the
parsing/rewriting/planning stages are relatively inexpensive, and the
bulk of the runtime is spent in the executor (and writing WAL records,
etc). So my guess would be that you won't see that much of a performance
improvement from this...
I've noticed a real performance boost using COPY
something like ...
PQexec("COPY xxx FROM stdin");
for (...)
{
sprintf(buf, "%d\t%d\t...\n",
values[0], values[1], ... values[n]);
PQputline(conn, buf);
}
PQputline(conn, "\\.\n");
PQendcopy(conn);
runs like hell.
Regards, Christoph
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] string variable with spaces in psql
> > I need to define a variable in psql, but the variable needs to contain a > space: > > This is what I want to do; > > \set theName '\''John Nobody '\'' > SELECT add_agent( :theName, 'Test Company', 'Test Department' ); > > ... > etc > > Unfortunately psql removes the space, so that theName = 'JohnNobody' > > What do i need to do to preserve the space? > Did you try \set theName "John Nobody " Regards, Christoph ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Finding if a temp table exists in the current connection
> > Is there a way (i.e., access theinternal pg_ tables) to find out if a > particular temp table already exists (in the current connection)? > > I have written some stored procedures that check if a table exists and if a > column exists in a table. This is used so I can perform upgrades of our > system (if table doesnt exist, call create table... sql). > > I would like to do a similar thing for temp tables. > > I have noticed that if another connection creates a temp table, it will > show up in the pg_* tables so that all ocnnections can see the table. > > Is there some standard SQL way to test? > I'm using 7.3.2 and it seems there is a way. I've found out by starting a psql session using the -E option. This shows you the SQL behind \dt which shows the current temp tables. It looks like they are created in name spaces called "pg_temp_, where N is simply a connection counter. SELECT n.nspname as "Schema",c.relname as "Name" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace where n.nspname like 'pg_temp_%' AND pg_catalog.pg_table_is_visible(c.oid) ; If I do (within 1st connection) create temp table tgif (dummy int); and the select above returns Schema | Name ---+-- pg_temp_1 | tgif (1 row) And within a 2nd connection create temp table tgif (dummy int); and the select above returns Schema | Name ---+-- pg_temp_2 | tgif (1 row) Does this help? Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How Do I Hide Trigger ??
> > Dear Milist,=20 > For the security reason, i'd like=20 > to Hide or Encrypting some Function, > triggers or procedure on tables pg_proc,=20 > So If I have many user on my database, > there will be one user could see the > "prosrc" on pg_proc or could do select=20 > the pg_proc without encrypted. > > Any advice will be helpfull. > Would this help? revoke all privileges on pg_proc from public; Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] find open transactions/locks in 7.2?
> > [select version() --> PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 3.0.4] > > I'm getting hangups every day or so, I presume due to some open transaction > that insert/update/delete'ed on a table that is used by my main app without > a commit. > > Is there some way (in 7.2!) to find who's locking what or who has > a transaction open? > AFAICT No. There has been a thread on a similar topic [HACKERS] Can pessimistic locking be emulated? but it was all referring to 7.3. Anyway, you might want to check. Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to make a IN without a table... ?
> > i ran today in a problem when doing some (i mean too much for me) advanced sql... > > What i want to do is something like this: > > SELECT > my_var1, > my_var2, > my_function(my_var1, my_var2) > FROM ( > SELECT > '1', > '2', > '3', > '4' > ) AS my_var1_values, > ( > SELECT > '1', > '2', > '3', > '4' > ) AS my_var2_values > > In short, i want to calculate the result of the function my_function for > some values of my_var1, cross by some values of my_var2. > These values are not taken in a table, but put in directly. > They are a lot, so i would prefer not to write the whole thing, line > after line. (Let's say 10 values for the first, and 40 for the second => > 400 lines of code to maintain...) > > I really don't see how to do this :-/ > What about using a TEMP TABLE? And COPY FROM STDIN offers a real fast way to populate. BTW, why using SQL at all? Couldn't perl do the job much easier? Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to make a IN without a table... ?
> > > These values are not taken in a table, but put in directly. > > I guess i wasn't clear enough. > "my_function" here is a function which calculate some results about some > datas from the database. > Example : a function which would calculate the sum of the money (which > would be stored in the database by movement, ie amount + date + cause + > account) spent by user my_var1 during period my_var2. > I am not sure I'm getting it right. The first statement suggests the data is coming from outside the database. The second suggests it is derived from the database though. And now it sounds like you could use table functions. Can you confirm that? Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] How to make a IN without a table... ?
> > Like "Tell me how much i spent between the 4th and the 7th og this month > ?" uses 4 and 7 as arguments, but need to fetch the datas in the > database. > Clear enough ? :-) Yes. > > What do you mean by table function ? Table functions are aka SetReturningFunctions. Refer to http://techdocs.postgresql.org/guides/SetReturningFunctions Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] columnar format
> > i would like to ask for the sql statement to the output: > > ITEM DESC Jan Feb Mar Apr ... Sep Total > xx 999 999 999 999 ... 999 9,999 > > where "Jan" column is sum of all "x" items purchased on Jan, "Feb" column as > sum of Feb purchases, and so on up to "Sep", and "Total" as the total from > "Jan" to "Sep" (as in the case here). the numbers may/may not be formatted, > and the period varies, depending on the range. > Search the archives for pivot tables resp. crosstab and/or have a look at $PGSQLD/contrib/tablefunc/README.tablefunc Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Non-Blocking Locks (i.e. Oracle NOWAIT)
> > though this question has been asked several times before (but never really > answered), I have to give it another try. > > I have a multi-user application which synchronizes access to certain datasets > via the database itself. If a user has a project in that application open no > other user should be able to work on it too. When developing the application I > considered the database to be a place to handle the synchronization since > transactions are (normally) an integral part of a database system. When a user > opens a project the application firstly locks a corresponding row. But since I > don't want the application to block if that row is already locked I use the > Oracle "NOWAIT" feature (SELECT ... FOR UPDATE NOWAIT) which tries to receive > the lock on the specified row and if that row is already locked it returns > with an error without blocking and I can tell the user that the project is > already in use. > > Now that the application is to be ported to PG I need a similar functionality. > Is there that an animal? If not, what would you recommend? > PostgreSQL 7.3.2 T1: begin; select * from foo for update; T2: set STATEMENT_TIMEOUT = 1000; -- milliseconds T2: select * from foo for update; T2: ERROR: Query was cancelled. HTH, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] time delay function
> > Pseudo code: > > begin trans > select * from table1 > WAIT FOR 20 SECS > update table1 set blah = 'blah' > end transcation > > In pgplsql, Im looking for something like a function that I can use to make the process to wait for 20 secs before con tinuing to execute the next sql statment? > AFAIK there is no such thing. But probably you can write a C function, which waits for N seconds and which can be called by your plpgsql function. Regards, Christoph ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Problem using Subselect results
> I want to use the result of a subselect as condition in another one. > > table1: a,b > table2: a,c > > CREATE VIEW my_view AS SELECT b,c > (SELECT a, b FROM table1 WHERE b=3D1) my_ab, > (SELECT c FROM table2, my_ab WHERE table3.a=3Dmy_ab.a) my_c; > > this is just an example - i know i could cross join this one, but i need = > to=20 > refer to the results of several subselects in several other. > > > does return "relation my_ab unknown". it is not just a problem of executi= > on=20 > order - if i turn it the other way round it's still the same. > > Am I just trying to do something really stupid? And what for is the (nece= > ssary)=20 > AS statement for subselects, if it's not possible to access their results= > by=20 > that name? > > And as I need the result of a subselect in several other subselects it's= > not=20 > possible to transform them into a cascade of sub, subsub, subsubsub s= > elects. > Any ideas?=20 > Does this match your intentions: CREATE VIEW my_view AS SELECT b,c FROM (SELECT b,c FROM table2, (SELECT a, b FROM table1 WHERE b=3D1) my_ab WHERE table3.a=3Dmy_ab.a) my_c; I assume the reference table3.a is a typo. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] time precision.
> I'm using PG-7.2.4 on Solaries. > When I do: > > template1# select time(6576); > ERROR: TIME(6576) precision must be between 0 and 13 > > Where am I wrong? What's the purpose of this function? And where did you find it in the docs? I didn't see it before. Regards, Christoph ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] One to many query question
> > On Wed, Jul 30, 2003 at 05:26:23PM -0400, Dmitry Tkach wrote: > > >How do I write a query to find all CDs that are NOT Rock? > > > > > What about > > select * from cd where not exists (select 1 from cd_genres where cd_id > > = cd.id and genre='Rock')? > > Thanks everyone! This did indeed work, and it does seem clearer. I > never knew about EXISTS before. How portable is this? I'm interested > in supporting PostgreSQL and MS SQL Server for now, and possibly > Oracle and MySQL in the future. > It should be portable completely. It's SQL standard. Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] NEW and a subselect in a rule
> > So, I met such a problem: it's impossible to use NEW in a subselect > used in a (non-select) rule. The error is: exist>. Is this a way to do that newertheless (without using of a > function, of course)? > Could we see the CREATE RULE command causing the error? Regards, Christoph ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Fwd: Bad Join moment - how is this happening?
> I have a view: > > create or replace view addenda as > select > documents.id, > documents.oid, > documents.projects_id, > documents.doc_num, > documents.description, > documents.date, > documents.createdate, > documents.moddate, > documents.people_id, > documents.parent, > documents.document_type, > documents.state, > documents.machines_id, > documents.phases_id, > > d_addenda.item_num, > d_addenda.drawing_reference > > from > d_addenda as a, documents as d > where a.documents_id = d.id; > Doing this you should have got NOTICE: Adding missing FROM-clause entry for table "d_addenda" NOTICE: Adding missing FROM-clause entry for table "documents" So it's pretty useful to write psql's output to a file when creating relations and check for NOTICE messages. exec 3>/tmp/psql.out;$PGSQLD/bin/psql 1>&3 2>&3 NOTICE messages appear to be sent to stderr. As the previous posters already made clear create or replace view addenda as select documents.id, documents.oid, documents.projects_id, documents.doc_num, documents.description, documents.date, documents.createdate, documents.moddate, documents.people_id, documents.parent, documents.document_type, documents.state, documents.machines_id, documents.phases_id, d_addenda.item_num, d_addenda.drawing_reference from d_addenda , documents where d_addenda.documents_id = documents.id; resp. create or replace view addenda as select d.id, d.oid, d.projects_id, d.doc_num, d.description, d.date, d.createdate, d.moddate, d.people_id, d.parent, d.document_type, d.state, d.machines_id, d.phases_id, a.item_num, a.drawing_reference from d_addenda as a, documents as d where a.documents_id = d.id; should match your intentions. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] length of recordset read through a cursor
> > After declaring a cursor, one way of obtaining the length of the resultset > is to perform a "MOVE 0" and read the PQcmdStatus which returns a "MOVE nn" > where nn is the length of the resultset. (A negative MOVE can then be used > to allow starting to fetch records from the beginning of the resultset.) > > Is there another, possibly faster way? > Looks like you're using libpq (because you mention PQcmdStatus), then after declaring a cursor and FETCH ALL, try 1.3.4. Retrieving SELECT Result Information PQntuples Returns the number of tuples (rows) in the query result. int PQntuples(const PGresult *res); I'm not exactly sure what you're trying to achieve or going to do, so if I misunderstood you, ask again. Regards, Christoph ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Problem with looping on a table function result
>
> I've met the following problem.
> I had successfully written a function divide_into_days(timestamp,
timestamp)
> which returns setof (timestamp, timestamp) pairs - a list of days the
> given interval is divided into.
>
> What I want is to use each record from resultset to pass to another
> function, something like:
>
> SELECTdays.*, summary_stats(days.day_start, days.day_end)
> FROM divide_into_days('2003-06-01', '2003-07-01') days;
>
> The problem is that summary_stats function returns a record, so I have
> to use SELECT * FROM summary_stats(...). I can't use the following
too:
>
> SELECT*
> FROM summary_stats(days.day_start, days.day_end) stats,
> divide_into_days('2003-06-01', '2003-07-01') days;
>
> (there was a discussion a few days ago about using subselects,
> but here's a slightly different case).
>
> I wonder if where's a way to do the trick without writing one more
> PL/PgSQL table function doing FOR row IN SELECT ... LOOP or using
> client-side loop?
>
Did you see
http://techdocs.postgresql.org/guides/SetReturningFunctions
Regards, Christoph
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] length of recordset read through a cursor
> > >> After declaring a cursor, one way of obtaining the length of the > >resultset > >> is to perform a "MOVE 0" and read the PQcmdStatus which returns a > >"MOVE nn" > >> where nn is the length of the resultset. (A negative MOVE can then be > >used > >> to allow starting to fetch records from the beginning of the > >resultset.) > >> > >> Is there another, possibly faster way? > >> > >Looks like you're using libpq (because you mention PQcmdStatus), > >then after declaring a cursor and FETCH ALL, try > > > >1.3.4. Retrieving SELECT Result Information > > > >PQntuples Returns the number of tuples (rows) in the query result. > > > >int PQntuples(const PGresult *res); > > > >I'm not exactly sure what you're trying to achieve or going to do, > >so if I misunderstood you, ask again. > > > >Regards, Christoph > > Thanks for your reply. > What I'm trying to do is the following: I want to browse through a view > containing more than 1 records. To avoid slowing things down too much, > I would like my client program to receive (through the network) only the > records that are to be displayed on the screen. I believe I could do this > by declaring a cursor and then fetching the parts of the resultset I need. > It would be useful to know the size of the resultset immediately after the > cursor has been declared. How do I get this information? I could of course > fetch all of the resultset, but that is what I am trying to avoid. > Shouldn't it be quicker to perform a move through the set than fetching it? > I found that moving zero records results in a move to the end of the > resultset, with a command status returning the number of records moved. > Although I expected this method to take less time than a fetch (does it?), > I was wondering if there might be another way to get the size of the > resultset that can be fetched through the declared cursor. > I do not know about the internals of FETCH or MOVE. To me your first approach looks reasonable. I doubt there is way to learn about the size of a resultset simply by declaring a cursor (because there is no query taking place yet). You may like to send your request to the performance list. Regards, Christoph ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] sub-sel/group problem
> > select r.rtid, concat(r.rcid::text || ' on ' || l.lnumber::text) as task > from rides r, loco_dets l where r.rlid = l.lid group by rtid; > Looks like another implicit FROM clause mystery. Try ... GROUP BY r.rtid ; The same goes for the JOIN. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Insert a description while creating a table
> > I want to insert descriptions at the columns of my tables but
without
> > using the command COMMENT ON. I want to do it together with the
table
> > creation. Is that possible?
> >
> > I wanna do something like this:
> >
> > create table test (
> > id serial 'Descripitions about ID',
> > name varchar(50) 'Descriptions about NAME'
> > );
>
> Probably not going to happen in the backend.
>
> However, you should be able to accomplish that with a little bit of
Perl
> to pre-process the SQL.
>
That perl script comes to my mind too.
The reason why Luiz doesn't like it, may be because you can't
see these descriptions within psql using \dd test
I did
the create table, then
COMMENT ON COLUMN test.id is 'Descripitions about ID';
COMMENT ON COLUMN test.name is 'Descriptions about NAME';
\dd test shows
Object descriptions
Schema | Name | Object | Description
+--++-
(0 rows)
This is odd. OK, I know the doc says
\dd [ pattern ]
Shows the descriptions of objects matching the pattern, or of all
visible objects if no argument is given. But in either case,
only objects that have a description are listed. ("Object" covers
aggregates, functions, operators, types, relations (tables, views,
indexes, sequences, large objects), rules, and triggers.) For
example:
Nothing about columns.
But what is the purpose of comments on columns if you can only get them
via
select * from pg_description where
objoid = (select typrelid from pg_type where typname='test')
order by objsubid ;
objoid | classoid | objsubid | description
+--+--+-
17326 | 1259 |1 | Descripitions about ID
17326 | 1259 |2 | Descriptions about NAME
(2 rows)
which you'll have to find out on your own.
Regards, Christoph
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Insert a description while creating a table
> On Wed, Aug 13, 2003 at 02:54:59PM +0200, Christoph Haller wrote:
> > \dd test shows
> >
> > Object descriptions
> > Schema | Name | Object | Description
> > +--++-
> > (0 rows)
> >
> > This is odd. OK, I know the doc says
> > \dd [ pattern ]
> >
> > Shows the descriptions of objects matching the pattern, or of
all
> > visible objects if no argument is given. But in either case,
> > only objects that have a description are listed. ("Object"
covers
> > aggregates, functions, operators, types, relations (tables, views,
> > indexes, sequences, large objects), rules, and triggers.) For
> > example:
> >
> > Nothing about columns.
> > But what is the purpose of comments on columns if you can only get
them
> > via
> > select * from pg_description where
>
> Have you tried \d+ tablename? (sorry for the long lines ";-)
>
I apologize for complaining, missed that completely.
Thanks to Reinoud for pointing this out.
Regards, Christoph
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
