On Wednesday 30 June 2010 6:21:44 am Andrea Lombardoni wrote: > >> Am I doing something wrong or is this a bug? > > > > The plan is cached, to avoid this problem, use dynamic SQL. In your > > case: > > > > EXECUTE 'CREATE TEMPORARY TABLE idmap ...' > > Nice idea, but the problem persists, see log below. > > I am beginning to mentally place this into the 'bug' area :) > > > CREATE OR REPLACE FUNCTION test() RETURNS bigint AS $$ > DECLARE > > > v_oid bigint; > BEGIN > > -- create tmp-table used to map old-id to new-id > -- type: 1=skill 3=function > EXECUTE 'CREATE TEMPORARY TABLE idmap (oldid bigint PRIMARY KEY, > type bigint, newid bigint) ON COMMIT DROP'; > > SELECT INTO v_oid oid FROM pg_class WHERE relname = 'idmap'; > RAISE NOTICE 'OOID of idmap %', v_oid; > > > -- add id mapping (type=1) > INSERT INTO idmap (oldid, type, newid) VALUES(1, 1, 1); > > > RETURN 1; > END; > $$ LANGUAGE plpgsql; > > > # begin;select test();commit; > BEGIN > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "idmap_pkey" for table "idmap" > CONTEXT: SQL statement "CREATE TEMPORARY TABLE idmap (oldid bigint > PRIMARY KEY, type bigint, newid bigint) ON COMMIT DROP" > PL/pgSQL function "test" line 9 at execute statement > NOTICE: OOID of idmap 475391188 > test > ------ > 1 > (1 row) > > COMMIT > # begin;select test();commit; > BEGIN > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "idmap_pkey" for table "idmap" > CONTEXT: SQL statement "CREATE TEMPORARY TABLE idmap (oldid bigint > PRIMARY KEY, type bigint, newid bigint) ON COMMIT DROP" > PL/pgSQL function "test" line 9 at execute statement > NOTICE: OOID of idmap 475391192 > ERROR: relation with OID 475391188 does not exist > CONTEXT: SQL statement "INSERT INTO idmap (oldid, type, newid) VALUES(1, > 1, 1)" PL/pgSQL function "test" line 16 at SQL statement > ROLLBACK You need to use EXECUTE for the INSERT statement as well per error: CONTEXT: SQL statement "INSERT INTO idmap (oldid, type, newid) VALUES(1, 1, 1)" PL/pgSQL function "test" line 16 at SQL statement -- Adrian Klaver adrian.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
