Re: plpgsql.consistent_into

On Sun, Jan 12, 2014 at 7:51 AM, Marko Tiikkaja <marko(at)joh(dot)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 to NULL
-- 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