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
Attachments:
plpgsql_mvardef.patchtext/x-patch; name=plpgsql_mvardef.patchDownload
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index e3a992c..2737a3a 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -167,6 +167,7 @@ static List *read_raise_options(void);
%type <declhdr> decl_sect
%type <varname> decl_varname
+%type <list> decl_varnames
%type <boolean> decl_const decl_notnull exit_type
%type <expr> decl_defval decl_cursor_query
%type <dtype> decl_datatype
@@ -471,9 +472,10 @@ decl_stmt : decl_statement
}
;
-decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull decl_defval
+decl_statement : decl_varnames decl_const decl_datatype decl_collate decl_notnull decl_defval
{
PLpgSQL_variable *var;
+ ListCell *lc;
/*
* If a collation is supplied, insert it into the
@@ -492,38 +494,44 @@ decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull
$3->collation = $4;
}
- var = plpgsql_build_variable($1.name, $1.lineno,
- $3, true);
- if ($2)
- {
- if (var->dtype == PLPGSQL_DTYPE_VAR)
- ((PLpgSQL_var *) var)->isconst = $2;
- else
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("row or record variable cannot be CONSTANT"),
- parser_errposition(@2)));
- }
- if ($5)
- {
- if (var->dtype == PLPGSQL_DTYPE_VAR)
- ((PLpgSQL_var *) var)->notnull = $5;
- else
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("row or record variable cannot be NOT NULL"),
- parser_errposition(@4)));
- }
- if ($6 != NULL)
+ foreach(lc, $1)
{
- if (var->dtype == PLPGSQL_DTYPE_VAR)
- ((PLpgSQL_var *) var)->default_val = $6;
- else
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("default value for row or record variable is not supported"),
- parser_errposition(@5)));
+ YYSTYPE *v = (YYSTYPE *) lfirst(lc);
+
+ var = plpgsql_build_variable(v->varname.name, v->varname.lineno,
+ $3, true);
+ if ($2)
+ {
+ if (var->dtype == PLPGSQL_DTYPE_VAR)
+ ((PLpgSQL_var *) var)->isconst = $2;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("row or record variable cannot be CONSTANT"),
+ parser_errposition(@2)));
+ }
+ if ($5)
+ {
+ if (var->dtype == PLPGSQL_DTYPE_VAR)
+ ((PLpgSQL_var *) var)->notnull = $5;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("row or record variable cannot be NOT NULL"),
+ parser_errposition(@4)));
+
+ }
+ if ($6 != NULL)
+ {
+ if (var->dtype == PLPGSQL_DTYPE_VAR)
+ ((PLpgSQL_var *) var)->default_val = $6;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("default value for row or record variable is not supported"),
+ parser_errposition(@5)));
+ }
}
}
| decl_varname K_ALIAS K_FOR decl_aliasitem ';'
@@ -773,6 +781,22 @@ decl_varname : T_WORD
}
;
+decl_varnames : decl_varname
+ {
+ YYSTYPE *v = palloc(sizeof(YYSTYPE));
+ v->varname.name = pstrdup($1.name);
+ v->varname.lineno = $1.lineno;
+ $$ = list_make1(v);
+ }
+ | decl_varnames ',' decl_varname
+ {
+ YYSTYPE *v = palloc(sizeof(YYSTYPE));
+ v->varname.name = pstrdup($3.name);
+ v->varname.lineno = $3.lineno;
+ $$ = lappend($1, v);
+ }
+ ;
+
decl_const :
{ $$ = false; }
| K_CONSTANT
Hello
+ it is natural in almost all languages including ADA
- it increases a distance between PL/pgSQL and PL/SQLI am don't think, so this feature is necessary, but I am not against it.
Regards
Pavel
2014-06-13 9:20 GMT+02:00 Quan Zongliang <quanzongliang@gmail.com>:
Show quoted text
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--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 :)
Pavel
Show quoted text
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
We can disallow custom initialization when when variables are declared as
list.
Quan' example is 100% valid in SQL/PSM and what I read about ADA then in
ADA too.
Regards
Pavel
2014-06-13 16:04 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
Show quoted text
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2014-06-13 16:17 GMT+02:00 Andres Freund <andres@2ndquadrant.com>:
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).
PL/pgSQL is based on PL/SQL, that is based on ADA. Next PL/SQL takes some
statements from SQL/PSM .. GET DIAGNOSTICS statement, and we implemented
these statements in PL/pgSQL too.
Some statements in PL/pgSQL are our design - RAISE is good example. So
PL/pgSQL is mix PL/SQL, SQL/PSM, and some proprietary
Regards
Pavel
Show quoted text
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. 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 $$
declare
a, b, c text;
begin
a := 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2014-06-13 17:32 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
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.
I don't think so this feature is 100% necessary, but a few users requested
some more compressed form of variable declarations.
we can allow multi variable declaration without initial value specification
so "a,b,c text" can be valid, and "a,b,c text := 'hello'" not
It is just step to users who knows this feature from others languages.
Regards
Pavel
Show quoted text
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Jun 13, 2014 at 11:32 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
Not withstanding the decision making of the SQL committee I was rejecting
as inconsistent:
SET random_1 = 0;
SET random_2 = 0;
SET random_3 = random(1234);
The ambiguity regarding re-execute or copy still remains.
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.
David J.
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers