Thanks for the answer but it's not quite sufficient. The code supplied on his page:
CREATE OR REPLACE FUNCTION ...
BEGIN
PERFORM 1 FROM pg_catalog.pg_tables
WHERE tablename = 'xx' AND schemaname LIKE 'pg_temp%';
IF FOUND THEN
TRUNCATE xx;
ELSE
CREATE TEMP TABLE xx(...
END IF;
The function does exactly what I was trying to avoid - simple check the existence of xx table in pg_tables virtualy only by it's name, it's not enough since there may be other temp tables created in seprate sessions. The only thing those temp table differ in pg_tables i schemaname, they have
that suffix number and in the above mentioned function I would have to be able to retrieve this number somehow.
...
WHERE tablename = 'xx' AND schemaname LIKE 'pg_temp_' || function_to_retieve_suffix() ???
...
That would work. Otherwise all temp tables by the name of xx will be truncated, which I would not like to happen since since they may still be in use.
2007/12/29, Marcin Krawczyk < jankes.mk@gmail.com>: Hi all. Is there a way to determine the existence of a TEMP TABLE? I need to check i it exists before I create it. Doing simple check on pg_class or pg_tables is
not enough because there may be other such tables created in other sessions. Or maybe anyone knows the identification (apart from 'others') of error to trap it with EXCEPTION clause?