how to check if a temp table exists? - Mailing list pgsql-general

From Conxita Marín
Subject how to check if a temp table exists?
Date
Msg-id 000001c2bbb8$eebaeec0$0cd8a8c0@dims
Whole thread Raw
List pgsql-general
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.


pgsql-general by date:

Previous
From: Jochem van Dieten
Date:
Subject: Re: PostgreSQL on Windows
Next
From: pascal bérest
Date:
Subject: time to restore a database