Re: [HACKERS] Dynamic result sets from procedures

Peter Eisentraut wrote:

> There is also one need error that needs further investigation.

I've looked at this bit in the regression tests about \gexec:

--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -232,11 +232,7 @@ union allselect 'drop table gexec_test', 'select ''2000-01-01''::date as party_over'\gexecselect 1
asones 
- ones
-------
-    1
-(1 row)
-
+ERROR:  DECLARE CURSOR can only be used in transaction blocks

This can be interpreted as two separate errors:

* \gexec ignores the first result

postgres=# select 'select 1','select 2' \gexec?column?
----------2
(1 row)

* \gexec fails with FETCH_COUNT
 postgres=# \set FETCH_COUNT 1 postgres=# select 'select 1','select 2' \gexec ERROR:  DECLARE CURSOR can only be used
intransaction blocks  ?column?  ----------  2 (1 row) 

The two issues are due to SendQuery() being reentered
for the gexec'd queries when it hasn't finished yet with the
main query.
I believe that just collecting all results of \gexec before
executing any of them would solve both errors.

Also doing a bit more testing I've seen these other issues:

* combining multiple result sets and FETCH_COUNT doesn't work:
 postgres=# \set FETCH_COUNT 1 postgres=# select 1 \; select 2; postgres=#


* last error is not recorded for \errverbose :
 postgres=# select foo; ERROR:  column "foo" does not exist LINE 1: select foo;     ^ postgres=# \errverbose There is
noprevious error. 

* memory leaks on PGResults.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite