Prepared statements and cursors

pgpool-II may do what you want.

On Thu, Jan 23, 2014 at 6:31 AM, Andreas Lubensky <lubensky@cognitec.com>wrote:

Sorry, answered wrong posting.

On Thu, Jan 23, 2014 at 6:31 AM, Andreas Lubensky <lubensky@cognitec.com>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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general