Scrolling cursors in PL/PgSQL

From: "Dawid Kuroczko" <qnex42(at)gmail(dot)com>
To: "Postgres General" <pgsql-general(at)postgresql(dot)org>
Cc: pgsql-sql <pgsql-sql(at)postgresql(dot)org>
Subject: Scrolling cursors in PL/PgSQL
Date: 2007-11-01 20:19:44
Message-ID: 758d5e7f0711011319t5e752dsad6481d1427a46ff@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2007-11-01 20:35:35 Re: select random order by random
Previous Message hubert depesz lubaczewski 2007-11-01 19:25:22 Re: getting list of tables from command line

Browse pgsql-sql by date

  From Date Subject
Next Message Raymond O'Donnell 2007-11-01 21:02:42 Re: Scrolling cursors in PL/PgSQL
Previous Message Andrew Sullivan 2007-10-31 19:04:58 Re: Backup Database