2014/1/14 Marko Tiikkaja <ma...@joh.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 <ma...@joh.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Reply via email to