> adrian.klaver@aklaver.com wrote:
>
>> bryn@yugabyte.com wrote:
>>
>> It seems that there must be different underlying mechanisms at work and that this explains why creating a cursor
usingSQL to execute a prepared statement fails but doing this using PL/pgSQL succeeds. What's going on under the
covers?
>
> Pretty sure:
>
> www.postgresql.org/docs/current/spi.html
I think that I see what you mean, Adrian. I had read the "PL/pgSQL Under the Hood" section to mean that, at run time,
ordinarySQL calls were invariably made whenever the point of execution reached anything that implied SQL functionality
(including,famously, expression evaluation). I'd assumed, therefore, that when the PL/pgSQL has an "open" statement,
andwhen this is encountered at run time, the ordinary SQL "declare" statement was invoked.
But it seems, rather, that the SQL "declare" and the PL/pgSQL "open" each as its own implementation by lower-level
prinitives—andthat these differ in their details and in their power of expression. That would explain why the
"pg_cursors.statement"text differs for cursors with identical properties (like scrollability), and the identically
spelledsubquery, like I showed in my earlier email.
It seems odd that the two approaches each has its own limitation(s).
— You can't create a cursor to execute a prepared statement using the SQL API; and you can't create a holdable cursor
usingthe (static) PL/pgSQL API but can work around this with dynamic SQL.
—You have to dive down to a lower-level API like "Embedded SQL in C" (a.k.a. ECPG) to create a holdable cursor to
executea prepared statement.
But I appreciate that this comes with the territory and that anyone who feels strongly about this, and who knows how to
doit, can develop their own patch and submit it for consideration.