Re: plpgsql.consistent_into

On 1/14/14, 6:15 PM, Tom Lane wrote:
> Marko Tiikkaja <marko(at)joh(dot)to> writes:
>> How about:
>
>> (a) = SELECT 1;
>> (a, b) = SELECT 1, 2;
>> (a, b) = INSERT INTO foo RETURNING col1, col2;
>
>> 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.
>
> The idea of inventing new syntax along this line seems like a positive
> direction to pursue. Since assignment already rejects multiple rows
> from the source expression, this wouldn't be weirdly inconsistent.
>
> It might be worth thinking about the <multiple column assignment> UPDATE
> syntax that's in recent versions of the SQL standard:
>
> UPDATE targettab SET (a, b, c) = row-valued-expression [ , ... ] [ WHERE ... ]
>
> We don't actually implement this in PG yet, except for trivial cases, but
> it will certainly happen eventually. I think your sketch above deviates
> unnecessarily from what the standard says for UPDATE. In particular
> I think it'd be better to write things like
>
> (a, b) = ROW(1, 2);
> (a, b, c) = (SELECT x, y, z FROM foo WHERE id = 42);
>
> which would exactly match what you'd write in a multiple-assignment
> UPDATE, and it has the same rejects-multiple-rows semantics too.

Hmm. That's a fair point I did not consider.

> Also note that the trivial cases we do already implement in UPDATE
> look like
>
> UPDATE targettab SET (a, b, c) = (1, 2, 3) [ WHERE ... ]
>
> that is, we allow a row constructor where the optional keyword ROW has
> been omitted. I think people would expect to be able to write this in
> plpgsql:
>
> (a, b) = (1, 2);
>
> Now, this doesn't provide any guidance for INSERT/UPDATE/DELETE RETURNING,
> but frankly I don't feel any need to invent new syntax for those, since
> RETURNING INTO already works the way you want.

Yeah, I don't feel strongly about having to support them with this
syntax. The inconsistency is a bit ugly, but it's not the end of the world.

> I'm not too sure what it'd take to make this work. Right now,
>
> SELECT (SELECT x, y FROM foo WHERE id = 42);
>
> would generate "ERROR: subquery must return only one column", but
> I think it's mostly a historical artifact that it does that rather than
> returning a composite value (of an anonymous record type). If we were
> willing to make that change then it seems like it'd be pretty
> straightforward to teach plpgsql to handle
>
> (a, b, ...) = row-valued-expression
>
> where there wouldn't actually be any need to parse the RHS any differently
> from the way plpgsql parses an assignment RHS right now. Which would be
> a good thing IMO. If we don't generalize the behavior of scalar
> subqueries then plpgsql would have to jump through a lot of hoops to
> support the subselect case.

You can already do the equivalent of (a,b,c) = (1,2,3) with SELECT ..
INTO. Would you oppose to starting the work on this by only supporting
the subquery syntax, with the implementation being similar to how we
currently handle SELECT .. INTO? If we could also not flat out reject
including that into 9.4, I would be quite happy.

Regards,
Marko Tiikkaja