Re: 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(dot)stehule(at)hotmail(dot)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(dot)stehule(at)hotmail(dot)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(dot)stehule(at)hotmail(dot)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(dot)stehule(at)hotmail(dot)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(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout <kleptog(at)svana(dot)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(at)svana(dot)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(dot)stehule(at)hotmail(dot)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(dot)stehule(at)hotmail(dot)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(at)commandprompt(dot)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(at)enterprisedb(dot)com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/

Andrew Dunstan <andrew(at)dunslane(dot)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