Thread: Scrolling cursors in PL/PgSQL
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
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 ---------------------------------------------------------------
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
"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