Обсуждение: Prepared statements and cursors

Hello,
When implementing a database backend with libpq I realized that it seems
to be impossible to declare a cursor on a prepared statement. Is this
correct? What is the reason for this limitation?

--
with best regards,

Andreas Lubensky

On Thu, Jan 23, 2014 at 8:31 AM, Andreas Lubensky <lubensky@cognitec.com> wrote:
> Hello,
> When implementing a database backend with libpq I realized that it seems
> to be impossible to declare a cursor on a prepared statement. Is this
> correct? What is the reason for this limitation?

I can't think of any but it can be trivially worked around:
create or replace function f() returns void as $$ declare v cursor for
select 0; $$ language sql;
prepare p as select f();
postgres=# begin;
BEGIN
postgres=# execute p;
 f
---

(1 row)

postgres=# fetch all from v;
 ?column?
----------
        0

merlin

That is an interesting approach. However, I see the problem that the
functions would have to be removed when no longer needed. If that fails
(broken connection etc.), they would be orphaned.
Prepared statements are bound to the connection, so when the connection
is closed they are gone.

On Thu, 2014-01-23 at 15:07 -0600, Merlin Moncure wrote:
> On Thu, Jan 23, 2014 at 8:31 AM, Andreas Lubensky <lubensky@cognitec.com> wrote:
> > Hello,
> > When implementing a database backend with libpq I realized that it seems
> > to be impossible to declare a cursor on a prepared statement. Is this
> > correct? What is the reason for this limitation?
>
> I can't think of any but it can be trivially worked around:
> create or replace function f() returns void as $$ declare v cursor for
> select 0; $$ language sql;
> prepare p as select f();
> postgres=# begin;
> BEGIN
> postgres=# execute p;
>  f
> ---
>
> (1 row)
>
> postgres=# fetch all from v;
>  ?column?
> ----------
>         0
>
> merlin
>

--
with best regards,

Andreas Lubensky

Software Engineer

On Tue, Jan 28, 2014 at 7:53 AM, Andreas Lubensky <lubensky@cognitec.com> wrote:
> That is an interesting approach. However, I see the problem that the
> functions would have to be removed when no longer needed. If that fails
> (broken connection etc.), they would be orphaned.
> Prepared statements are bound to the connection, so when the connection
> is closed they are gone.

well, you could abstract the function:
create or replace function eval(text) returns void as $$ begin execute
$1; end; $$ language plpgsql;
select eval('declare v cursor for select 0');

note, here eval() is a gaping security hole, so be advised.

merlin