Re: [SQL] function returning a cursor and a scalar
Thanks much Alvaro. Didn't know I could do that. For returning the matches, I am planning to run a separate count(*) query with the original WHERE clause minus joins that are purely for pulling addnl attributes from related entities. Only if this query returns a positive count, I will run the query that actually gets the data. I google'd some and couldn't find a way to avoid two queries. Best Regards, Surajit Bhattacharjee __ Mobile: 508-277-9091 VOIP: 9032 -Original Message- From: Alvaro Herrera [mailto:[email protected]] Sent: Monday, July 13, 2009 2:22 PM To: Surajit Bhattacharjee Cc: [email protected] Subject: Re: [SQL] function returning a cursor and a scalar Surajit Bhattacharjee wrote: > I am new to Postgresql and am trying to write a function which will do a > search and return the first page of results along with the total number > of matches. How can I make my function return a cursor AND a scalar - > can I do the scalar as an OUT param and then make the function > explicitly RETURN a refcursor? Why wouldn't you just use two OUT params? BTW how do you plan on returning the number of matches? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support - This message, including any attachments, contains confidential information intended for a specific individual and purpose, and is intended for the addressee only. Any unauthorized disclosure, use, dissemination, copying, or distribution of this message or any of its attachments or the information contained in this e-mail, or the taking of any action based on it, is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail and delete this message. - -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Request new version to support "on commit drop" for create temp table ... as select ?
Good morning, I googled to find that "on commit drop" does not support: (a) create temp table as select * from table1 where 1<>2; http://archives.postgresql.org/pgsql-sql/2005-09/msg00153.php If table1 has complex table structure, grammar(a) will save lots of codes - col1 varchar(1), col2, . colN I just wonder would the new version support "on commit drop" for select? Thanks a lot! -- Lu Ying -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Request new version to support "on commit drop" for create temp table ... as select ?
Emi Lu writes: > I googled to find that "on commit drop" does not support: > (a) create temp table as select * from table1 where 1<>2; > http://archives.postgresql.org/pgsql-sql/2005-09/msg00153.php Ah, the pitfalls of believing that the first google hit you get is authoritative. Didn't you notice that message was from 2005? regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Request new version to support "on commit drop" for create temp table ... as select ?
On Tue, Jul 14, 2009 at 10:47 AM, Tom Lane wrote: > Emi Lu writes: >> I googled to find that "on commit drop" does not support: > >> (a) create temp table as select * from table1 where 1<>2; >> http://archives.postgresql.org/pgsql-sql/2005-09/msg00153.php > > Ah, the pitfalls of believing that the first google hit you get > is authoritative. Didn't you notice that message was from 2005? That's what I thought, but create temp table xyz as select * from abc on commit drop; still fails on 8.3. Was this fixed in 8.4 or is my syntax wonky? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Request new version to support "on commit drop" for create temp table ... as select ?
Scott Marlowe writes: > That's what I thought, but > create temp table xyz as select * from abc on commit drop; > still fails on 8.3. Was this fixed in 8.4 or is my syntax wonky? Your syntax is wonky -- switch the clause order. http://www.postgresql.org/docs/8.3/static/sql-createtableas.html regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Request new version to support "on commit drop" for create temp table ... as select ?
Got it. Thank you Tom! Scott Marlowe writes: That's what I thought, but create temp table xyz as select * from abc on commit drop; still fails on 8.3. Was this fixed in 8.4 or is my syntax wonky? Your syntax is wonky -- switch the clause order. http://www.postgresql.org/docs/8.3/static/sql-createtableas.html regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
