Обсуждение: SQL Functions vs PL/PgSQL
Hi!
Does postgres store execution plan's for user-defined SQL functions as the PL/pgSQL interpreter does. And if so - will this only be prepared after an initial call to the function? Can't seem to find this information in the docs!?
Thanks,
Johnny Kristensen
Johnny Kristensen wrote: > Hi! > Does postgres store execution plan's for user-defined SQL functions as > the PL/pgSQL interpreter does. And if so - will this only be prepared > after an initial call to the function? Can't seem to find this > information in the docs!? > Thanks, > Johnny Kristensen You need Postgresql 7.3 to do this. Check sql function "PREPARE". Regards, Tomasz Myrta
Johnny, > Does postgres store execution plan's for user-defined SQL functions as the > PL/pgSQL interpreter does. And if so - will this only be prepared after an > initial call to the function? Can't seem to find this information in the > docs!? Thanks, Unless you know something I don't, I do not believe that PL/pgSQL stores execution plans for functions. -- Josh Berkus Aglio Database Solutions San Francisco
On Thu, Feb 13, 2003 at 09:02:05AM -0800, Josh Berkus wrote: > Johnny, > > > Does postgres store execution plan's for user-defined SQL functions as the > > PL/pgSQL interpreter does. And if so - will this only be prepared after an > > initial call to the function? Can't seem to find this information in the > > docs!? Thanks, > > Unless you know something I don't, I do not believe that PL/pgSQL stores > execution plans for functions. AFAIK execution plans for PL/pgSQL functions were cached once per backend. No? -Roberto -- +----| Roberto Mello - http://www.brasileiro.net/ |------+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + If CPU < 486 then upgrade (486,OS/2 3.0) : while < 586...
Roberto Mello <rmello@cc.usu.edu> writes: > On Thu, Feb 13, 2003 at 09:02:05AM -0800, Josh Berkus wrote: >> Unless you know something I don't, I do not believe that PL/pgSQL stores >> execution plans for functions. > AFAIK execution plans for PL/pgSQL functions were cached once per backend. > No? Yes. This is explained in the docs ... http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql.html#PLPGSQL-OVERVIEW regards, tom lane
Of course what I meant was if postgres stores execution plans for queries in user-defined SQL functions. From the docs Ican see that PL/PgSQL uses the SPI-interface to create and store execution plans for queries for each connection. But I'lluse the PREPARE function like Thomasz Myrta suggested. /Johnny ----- Original Message ----- From: "Josh Berkus" <josh@agliodbs.com> To: "Johnny Kristensen" <johnnybo1@mail.tele.dk>; <pgsql-sql@postgresql.org> Sent: Thursday, February 13, 2003 6:02 PM Subject: Re: [SQL] SQL Functions vs PL/PgSQL Johnny, > Does postgres store execution plan's for user-defined SQL functions as the > PL/pgSQL interpreter does. And if so - will this only be prepared after an > initial call to the function? Can't seem to find this information in the > docs!? Thanks, Unless you know something I don't, I do not believe that PL/pgSQL stores execution plans for functions. -- Josh Berkus Aglio Database Solutions San Francisco ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)