Re: plpgsql.consistent_into

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 :=.

> 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@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net