Re: [9.0] On temporary tables

Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it> writes:
> create or replace function session_init()
> returns void
> language plpgsql
> as $body$
> declare
> t text;
> begin
> select valu into t from session where name='SESSION_ID';
> if not found then
> create temporary table session ( like public.session including all );
> insert into session values ( 'SESSION_ID',current_user );
> end if;
> end;
> $body$;

> The idea is to create a temporary table to store session variables
> only of there's no temporary table with that name.

That isn't going to work tremendously well. plpgsql will cache a plan
for that SELECT on first use, and creation of the temp table is not an
event that will cause replanning of a select that doesn't already use
the temp table.

If you're dead set on this design (which frankly doesn't seem like a
terribly great idea to me), try doing the initial probe with an EXECUTE
so it'll be replanned each time.

Or you might try examining the system catalogs directly rather than
relying on an attempted table access, eg

if not exists (select 1 from pg_catalog where relname =
'session' and pg_table_is_visible(oid))
then ... create it ...

That approach would work best if you *didn't* have any permanent
table that the temp tables were masking, which on the whole seems
like a smarter plan to me.

regards, tom lane