On Sun, Jan 12, 2014 at 7:51 AM, Marko Tiikkaja <marko@joh.to> wrote:
> the behaviour of SELECT .. INTO when the query returns more than one row.
> Some of you might know that no exception is raised in this case
Agreed. But I also agree with the rest of the thread about changing
current INTO behavior and introducing new GUC variables.
But PL/pgSQL already has an assignment syntax with the behavior you want:
DECLARE foo int;
BEGIN foo = generate_series(1,1); -- this is OK foo = generate_series(1,2); -- fails foo = 10 WHERE FALSE; -- sets foo
toNULL -- And you can actually do: foo = some_col FROM some_table WHERE bar=10;
END;
So if we extend this syntax to support multiple columns, it should
satisfy the use cases you care about.
foo, bar = col1, col2 FROM some_table WHERE bar=10;
It's ugly without the explicit SELECT though. Perhaps make the SELECT optional:
foo, bar = SELECT col1, col2 FROM some_table WHERE bar=10;
I think that's more aesthetically pleasing than INTO and also looks
more familiar to other languages.
Plus, now you can copy-paste the query straight to an SQL shell
without another footgun involving creating new tables in your
database.
Regards,
Marti