Hello
in PostgreSQL 8.2 and older you have to respect one rule - newer to
drop temp table. You don't must do it. After session end, all temp
tables are removed.
you can execute some initialisation part like
CREATE OR REPLACE FUNCTION check_tab()
RETURNS void AS $$
BEGIN
BEGIN
TRUNCATE TABLE foo;
EXCEPTION
WHEN others THEN
CREATE TABLE foo(a int);
END;
RETURN;
END;
$$ LANGUAGE plpgsql;
This problem is solved from 8.3
2010/6/30 Andrea Lombardoni <andrea@lombardoni.ch>:
> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>