Thread: how to check if a temp table exists?

I need to write functions that uses temporary tables. The function has to
create te temp table if it not exists,  or delete the contents if it exists.
Another user (rmello@fslc.usu.edu) in a recent post give me some idea how to
do this. There is the code:

DROP FUNCTION prova();
CREATE FUNCTION prova() returns varchar as
'
DECLARE
    Cnt int4;

BEGIN
    SELECT COUNT(*) FROM pg_tables WHERE tablename=''some_temp_table'' INTO
Cnt;
    IF Cnt > 0 THEN
        RAISE NOTICE '' DELETE'';
        DELETE FROM some_temp_table;
    ELSE
        RAISE NOTICE '' CREATE'';
        CREATE TEMP TABLE some_temp_table(
        t0_nom        varchar(15) NOT NULL
        ) WITH OIDS;
    END IF;
    return ''ok'';
END
'
LANGUAGE 'plpgsql';

But when I start a session and i lunch the function , the first time works,
after it fails.

s001=>  select prova();
NOTICE:   CREATE
 prova
-------
 ok
(1 row)

s001=>  select prova();
NOTICE:   CREATE
NOTICE:  Error occurred while executing PL/pgSQL function prova
NOTICE:  line 11 at SQL statement
ERROR:  Relation 'some_temp_table' already exists
s001=>

In wich table Postgres stores the name of the temporary tables?

Any help will be greatly appreciated.

Conxita.

> Date: Tue, 14 Jan 2003 11:37:26 +0100
> From: <comarin@telefonica.net>
>
> I need to write functions that uses temporary tables. The function has to
> create te temp table if it not exists,  or delete the contents if it exists.
>
> But when I start a session and i lunch the function , the first time works,
> after it fails.
>
Using temporary tables in stored procedures is somewhat tricky.
Here are some tips:

 - Use "create local temporary table" to make the table local to
   the current transaction. Then other transactions may happily
   use the same temporary table name at teh same time.

 - All statements that refer to the temp table in your function
   must be called with EXECUTE. This is because after the function
   is compiled into the DB server, the tables are referenced via
   OID rather than name. That's why your function works the first time,
   but fails the second time when the original OID is no longer valid.

 - Don't forget to drop your temp table somewhere in your function
   (it will be dropped implicitly at the end of the transaction, but you
   cannot be sure that the same function is not called more than once
   in a single transaction).

Hope this helps,

Christoph Dalitz