Hello 2010/9/30 Tom Lane <tgl@xxxxxxxxxxxxx>: > Vincenzo Romano <vincenzo.romano@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
