I found this email from Peter Eisentraut:
I have developed a patch that allows declaring cursors over prepared statements... This is an SQL standard feature. ECPG already supports it (with different internals). Internally, this just connects existing functionality in different ways, so it doesn't really introduce anything new.
I tried this in Version 15.2 (avoiding the question of how to bind actual arguments to placeholders):
prepare series as select s.v from generate_series(1, 10) as s(v);
start transaction;
declare cur scroll cursor for execute series;
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?
p.s. The section doc "PL/pgSQL under the Hood” explains that expressions and static SQL statements in a PL/pgSQL source text are effectively prepared when first encountered at run time in a session. But simple tests show that there’s no evidence if this in the “pg_prepared_statements” catalog view. Is it possible that the subquery that is used to declare a cursor (using ordinary top level SQL or the PL/pgSQL API) is also invisibly prepared under the hood?