Thread: BUG #2494: SCROLL CURSOR does not worl
The following bug has been logged online: Bug reference: 2494 Logged by: Michal Szymanski Email address: biuro@globeinphotos.com PostgreSQL version: 8.1 Operating system: Windows Description: SCROLL CURSOR does not worl Details: Function with scroll cursor cannot be compiled. ERROR: syntax error at or near "CURSOR" CONTEXT: invalid type name "SCROLL CURSOR FOR SELECT * FROM freeconet.alias WHERE mask >" compile of PL/pgSQL function "dummy" near line 3 Function that I want compile. CREATE OR REPLACE FUNCTION freeconet.dummy ( ) RETURNS INT8 AS $$ DECLARE rowvar freeconet.ALIAS%ROWTYPE; DECLARE curs1 SCROLL CURSOR FOR SELECT * FROM freeconet.alias WHERE mask>='+4850X' LIMIT 100 ; BEGIN BEGIN OPEN curs1; FETCH curs1 INTO rowvar; EXCEPTION WHEN OTHERS THEN RAISE WARNING '.... Kod bledu = %', SQLSTATE; RETURN -1; /* blad danych */ END; RAISE WARNING ' mask=%',rowvar.mask; RETURN 1; END; $$ LANGUAGE 'plpgsql' VOLATILE;
There's no such thing as a 'SCROLL CURSOR' in plpgsql: http://www.postgresql.org/docs/8.1/interactive/plpgsql-cursors.html I'm pretty sure that the default behavior would be scrollable. We should probably add a TODO to bring the plpgsql cursor syntax inline with what the backend supports. On Fri, Jun 23, 2006 at 01:44:24PM +0000, Michal Szymanski wrote: > > The following bug has been logged online: > > Bug reference: 2494 > Logged by: Michal Szymanski > Email address: biuro@globeinphotos.com > PostgreSQL version: 8.1 > Operating system: Windows > Description: SCROLL CURSOR does not worl > Details: > > Function with scroll cursor cannot be compiled. > ERROR: syntax error at or near "CURSOR" > CONTEXT: invalid type name "SCROLL CURSOR FOR SELECT * FROM freeconet.alias > WHERE mask >" > compile of PL/pgSQL function "dummy" near line 3 > > Function that I want compile. > CREATE OR REPLACE FUNCTION freeconet.dummy ( > ) RETURNS INT8 AS $$ > DECLARE > rowvar freeconet.ALIAS%ROWTYPE; > DECLARE curs1 SCROLL CURSOR FOR SELECT * FROM freeconet.alias WHERE > mask>='+4850X' LIMIT 100 ; > BEGIN > BEGIN > > OPEN curs1; > FETCH curs1 INTO rowvar; > > EXCEPTION > WHEN OTHERS THEN > RAISE WARNING '.... Kod bledu = %', SQLSTATE; > RETURN -1; /* blad danych */ > END; > RAISE WARNING ' mask=%',rowvar.mask; > RETURN 1; > END; > $$ LANGUAGE 'plpgsql' VOLATILE; > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Added to TODO: > o Add support for WITH HOLD and SCROLL cursors > > PL/pgSQL cursors should support the same syntax as > backend cursors. --------------------------------------------------------------------------- Jim C. Nasby wrote: > There's no such thing as a 'SCROLL CURSOR' in plpgsql: > http://www.postgresql.org/docs/8.1/interactive/plpgsql-cursors.html > I'm pretty sure that the default behavior would be scrollable. > > We should probably add a TODO to bring the plpgsql cursor syntax inline > with what the backend supports. > > On Fri, Jun 23, 2006 at 01:44:24PM +0000, Michal Szymanski wrote: > > > > The following bug has been logged online: > > > > Bug reference: 2494 > > Logged by: Michal Szymanski > > Email address: biuro@globeinphotos.com > > PostgreSQL version: 8.1 > > Operating system: Windows > > Description: SCROLL CURSOR does not worl > > Details: > > > > Function with scroll cursor cannot be compiled. > > ERROR: syntax error at or near "CURSOR" > > CONTEXT: invalid type name "SCROLL CURSOR FOR SELECT * FROM freeconet.alias > > WHERE mask >" > > compile of PL/pgSQL function "dummy" near line 3 > > > > Function that I want compile. > > CREATE OR REPLACE FUNCTION freeconet.dummy ( > > ) RETURNS INT8 AS $$ > > DECLARE > > rowvar freeconet.ALIAS%ROWTYPE; > > DECLARE curs1 SCROLL CURSOR FOR SELECT * FROM freeconet.alias WHERE > > mask>='+4850X' LIMIT 100 ; > > BEGIN > > BEGIN > > > > OPEN curs1; > > FETCH curs1 INTO rowvar; > > > > EXCEPTION > > WHEN OTHERS THEN > > RAISE WARNING '.... Kod bledu = %', SQLSTATE; > > RETURN -1; /* blad danych */ > > END; > > RAISE WARNING ' mask=%',rowvar.mask; > > RETURN 1; > > END; > > $$ LANGUAGE 'plpgsql' VOLATILE; > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +