pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedScrolling cursors in PL/PgSQL
4+ messages / 4 participants
[nested] [flat]
* Scrolling cursors in PL/PgSQL
@ 2007-11-01 20:19 Dawid Kuroczko <qnex42@gmail.com>
0 siblings, 3 replies; 4+ messages in thread
From: Dawid Kuroczko @ 2007-11-01 20:19 UTC (permalink / raw)
To: Postgres General <pgsql-general@postgresql.org>; +Cc: pgsql-sql
Hello.
Is there a way to scroll a cursor from within PL/PgSQL?
I tried EXECUTE, but:
ERROR: cannot manipulate cursors directly in PL/pgSQL
HINT: Use PL/pgSQL's cursor features instead.
The idea would be that PL/pgsql function would look
through (all) query results, then rewind the cursor and
finally return it.
My fellow developers have created a function which run
the (complex) query twice (first a count(*) on the result
set, then return the row count and cursor to the results.
Then they created a version which uses temporary
table as a placeholder for the query results, and results
count and a pointer to select * from tmp_table.
Now I imagined a function which would open the cursor,
MOVE FORWARD ALL, then MOVE ABSOLUTE 0;
and return count and a cursor, yet it seems it won't work.
Any other idea how to efficiently solve such a problem?
Regards,
Dawid
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Scrolling cursors in PL/PgSQL
@ 2007-11-01 21:02 Raymond O'Donnell <rod@iol.ie>
parent: Dawid Kuroczko <qnex42@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Raymond O'Donnell @ 2007-11-01 21:02 UTC (permalink / raw)
To: ; +Cc: Postgres General <pgsql-general@postgresql.org>; pgsql-sql
On 01/11/2007 20:19, Dawid Kuroczko wrote:
> Any other idea how to efficiently solve such a problem?
How about creating a temporary table within your function and using that
instead?
Ray.
---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Scrolling cursors in PL/PgSQL
@ 2007-11-01 21:05 Pavel Stehule <pavel.stehule@gmail.com>
parent: Dawid Kuroczko <qnex42@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2007-11-01 21:05 UTC (permalink / raw)
To: Dawid Kuroczko <qnex42@gmail.com>; +Cc: Postgres General <pgsql-general@postgresql.org>; pgsql-sql
On 01/11/2007, Dawid Kuroczko <qnex42@gmail.com> wrote:
> Hello.
>
> Is there a way to scroll a cursor from within PL/PgSQL?
>
> I tried EXECUTE, but:
>
> ERROR: cannot manipulate cursors directly in PL/pgSQL
> HINT: Use PL/pgSQL's cursor features instead.
>
> The idea would be that PL/pgsql function would look
> through (all) query results, then rewind the cursor and
> finally return it.
>
> My fellow developers have created a function which run
> the (complex) query twice (first a count(*) on the result
> set, then return the row count and cursor to the results.
> Then they created a version which uses temporary
> table as a placeholder for the query results, and results
> count and a pointer to select * from tmp_table.
>
> Now I imagined a function which would open the cursor,
> MOVE FORWARD ALL, then MOVE ABSOLUTE 0;
> and return count and a cursor, yet it seems it won't work.
>
> Any other idea how to efficiently solve such a problem?
>
Hello
PostgreSQL 8.3 allows scrollable cursors in plpgsql
Regards
Pavel Stehule
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Scrolling cursors in PL/PgSQL
@ 2007-11-01 21:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dawid Kuroczko <qnex42@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2007-11-01 21:22 UTC (permalink / raw)
To: Dawid Kuroczko <qnex42@gmail.com>; +Cc: Postgres General <pgsql-general@postgresql.org>; pgsql-sql
"Dawid Kuroczko" <qnex42@gmail.com> writes:
> Is there a way to scroll a cursor from within PL/PgSQL?
FWIW, in 8.3 plpgsql has full FETCH/MOVE support --- that might or might
not help you, depending on what your timescale for deploying this
application is ...
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-11-01 21:22 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-01 20:19 Scrolling cursors in PL/PgSQL Dawid Kuroczko <qnex42@gmail.com>
2007-11-01 21:02 ` Raymond O'Donnell <rod@iol.ie>
2007-11-01 21:05 ` Pavel Stehule <pavel.stehule@gmail.com>
2007-11-01 21:22 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox