Обсуждение: 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, 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