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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers