Re: [HACKERS] Error working with Temporary Sequences in plpgsql in 8.1 (8.0
A nice workaraound because EXECUTE 'select nextval(''test'')' INTO I; doesnt work in 8.0 seems to be: myid:=nextval('stvtrsid_seq'::TEXT); This seems to work in every case. Daniel Jaime Casanova schrieb: try this way: CREATE OR REPLACE FUNCTION testseq() RETURNS void AS $BODY$ BEGIN EXECUTE 'CREATE TEMP SEQUENCE test'; PERFORM testseq1(); DROP SEQUENCE test; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION testseq() OWNER TO postgres; CREATE OR REPLACE FUNCTION testseq1() RETURNS void AS $BODY$ DECLARE I INTEGER; BEGIN EXECUTE 'select nextval(''test'')' INTO I; raise notice '%', I; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION testseq1() OWNER TO postgres; SELECT testseq(); SELECT testseq(); is the same problem as with temp tables, you must put their creation, and in this case even the nextval in an execute... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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
[HACKERS] Error working with Temporary Sequences in plpgsql in 8.1 (8.0 works fine)
Hi, here is a testcase: CREATE OR REPLACE FUNCTION testseq() RETURNS void AS $BODY$ BEGIN CREATE TEMP SEQUENCE test; PERFORM testseq1(); DROP SEQUENCE test; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION testseq() OWNER TO postgres; CREATE OR REPLACE FUNCTION testseq1() RETURNS void AS $BODY$ DECLARE I INTEGER; BEGIN I:= nextval('test'); RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION testseq1() OWNER TO postgres; SELECT testseq(); -- this works fine. SELECT testseq(); ERROR: could not open relation with OID 21152 CONTEXT: PL/pgSQL function testseq1 line 3 at assignment SQL statement SELECT testseq1() PL/pgSQL function testseq line 3 at perform Greetings, Daniel. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Error working with Temporary Sequences in plpgsql in 8.1 (8.0 works fine)
On 1/17/06, Daniel Schuchardt [EMAIL PROTECTED] wrote: Hi, here is a testcase: CREATE OR REPLACE FUNCTION testseq() RETURNS void AS $BODY$ BEGIN CREATE TEMP SEQUENCE test; PERFORM testseq1(); DROP SEQUENCE test; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION testseq() OWNER TO postgres; CREATE OR REPLACE FUNCTION testseq1() RETURNS void AS $BODY$ DECLARE I INTEGER; BEGIN I:= nextval('test'); RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION testseq1() OWNER TO postgres; SELECT testseq(); -- this works fine. SELECT testseq(); ERROR: could not open relation with OID 21152 CONTEXT: PL/pgSQL function testseq1 line 3 at assignment SQL statement SELECT testseq1() PL/pgSQL function testseq line 3 at perform Greetings, Daniel. try this way: CREATE OR REPLACE FUNCTION testseq() RETURNS void AS $BODY$ BEGIN EXECUTE 'CREATE TEMP SEQUENCE test'; PERFORM testseq1(); DROP SEQUENCE test; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION testseq() OWNER TO postgres; CREATE OR REPLACE FUNCTION testseq1() RETURNS void AS $BODY$ DECLARE I INTEGER; BEGIN EXECUTE 'select nextval(''test'')' INTO I; raise notice '%', I; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION testseq1() OWNER TO postgres; SELECT testseq(); SELECT testseq(); is the same problem as with temp tables, you must put their creation, and in this case even the nextval in an execute... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Error working with Temporary Sequences in plpgsql in 8.1 (8.0 works fine)
On Tue, Jan 17, 2006 at 01:28:03PM -0500, Jaime Casanova wrote: is the same problem as with temp tables, you must put their creation, and in this case even the nextval in an execute... Curious that it works in 8.0, though. I wonder if the failure in 8.1 is an artifact of changing sequence functions like nextval() to take a regclass argument (the sequence OID) instead of a text argument (the sequence name); that would affect what gets put in the function's cached plan. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org