2010/9/30 Vincenzo Romano <vincenzo.romano@xxxxxxxxxxx>: > 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. Quoting from documentation (v9.0.0 at chapter 35.6, v8.4.4 at chapter 34.6) "A VOLATILE function can do anything, including modifying the database. It can return different results on successive calls with the same arguments. The optimizer makes no assumptions about the behavior of such functions. A query using a volatile function will re-evaluate the function at every row where its value is needed." So, my question below stands still. > Is the planner caching the plan even in case of VOLATILE functions? I was expecting a volatile function not to be planned at definition time, but rather at execution time. Is this assumption wrong? -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
