Re: [9.0] On temporary tables

Hello

2010/9/30 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> 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.
>

I found a little bit faster solution a catching a exception.

http://okbob.blogspot.com/2008/11/plpgsql-and-temp-tables.html

but if you need a session variables, then you can use a plperl

http://www.postgresql.org/docs/9.0/static/plperl-global.html

Regards

Pavel Stehule

> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>