PL/pgSQL's "open cur for execute" provides workarouned... Re: cursors with prepared statements

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

bryn@xxxxxxxxxxxx wrote:

david.g.johnston@xxxxxxxxx wrote:

bryn@xxxxxxxxxxxx wrote:

I found this email from Peter Eisentraut:
https://www.postgresql.org/message-id/762cc764-74f0-13fb-77ed-16f91c90f40d%402ndquadrant.com

It caused the 42601 error, « syntax error at or near “execute” ». So it looks like Peter’s patch hasn’t yet been adopted. What is the likelihood that it will be adopted in a future version?

Closer to zero than one I'd say, given how that thread ended and not subsequent activity on the feature in the five years since.

Thanks. Shall I assume, too, that there’s no under-the-hood functionality for cursors, analogous to what happens with a PL/pgSQL program at run-time, that does the moral equivalent of on-demand prepare for a cursor’s defining subquery?

I tried this test.

create procedure s.p(n in int)
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  c0      constant refcursor not null := 'c0';
  c1      constant refcursor not null := 'c1';
  c2      constant refcursor not null := 'c2';

  r       int;
  r0      int[];
  r1      int[];
  r2      int[];
begin
  execute 'declare c0 scroll cursor for select s.v from generate_series(1, 10) as s(v)';
  open c1 for execute 'select s.v from generate_series(1, $1) as s(v)' using n;
  open c2 for execute format('execute series(%s)', n);

  for j in 1.. 100 loop
    fetch c0 into r; r0[j] := r;
    exit when not found;
  end loop;

  for j in 1.. 100 loop
    fetch c1 into r; r1[j] := r;
    exit when not found;
  end loop;

  for j in 1.. 100 loop
    fetch c2 into r; r2[j] := r;
    exit when not found;
  end loop;

  assert (r1 = r0) and (r2 = r1);
end;
$body$;

prepare series(int) as select s.v from generate_series(1, $1) as s(v);
start transaction;
  call s.p(10);
  select name, statement from pg_cursors order by name;
rollback;

 I expected to get an error from the PL/pgSQL statement that, after "format()" has been consumed, boils down to this:

open c2 for execute 'execute series(10)';

And I wondered how this would be reported. But it ran without error. The loop over each cursor showed that the result sets from the three alternatives are identical. And this is what the "pg_cursors" query showed:

 name |                                  statement                                  
------+-----------------------------------------------------------------------------
 c0   | declare c0 scroll cursor for select s.v from generate_series(1, 10) as s(v)
 c1   | select s.v from generate_series(1, $1) as s(v)
 c2   | execute series(10)
 c9   | execute series(10)

The difference in the value of "pg_cursors.statement" for two cursors based on the identical subquery where one is created with the SQL "declare" and  the other is created with the PL/pgSQL "open" has puzzled me from the first time that I noticed it.

It seems that there must be different underlying mechanisms at work and that this explains why creating a cursor using SQL to execute a prepared statement fails but doing this using PL/pgSQL succeeds.

What's going on under the covers?