Re: Simplifying identification of temporary tables

Tom Lane wrote:
> Currently, the recommended way to ask "have I already created a temp
> table named foo" is something like
>
>     select * from pg_class
>     where relname = 'foo' and pg_table_is_visible(oid);
>
> If there's a possibility that a regular table named 'foo' exists,
> then this isn't good enough and you have to resort to
>
>     select *
>     from pg_class c join pg_namespace n on n.oid = c.relnamespace
>     where relname = 'foo' and nspname like 'pg_temp_%' and
>     pg_table_is_visible(c.oid)

Well now that we have savepoints you have another approach.  In
non-dynamic pl/pgsql functions my preferred method is to probe the table
via normal sql and recreate it on the appropriate exception.

That said, I think what you are proposing is good since it causes less
log pollution.  Although I would prefer to return the name of the
namespace, not the oid, or just go right to the point and create
function accepting temp table name and returning bool.  That way the oid
is abstracted into the function.

Merlin