Thread: PL/pgSQL question
Hi, is a pl/pgSQL function completely parsed once? Or is only the next statement parsed as with many interpreters? If it's the latter it would mean one has to run each branch just to see if the syntax is correct. Is that true? Michael -- Michael Meskes Michael@Fam-Meskes.De Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
Michael Meskes wrote: > Hi, > > is a pl/pgSQL function completely parsed once? Or is only the next > statement parsed as with many interpreters? If it's the latter it would > mean one has to run each branch just to see if the syntax is correct. Is > that true? > > Michael If the docs are true, than the plain PL/pgSQL code is parsed at once, but SQL expressions and queries are not prepared until the branch is used. But read for yourself. To quote from Programmers Guide (Chapter 23, Section 1): "The PL/pgSQL call handler parses the function's source text and produces an internal binary instruction tree the first time the function is called (within any one backend process). The instruction tree fully translates the PL/pgSQL statement structure, but individual SQL expressions and SQL queries used in the function are not translated immediately. As each expression and SQL query is first used in the function, the PL/pgSQL interpreter creates a prepared execution plan (using the SPI manager's SPI_prepare and SPI_saveplan functions). Subsequent visits to that expression or query re-use the prepared plan. Thus, a function with conditional code that contains many statements for which execution plans might be required, will only prepare and save those plans that are really used during the lifetime of the database connection. This can provide a considerable savings of parsing activity. A disadvantage is that errors in a specific expression or query may not be detected until that part of the function is reached in execution." Regards, Michael
Michael Paesold wrote: > > Michael Meskes wrote: > > > Hi, > > > > is a pl/pgSQL function completely parsed once? Or is only the next > > statement parsed as with many interpreters? If it's the latter it would > > mean one has to run each branch just to see if the syntax is correct. Is > > that true? > > > > Michael > > If the docs are true, than the plain PL/pgSQL code is parsed at once, > but SQL expressions and queries are not prepared until the branch is > used. But read for yourself. That's the way I implemented it. Unless someone changed it, the documentation is correct. Someone might think now it'd be at least handy to have a mechanism to enforce parsing of all expressions and queries for debugging purposes. But that's not that easy. As soon as you use for example a record variable, each reference to one of the result row columns is of unknown datatype until that query is actually executed. You cannot parse an SQL query with unknown parameters via SPI. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Thanks for the explanation. On Thu, Sep 19, 2002 at 03:46:05PM -0400, Jan Wieck wrote: > Someone might think now it'd be at least handy to have a mechanism to > enforce parsing of all expressions and queries for debugging purposes. > But that's not that easy. As soon as you use for example a record > variable, each reference to one of the result row columns is of unknown > datatype until that query is actually executed. You cannot parse an SQL > query with unknown parameters via SPI. That's what I expected. I just wanted to be sure, before I tell something that's not correct. Michael -- Michael Meskes Michael@Fam-Meskes.De Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!