Hello
2010/9/30 Tom Lane <tgl@sss.pgh.pa.us>:
> Vincenzo Romano <vincenzo.romano@notorand.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>