how to check if a temp table exists?

From: Conxita Marín <comarin(at)telefonica(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Subject: how to check if a temp table exists?
Date: 2003-01-14 10:37:26
Message-ID: 000001c2bbb8$eebaeec0$0cd8a8c0@dims
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)fslc(dot)usu(dot)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.

Browse pgsql-general by date

  From Date Subject
Next Message João Alfredo 2003-01-14 11:10:41 Alternative to nested transactions
Previous Message Jochem van Dieten 2003-01-14 09:58:21 Re: PostgreSQL on Windows