Hello 2010/9/30 Tom Lane <t...@sss.pgh.pa.us>: > Vincenzo Romano <vincenzo.rom...@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 > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
