Problem with temporary tables

[Andrea Lombardoni <andrea@xxxxxxxxxxxxx>]

Hello.

I am trying to use temporary tables inside a stored procedure, but I
get a rather puzzling error.

I am currently using PostgreSQL 8.2.7 and this is my stored procedure:

CREATE OR REPLACE FUNCTION test() RETURNS bigint AS $$
DECLARE
  v_oid bigint;
BEGIN

    -- create tmp-table used to map old-id to new-id
    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;

    INSERT INTO idmap (oldid, type, newid) VALUES(1, 1, 1);

    RETURN 1;
END;
$$ LANGUAGE plpgsql;


The first time I invoke the stored procedure, everything goes fine:

# 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 SQL statement
NOTICE:  OOID of idmap 475391180
 test
------
    1
(1 row)

COMMIT

The second time I invoke the stored procedure, I get an error:

# 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 SQL statement
NOTICE:  OOID of idmap 475391184
ERROR:  relation with OID 475391180 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

The strange part is that the second time, the OID of the idmap is the
same as the one in the first invocation!

Am I doing something wrong or is this a bug?

Thanks!

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general