Hello 2008/9/26 Reg Me Please <[EMAIL PROTECTED]>: > Nice suggestion. > > In the meanwhile I've found a "workaround" that works for me (unless there's a > hidden pitfall): > > CREATE OR REPLACE FUNCTION f_cursor2( query text, out curs refcursor ) > LANGUAGE PLPGSQL STRICT > AS $BODY$ > DECLARE > c refcursor; > BEGIN > c := 'cursor_'||nextval( 's_cursors' ); > EXECUTE 'DECLARE '||curs||' SCROLL CURSOR WITH HOLD FOR '||query; > curs := c; > END; > $BODY$; > > SELECT f_cursor( 'SELECT * FROM pg_tables' ); > > curs > ----------- > cursor_2 > (1 row) > > FETCH 10 FROM cursor_2; > > schemaname | tablename | tableowner | tablespace | > hasindexes | hasrules | hastriggers > --------------------+-------------------------+------------+------------+------------+----------+------------- > information_schema | sql_features | postgres | [NULL] | > f | f | f > information_schema | sql_implementation_info | postgres | [NULL] | > f | f | f > pg_catalog | pg_statistic | postgres | [NULL] | > t | f | f > information_schema | sql_languages | postgres | [NULL] | > f | f | f > information_schema | sql_packages | postgres | [NULL] | > f | f | f > information_schema | sql_parts | postgres | [NULL] | > f | f | f > information_schema | sql_sizing | postgres | [NULL] | > f | f | f > pg_catalog | pg_authid | postgres | pg_global | > t | f | t > information_schema | sql_sizing_profiles | postgres | [NULL] | > f | f | f > pg_catalog | pg_database | postgres | pg_global | > t | f | t > (10 rows) > > SELECT * from pg_cursors ; > name | > statement | is_holdable | is_binary | > is_scrollable | creation_time > -----------+-----------------------------------------------------------------------+-------------+-----------+---------------+------------------------------- > cursor_2 | DECLARE cursor_2 SCROLL CURSOR WITH HOLD FOR SELECT * FROM > pg_tables | t | f | t | 2008-09-26 > 10:05:38.963548+02 > (1 row) > > I would then say the PLPgSQL should also have the "WITH / WITHOUT HOLD" > feature, otherwise a function that creates a cursor needs a transaction > despite the cursor is read-only. > > In my very humble opinion. >
ofcourse it's better, than some our hacks regards Pavel Stehule > On Thursday 25 September 2008 19:58:07 Pavel Stehule wrote: >> Hello >> >> try to look at >> http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html >> >> regards >> Pavel Stehule >> >> p.s. you should to use transaction >> >> 2008/9/25 Reg Me Please <[EMAIL PROTECTED]>: >> > Hi all. >> > >> > I'm running PGSQL v.8.3.3 >> > >> > I tried to adapt the examples from the friendly manual (38.7.3.5) in >> > order to to have a function to create cursors based on a parametric query >> > string: >> > >> > CREATE SEQUENCE s_cursors; >> > >> > CREATE OR REPLACE FUNCTION f_cursor( query text, out curs refcursor ) >> > LANGUAGE PLPGSQL STRICT >> > AS $BODY$ >> > DECLARE >> > c refcursor; >> > BEGIN >> > c := 'cursor_'||nextval( 's_cursors' ); >> > OPEN c SCROLL FOR EXECUTE query; >> > curs := c; >> > END; >> > $BODY$; >> > >> > SELECT f_cursor( 'SELECT * FROM pg_tables' ); >> > >> > curs >> > ----------- >> > cursor_1 >> > (1 row) >> > >> > FETCH 10 FROM cursor_1; >> > >> > ERROR: cursor "cursor_1" does not exist >> > >> > SELECT * from pg_cursors ; >> > name | statement | is_holdable | is_binary | is_scrollable | >> > creation_time >> > ------+-----------+-------------+-----------+---------------+------------ >> >--- (0 rows) >> > >> > The cursor is (should have been) created as there's no error but it seems >> > it vanishes as soon as the creating function returns. >> > As if it was created "WITHOUT HOLD", which doesn't make much of sense in >> > a function returning a refcursor, this is why there is (seems to be) no >> > "HOLD" part in the cursor creation in PLPgSQL. >> > >> > I think more likely I am making some mistake. But have n ìo idea where. >> > >> > Any hint? >> > >> > Thanks in advance >> > >> > RMP. >> > >> > >> > -- >> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> > To make changes to your subscription: >> > http://www.postgresql.org/mailpref/pgsql-general > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
