Re: plpgsql.consistent_into

2014/1/14 Marko Tiikkaja <marko(at)joh(dot)to>

> On 1/14/14 12:28 PM, Marti Raudsepp wrote:
>
>> I've always hated INTO in procedures since it makes the code harder to
>> follow and has very different behavior on the SQL level, in addition
>> to the multi-row problem you bring up. If we can make assignment
>> syntax more versatile and eventually replace INTO, then that solves
>> multiple problems in the language without breaking backwards
>> compatibility.
>>
>
> I don't personally have a problem with INTO other than the behaviour that
> started this thread. But I'm willing to consider other options.
>
>
> On Tue, Jan 14, 2014 at 4:30 AM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
>>
>>> On 2014-01-14 02:54, Marti Raudsepp wrote:
>>>
>>>> But PL/pgSQL already has an assignment syntax with the behavior you
>>>> want:
>>>>
>>>
>>> According to the docs, that doesn't set FOUND which would make this a
>>> pain
>>> to deal with..
>>>
>>
>> Right you are. If we can extend the syntax then we could make it such
>> that "= SELECT" sets FOUND and other diagnostics, and a simple
>> assignment doesn't. Which makes sense IMO:
>>
>> a = 10; -- simple assignments really shouldn't affect FOUND
>>
>
> With you so far.
>
>
> With explicit SELECT, clearly the intent is to perform a query:
>> a = SELECT foo FROM table;
>> And this could also work:
>> a = INSERT INTO table (foo) VALUES (10) RETURNING foo_id;
>>
>
> I'm not sure that would work with the grammar. Basically what PL/PgSQL
> does right now is for a statement like:
>
> a = 1;
>
> It parses the "a =" part itself, and then just reads until the next
> unquoted semicolon without actually looking at it, and slams a "SELECT " in
> front of it. With this approach we'd have to look into the query and try
> and guess what it does. That might be possible, but I don't like the idea.
>
>
> AFAICT the fact that this works is more of an accident and should be
>> discouraged. We can leave it as is for compatibility's sake:
>> a = foo FROM table;
>>
>
> I've always considered that ugly (IIRC it's still undocumented as well),
> and would encourage people not to do that.
>
>
> Now, another question is whether it's possible to make the syntax
>> work. Is this an assignment from the result of a subquery, or is it a
>> query by itself?
>> a = (SELECT foo FROM table);
>>
>
only this form is allowed in SQL/PSM - and it has some logic - you can
assign result of subquery (should be one row only) to variable.

>
> That looks like a scalar subquery, which is wrong because they can't
> return more than one column (nor can they be INSERT etc., obviously).
>
> How about:
>
> (a) = SELECT 1;
> (a, b) = SELECT 1, 2;
> (a, b) = INSERT INTO foo RETURNING col1, col2;
>
>
I prefer subquery only syntax - a := (some) or (a,b,c) = (some a,b,c) with
possible enhancing for statements with RETURNING

a advance is compatibility with DB2 (SQL/PSM) syntax - and this code is
written now - it is done in my sql/psm implementation

Regards

Pavel

> Same semantics: TOO_MANY_ROWS on rows > 1, sets FOUND and row_count.
> AFAICT this can be parsed unambiguously, too, and we don't need to look at
> the query string because this is new syntax.
>
>
> Regards,
> Marko Tiikkaja
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>