Thread: proposal for 8.3: Simultaneous assignment for PL/pgSQL
Hello I suggest enhance plpgsq to simultaneous assignment. Main reason is missing parameter passing by reference and less readable getting values from functions with OUT parameters. Currently I have to write SELECT: SELECT INTO a,b,c out3fce(1) a,b,c := out3fce(1); -- Simultaneous assignment Any comments? Regards Pavel Stehule _________________________________________________________________ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/
"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> a,b,c := out3fce(1); -- Simultaneous assignment
I thought we rejected that idea once already, on the grounds that it
would make it too hard to tell the difference between intended code
and typos.
regards, tom lane
Tom Lane wrote: > "Pavel Stehule" <pavel.stehule@hotmail.com> writes: >> a,b,c := out3fce(1); -- Simultaneous assignment > > I thought we rejected that idea once already, on the grounds that it > would make it too hard to tell the difference between intended code > and typos. > In any case, I had some questions: . is it compatible with PLSQL? . can the effect be achieved by assigning to a composite? cheers andrew
> >"Pavel Stehule" <pavel.stehule@hotmail.com> writes: > > a,b,c := out3fce(1); -- Simultaneous assignment > >I thought we rejected that idea once already, on the grounds that it >would make it too hard to tell the difference between intended code >and typos. > Yes, because wasn't procedures with out params, my arguments wasn't strong. Older patch was too liberal. I can test equal type compatibility, count of variables and fileds in record compatibility. Pavel Stehule _________________________________________________________________ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/
> >Tom Lane wrote: > > "Pavel Stehule" <pavel.stehule@hotmail.com> writes: > >> a,b,c := out3fce(1); -- Simultaneous assignment > > > > I thought we rejected that idea once already, on the grounds that it > > would make it too hard to tell the difference between intended code > > and typos. > > > >In any case, I had some questions: > >. is it compatible with PLSQL? what I know no, but PLSQL has different mechanism for calling procedures. And first of all it knows references to variables. >. can the effect be achieved by assigning to a composite? > yes. I can use record type. But here is problem with less readability The best of is implementation of CALL statement, where I can transmit values "by" references. But it's not possible in Postgres :-(. I can't to select unambiguously called procedure. "I can, if I accept SQL Server syntax, where caller specify OUT, INOUT, IN flags too". I am unhappy with current situation, and I search good solution. Simultaneous assignment is simplest. Regards Pavel Stehule _________________________________________________________________ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/
On Mon, Aug 07, 2006 at 04:11:48PM +0200, Pavel Stehule wrote: > The best of is implementation of CALL statement, where I can transmit > values "by" references. But it's not possible in Postgres :-(. I can't to > select unambiguously called procedure. "I can, if I accept SQL Server > syntax, where caller specify OUT, INOUT, IN flags too". I am unhappy with > current situation, and I search good solution. Simultaneous assignment is > simplest. Well, you can implement it. After all, the CALL syntax is merely syntactic sugar. You could (if you wanted to) do the following: CREATE FUNCTION foo( a TEXT IN, b TEXT INOUT, c TEXT OUT ) as blah... And in a pl/pgsql function, translate: "CALL foo(a,b,c)" into "(b,c) = foo(a,b)" internally. Doesn't seem like that would be too hard. Have anice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Martijn van Oosterhout <kleptog@svana.org> writes:
> Well, you can implement it. After all, the CALL syntax is merely
> syntactic sugar. You could (if you wanted to) do the following:
> CREATE FUNCTION foo( a TEXT IN, b TEXT INOUT, c TEXT OUT ) as blah...
> And in a pl/pgsql function, translate: "CALL foo(a,b,c)"
> into "(b,c) = foo(a,b)" internally.
No, Pavel's right: that doesn't work because it's ambiguous. How do you
tell whether "CALL foo(a,b,c)" means
a,b,c := foo();b,c := foo(a);c := foo(a,b);select foo(a,b,c);
There could be functions foo matching all four interpretations.
regards, tom lane
> >Martijn van Oosterhout <kleptog@svana.org> writes: > > Well, you can implement it. After all, the CALL syntax is merely > > syntactic sugar. You could (if you wanted to) do the following: > > > CREATE FUNCTION foo( a TEXT IN, b TEXT INOUT, c TEXT OUT ) as blah... > > > And in a pl/pgsql function, translate: "CALL foo(a,b,c)" > > into "(b,c) = foo(a,b)" internally. > >No, Pavel's right: that doesn't work because it's ambiguous. How do you >tell whether "CALL foo(a,b,c)" means > > a,b,c := foo(); > b,c := foo(a); > c := foo(a,b); > select foo(a,b,c); > >There could be functions foo matching all four interpretations. we can do some hints: CALL foo(a, OUT b, OUT c) it's better than nothing comments? Regards Pavel Stehule _________________________________________________________________ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/
>Tom Lane wrote:
> > "Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> >> a,b,c := out3fce(1); -- Simultaneous assignment
> >
> > I thought we rejected that idea once already, on the grounds that it
> > would make it too hard to tell the difference between intended code
> > and typos.
> >
>
>In any case, I had some questions:
>
>. is it compatible with PLSQL?
>. can the effect be achieved by assigning to a composite?
I looked into SQL2003, and SQL2003 knows it (SQL/PSM):
<assignment statement> ::= <singleton variable assignment> | <multiple variable assignment>
<multiple variable assignment> ::= SET <assignment target list> <equals operator> <assigned row>
<assignment target list> ::= <left paren> <assignment target> [ { <comma> <assignment target> }... ]
<right paren>
<singleton variable assignment> ::= SET <assignment target> <equals operator> <assignment source>
Regards
Pavel Stehule
_________________________________________________________________
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com.
http://www.msn.cz/
Pavel Stehule wrote:
>
>> Tom Lane wrote:
>> > "Pavel Stehule" <pavel.stehule@hotmail.com> writes:
>> >> a,b,c := out3fce(1); -- Simultaneous assignment
>> >
>> > I thought we rejected that idea once already, on the grounds that it
>> > would make it too hard to tell the difference between intended code
>> > and typos.
>> >
>>
>> In any case, I had some questions:
>>
>> . is it compatible with PLSQL?
>> . can the effect be achieved by assigning to a composite?
>
> I looked into SQL2003, and SQL2003 knows it (SQL/PSM):
>
> <assignment statement> ::=
> <singleton variable assignment>
> | <multiple variable assignment>
> <multiple variable assignment> ::=
> SET <assignment target list> <equals operator> <assigned row>
> <assignment target list> ::=
> <left paren> <assignment target> [ { <comma> <assignment target>
> }... ] <right paren>
> <singleton variable assignment> ::=
> SET <assignment target> <equals operator> <assignment source>
>
The parentheses are apparently required for multiple variables, so in
our case it might look like this:
(a,b,c) := foo(bar);
That might overcome the objection Tom referred to, I guess?
cheers
andrew
Andrew Dunstan wrote: > Pavel Stehule wrote: > >I looked into SQL2003, and SQL2003 knows it (SQL/PSM): > > [grammar productions] > > The parentheses are apparently required for multiple variables, so in > our case it might look like this: > > (a,b,c) := foo(bar); > > That might overcome the objection Tom referred to, I guess? Are we intending to support SQL/PSM with PL/pgSQL? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On 8/8/06, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Are we intending to support SQL/PSM with PL/pgSQL? I hope not. While PL/pgSQL and SQL/PSM share some similarities, they should be totally separate. IIRC, EnterpriseDB had tried to sponsor someone to write SQL/PSM support for PostgreSQL a little over a year ago and no one wanted to do it. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
Andrew Dunstan <andrew@dunslane.net> writes:
> Pavel Stehule wrote:
>> I looked into SQL2003, and SQL2003 knows it (SQL/PSM):
>>
>> <assignment statement> ::=
>> <singleton variable assignment>
>> | <multiple variable assignment>
>> <multiple variable assignment> ::=
>> SET <assignment target list> <equals operator> <assigned row>
>> <assignment target list> ::=
>> <left paren> <assignment target> [ { <comma> <assignment target>
>> }... ] <right paren>
>> <singleton variable assignment> ::=
>> SET <assignment target> <equals operator> <assignment source>
> The parentheses are apparently required for multiple variables, so in
> our case it might look like this:
> (a,b,c) := foo(bar);
More to the point, a SET keyword is required too by that standard.
I concur with the other comment that plpgql is intended to mimic
Oracle PL/SQL, not SQL/PSM. If we try to follow two different leads
we are likely to find ourselves with a mess.
regards, tom lane
Tom, > I concur with the other comment that plpgql is intended to mimic > Oracle PL/SQL, not SQL/PSM. If we try to follow two different leads > we are likely to find ourselves with a mess. Well, the proposed functionality would be extremely useful in making PL/pgSQL a more robust language. So can we find a syntax that is unambiguously assignment? To be honest, I'm unclear on what's wrong with Pavel's suggested syntax. --Josh