Thread: PL/pgSQL support to define multi variables once

Hi all,

Please find the attachment.

By my friend asking, for convenience,
support to define multi variables in single PL/pgSQL line.

Like this:

CREATE OR REPLACE FUNCTION try_mutlivardef() RETURNS text AS $$
DECLARE
local_a, local_b, local_c text := 'a1----';
BEGIN
return local_a || local_b || local_c;
end;
$$ LANGUAGE plpgsql;


Regards,
Quan Zongliang


---
此电子邮件没有病毒和恶意软件，因为 avast! 防病毒保护处于活动状态。
http://www.avast.com

On Fri, Jun 13, 2014 at 4:20 PM, Quan Zongliang <quanzongliang@gmail.com> wrote:
> By my friend asking, for convenience,
> support to define multi variables in single PL/pgSQL line.
>
> Like this:
>
> CREATE OR REPLACE FUNCTION try_mutlivardef() RETURNS text AS $$
> DECLARE
> local_a, local_b, local_c text := 'a1----';
> BEGIN
> return local_a || local_b || local_c;
> end;
> $$ LANGUAGE plpgsql;
I don't recall that this is possible. Have a look at the docs as well:
http://www.postgresql.org/docs/current/static/plpgsql-declarations.html
-- 
Michael

Hi

On 14/06/13 16:20, Quan Zongliang wrote:
> Hi all,
> 
> Please find the attachment.
> 
> By my friend asking, for convenience,
> support to define multi variables in single PL/pgSQL line.
> 
> Like this:
> 
> CREATE OR REPLACE FUNCTION try_mutlivardef() RETURNS text AS $$
> DECLARE
> local_a, local_b, local_c text := 'a1----';
> BEGIN
> return local_a || local_b || local_c;
> end;
> $$ LANGUAGE plpgsql;

Please submit this patch to the current commitfest:
 https://commitfest.postgresql.org/action/commitfest_view?id=22


Regards

Ian Barwick


-- Ian Barwick                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

On 06/13/2014 03:42 PM, Ian Barwick wrote:
> Hi
>
> On 14/06/13 16:20, Quan Zongliang wrote:
>> Hi all,
>>
>> Please find the attachment.
>>
>> By my friend asking, for convenience,
>> support to define multi variables in single PL/pgSQL line.
>>
>> Like this:
>>
>> CREATE OR REPLACE FUNCTION try_mutlivardef() RETURNS text AS $$
>> DECLARE
>> local_a, local_b, local_c text := 'a1----';
>> BEGIN
>> return local_a || local_b || local_c;
>> end;
>> $$ LANGUAGE plpgsql;
>
> Please submit this patch to the current commitfest:
>
>    https://commitfest.postgresql.org/action/commitfest_view?id=22
>
>
> Regards
>
> Ian Barwick
>
>
submitted
https://commitfest.postgresql.org/action/patch_view?id=1475


---
此电子邮件没有病毒和恶意软件，因为 avast! 防病毒保护处于活动状态。
http://www.avast.com

On Fri, Jun 13, 2014 at 4:43 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>
>
> 2014-06-13 9:41 GMT+02:00 Michael Paquier <michael.paquier@gmail.com>:
>
>> On Fri, Jun 13, 2014 at 4:20 PM, Quan Zongliang <quanzongliang@gmail.com>
>> wrote:
>> > By my friend asking, for convenience,
>> > support to define multi variables in single PL/pgSQL line.
>> >
>> > Like this:
>> >
>> > CREATE OR REPLACE FUNCTION try_mutlivardef() RETURNS text AS $$
>> > DECLARE
>> > local_a, local_b, local_c text := 'a1----';
>> > BEGIN
>> > return local_a || local_b || local_c;
>> > end;
>> > $$ LANGUAGE plpgsql;
>> I don't recall that this is possible. Have a look at the docs as well:
>> http://www.postgresql.org/docs/current/static/plpgsql-declarations.html
>> --
>
>
> It will be possible with Quan' patch :)
Sorry I misread his email.
-- 
Michael

Quan Zongliang <quanzongliang@gmail.com> writes:
> CREATE OR REPLACE FUNCTION try_mutlivardef() RETURNS text AS $$
> DECLARE
> local_a, local_b, local_c text := 'a1----';
> BEGIN
> return local_a || local_b || local_c;
> end;
> $$ LANGUAGE plpgsql;

This does not seem like a terribly good idea from here.  The main problem
with the syntax is that it's very unclear whether the initializer (if any)
applies to all the variables or just one.  C users will probably think
the latter but your example seems to suggest that you think the former.
I doubt that this adds so much usefulness that it's worth adding confusion
too.
        regards, tom lane

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).

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

Andres Freund <andres@2ndquadrant.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.
Otherwiselet DV be <null specification>. Let SV be the variable defined by the <SQL variable declaration>. The
followingSQL-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

Tom Lane-2 wrote
> Andres Freund <

> andres@

> > 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).
> 
> 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.

Haven't read the patch so, conceptually...

Its not quite as unclear as you make it out to be:

local_a, local_b, local_c text := 'a1----'; 

The "text" type declaration MUST apply to all three variables, so extending
that to include the default assignment would be the internally consistent
decision.

I'm not sure the following would be useful but:

var_1, var_2, var_3 integer := generate_series(1,3)

If the expression results in either a 3x1 or a 1x3 (in the three var
example) we could do an expansion.  If it results in a 1x1 that value would
be copied without re-executing the function.

Though I suppose someone might want to do the following:

random_1, random_2, random_3 float := random(1234);

The decision to copy, not re-execute, is safer to use as the behavior and
force explicitness in the re-execute situation.


Until then suggest that the friend do:

DECLARE 
local_a text := 'a1----';
local_b text := local_a;
local_c text := local_a;
BEGIN 



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/PL-pgSQL-support-to-define-multi-variables-once-tp5807168p5807215.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

David G Johnston <david.g.johnston@gmail.com> writes:
> Tom Lane-2 wrote
>> 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.

> Its not quite as unclear as you make it out to be:

Yes it is.

> Though I suppose someone might want to do the following:
> random_1, random_2, random_3 float := random(1234);
> The decision to copy, not re-execute, is safer to use as the behavior and
> force explicitness in the re-execute situation.

I would agree with that argument, if we both sat on the SQL committee and
were discussing how to resolve the ambiguity.  We don't, and we have no
good way to predict what they'll do (when and if they do anything :-().

The problem I've got is that a literal reading of the spec seems to
suggest multiple evaluation, since "DV" appears to refer to the syntactic
construct representing the initializer, not its evaluated value.  It's
hard to argue that the spec isn't telling us to do this:
    SET random_1 = random(1234);    SET random_2 = random(1234);    SET random_3 = random(1234);

That's not the reading I want, and it's not the reading you want either,
but there is nothing in the existing text that justifies single
evaluation.  So I think we'd be well advised to sit on our hands until
the committee clarifies that.  It's not like there is some urgent reason
to have this feature.
        regards, tom lane

Quan Zongliang wrote:
> Hi all,
> 
> Please find the attachment.
> 
> By my friend asking, for convenience,
> support to define multi variables in single PL/pgSQL line.
> 
> Like this:
> 
> CREATE OR REPLACE FUNCTION try_mutlivardef() RETURNS text AS $$
> DECLARE
> local_a, local_b, local_c text := 'a1----';
> BEGIN
> return local_a || local_b || local_c;
> end;
> $$ LANGUAGE plpgsql;

This seems pretty odd.  I think if you were to state it like this:

create or replace function multivar() returns text language plpgsql as $$
declarea, b, c text;
begina := b := c := 'a1--';return a || b || c;
end;
$$;

it would make more sense to me.  There are two changes to current
behavior in that snippet; one is the ability to declare several
variables in one go (right now you need one declaration for each), and
the other is that assignment is an expression that evaluates to the
assigned value, so you can assign that to another variable.

Personally, in C I don't think I do this:int a, b;
but always "int a; int b;" (two lines of course).  This is matter of
personal taste, but it seems clearer to me.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Fri, Jun 13, 2014 at 11:57 AM, David Johnston
<david.g.johnston@gmail.com> wrote:
>> That's not the reading I want, and it's not the reading you want either,
>> but there is nothing in the existing text that justifies single
>> evaluation.  So I think we'd be well advised to sit on our hands until
>> the committee clarifies that.  It's not like there is some urgent reason
>> to have this feature.
>
> Agreed.
>
> I don't suppose there is any support or prohibition on the :
>
> one,two,three integer := generate_series(1,3);
>
> interpretation...not that I can actually come up with a good use case that
> wouldn't be better implemented via a loop in the main body.

Based on these comments and the remarks by Alvaro and Andres, I think
it's clear that we should reject this patch.  The number of patches
that get through with -1 votes from 3 committers is very small, if not
zero.  While I like the feature in the abstract, I agree with Tom that
it would be better to wait until we have more clarity about what the
semantics are supposed to be.

I will update the CommitFest app accordingly.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company