Re: Problem with temporary tables

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(at)lombardoni(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>