Re: temp table existence

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(dot)mk(at)gmail(dot)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?
>