how to check if a temp table exists?

[Conxita Marín]

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 (***@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.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Christoph Dalitz]

Date: Tue, 14 Jan 2003 11:37:26 +0100
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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly