Search Postgresql Archives

Re: Problem with temporary tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@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
>

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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux