Re: [HACKERS] PL/pgSQL support to define multi variables once
On Fri, Jun 13, 2014 at 11:57 AM, David Johnston david.g.johns...@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
Re: [HACKERS] PL/pgSQL support to define multi variables once
Hello + it is natural in almost all languages including ADA - it increases a distance between PL/pgSQL and PL/SQL I 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 quanzongli...@gmail.com: 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
Re: [HACKERS] PL/pgSQL support to define multi variables once
On Fri, Jun 13, 2014 at 4:20 PM, Quan Zongliang quanzongli...@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
Re: [HACKERS] PL/pgSQL support to define multi variables once
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
Re: [HACKERS] PL/pgSQL support to define multi variables once
2014-06-13 9:41 GMT+02:00 Michael Paquier michael.paqu...@gmail.com: On Fri, Jun 13, 2014 at 4:20 PM, Quan Zongliang quanzongli...@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 Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL support to define multi variables once
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
Re: [HACKERS] PL/pgSQL support to define multi variables once
On Fri, Jun 13, 2014 at 4:43 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 2014-06-13 9:41 GMT+02:00 Michael Paquier michael.paqu...@gmail.com: On Fri, Jun 13, 2014 at 4:20 PM, Quan Zongliang quanzongli...@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
Re: [HACKERS] PL/pgSQL support to define multi variables once
Quan Zongliang quanzongli...@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
Re: [HACKERS] PL/pgSQL support to define multi variables once
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 t...@sss.pgh.pa.us: Quan Zongliang quanzongli...@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
Re: [HACKERS] PL/pgSQL support to define multi variables once
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
Re: [HACKERS] PL/pgSQL support to define multi variables once
2014-06-13 16:17 GMT+02:00 Andres Freund and...@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 Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
Re: [HACKERS] PL/pgSQL support to define multi variables once
Andres Freund and...@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
Re: [HACKERS] PL/pgSQL support to define multi variables once
Tom Lane-2 wrote Andres Freund lt; andres@ gt; 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
Re: [HACKERS] PL/pgSQL support to define multi variables once
David G Johnston david.g.johns...@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
Re: [HACKERS] PL/pgSQL support to define multi variables once
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 Herrerahttp://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
Re: [HACKERS] PL/pgSQL support to define multi variables once
2014-06-13 17:32 GMT+02:00 Tom Lane t...@sss.pgh.pa.us: David G Johnston david.g.johns...@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 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
Re: [HACKERS] PL/pgSQL support to define multi variables once
On Fri, Jun 13, 2014 at 11:32 AM, Tom Lane t...@sss.pgh.pa.us wrote: David G Johnston david.g.johns...@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.
