Re: PL/pgSQL question

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