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)
which is truly ugly, and pretty inefficient as well. And both of
these cases have a race condition if multiple sessions might be
creating and dropping temp tables named 'foo': pg_table_is_visible()
might fail because the table is one that someone else dropped just
before control got to the function.
It occurs to me that a much better solution is possible if we create
a function defined along the following lines:
pg_my_temp_namespace() returns oid
If a temporary table namespace has been established
for the current session, return its OID;
else return NULL.
The probe to see if 'foo' exists then becomes
select * from pg_class
where relname = 'foo' and relnamespace = pg_my_temp_namespace();
No join, no race condition, and a fully indexable WHERE clause.
You can sort of do this now at the SQL level by inspecting the result of
current_schemas(true), but it's fairly tedious to write such a function.
As a C function it'd be a one-liner.
Seems worthwhile to me --- any objections? Any better ideas about a
name?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
