Re: PL/pgSQL support to define multi variables once

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2014-06-13 16:12:36 +0200, Pavel Stehule wrote:
>> Quan' example is 100% valid in SQL/PSM and what I read about ADA then in
>> ADA too.

> So what? plpgsql is neither language and this doesn't seem to be the way
> to make them actually closer (which I doubt would be a good idea in the
> first place).

What plpgsql actually tries to model is Oracle's PL/SQL, in which this
syntax is specifically *not* allowed (at least according to the 2008-or-so
manual I have handy).

The SQL/PSM reference is kind of interesting, since as far as I can tell
the standard does allow this syntax but it fails to explain what the
initialization behavior is. The actual text of SQL:2011 14.4 <SQL
variable declaration> general rule 1 is:

If <SQL variable declaration> contains <default clause> DC, then let DV be
the <default option> contained in DC. Otherwise let DV be <null
specification>. Let SV be the variable defined by the <SQL variable
declaration>. The following SQL-statement is effectively executed:

SET SV = DV

It says "the variable", not "the variables", and definitely not "for each
variable". Are we supposed to read this as only one variable getting
initialized? Even assuming that that's an obvious thinko and they meant
to say "for each variable SV, the following is executed", it's unclear
whether DV is to be evaluated once, or once per variable. If it's a
volatile expression then that matters.

At the very least I think we should stay away from this syntax until
the SQL committee understand it better than they evidently do today.
I don't want to implement it and then get caught by a future
clarification that resolves the issue differently than we did.

regards, tom lane