Re: plpgsql.consistent_into

2014/1/15 Jim Nasby <jim(at)nasby(dot)net>

> On 1/14/14, 11:15 AM, Tom Lane wrote:
>
>> 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.
>>
>
> Do we actually support = right now? We already support
>
> v_field := field FROM table ... ;
>
> and I think it's a bad idea to have different meaning for = and :=.

It is probably second the most ugly thing on plpgsql. I don't know about
other crippled embedded SQL statement in any stored procedure language.

Regards

Pavel

>
>
> 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.
>>
>
> I have no idea if this is related or not, but I would REALLY like for this
> to work (doesn't in 8.4, AFAIK not in 9.1 either...)
>
> CREATE FUNCTION f(int) RETURNS text STABLE LANGUAGE sql AS ( SELECT field
> FROM table WHERE table_id = $1 );
> SELECT f(blah_id) FROM ...
>
> to be equivalent to
>
> SELECT ( SELECT field FROM table WHERE table_id = blah_id ) FROM ...
>
> That would make it very easy to do a lot of code simplification with no
> performance loss.
>
> --
> Jim C. Nasby, Data Architect jim(at)nasby(dot)net
> 512.569.9461 (cell) http://jim.nasby.net
>
>
> --
> 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
>