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.
