how to check if a temp table exists?
От | Conxita Marín |
---|---|
Тема | how to check if a temp table exists? |
Дата | |
Msg-id | 000001c2bbb8$eebaeec0$0cd8a8c0@dims обсуждение исходный текст |
Список | 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 по дате отправления: