Re: plpgsql.consistent_into

Greetings fellow elephants,

I would humbly like to submit for your consideration my proposal for alleviating pain caused by one of the most annoying footguns in PL/PgSQL: 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 (as opposed to INSERT/UPDATE/DELETE .. INTO, all of them yielding TOO_MANY_ROWS), which can hide subtle bugs in queries if during testing the query always returns only one row or the "correct" one happens to be picked up every time.  Additionally, the row_count() after execution is always going to be either 0 or 1, so even if you want to explicitly guard against potentially broken queries, you can't do so!

So I added the following compile-time option:


set plpgsql.consistent_into to true;

create or replace function footest() returns void as $$
declare
x int;
begin
  -- too many rows
  select 1 from foo into x;
end$$ language plpgsql;

select footest();
ERROR:  query returned more than one row

It defaults to false to preserve full backwards compatibility.  Also turning it on makes the executor try and find two rows, so it might have an effect on performance as well.  The patch, as currently written, also changes the behaviour of EXECUTE .. INTO, but I don't feel strongly about whether that should be affected as well or not.


Regards,
Marko Tiikkaja


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