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.