From: | Andrea Lombardoni <andrea(at)lombardoni(dot)ch> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Andrea Terribilini <andrea(dot)terribilini(at)oneoverzero(dot)net> |
Subject: | Problem with temporary tables |
Date: | 2010-06-30 12:51:37 |
Message-ID: | AANLkTik4N7ZVfP_uD3LIMTPodjQzWCwftIG9hdXzbZmX@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | A. Kretschmer | 2010-06-30 13:14:55 | Re: Problem with temporary tables |
Previous Message | Vibhor Kumar | 2010-06-30 12:12:24 | Re: Scheduling backup |