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 all
select 'drop table gexec_test', 'select ''2000-01-01''::date as party_over'
\gexec
select 1 as ones
- 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 in transaction 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 no previous error.

* memory leaks on PGResults.

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