Thread: PL/pgSQL question

PL/pgSQL question

From
Michael Meskes
Date:
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!


Re: PL/pgSQL question

From
"Michael Paesold"
Date:
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





Re: PL/pgSQL question

From
Jan Wieck
Date:
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 #


Re: PL/pgSQL question

From
Michael Meskes
Date:
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!