Re: [HACKERS] Dynamic result sets from procedures

On Thu, Nov 16, 2017 at 8:27 PM, Daniel Verite <daniel(at)manitou-mail(dot)org> wrote:
> 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.

Peter, are you planning to answer to those complains? For now I am
switching the patch as returned with feedback as this thread has no
activity for two weeks.
--
Michael