[SQL] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] Scrolling cursors in PL/PgSQL
On 01/11/2007, Dawid Kuroczko <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] Scrolling cursors in PL/PgSQL
"Dawid Kuroczko" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
