Hi pá 30. 10. 2026 v 12:43 odesílatel Jeevan Chalke < [email protected]> napsal:
> Hi, > > The documentation for plpgsql.extra_warnings / plpgsql.extra_errors says > that the too_many_rows check will "*check if a given query returns more > than one row when an INTO clause is used*" *[1]*. Only static SQL > statements implement this (exec_stmt_execsql()). exec_stmt_dynexecute() > only looks at STRICT, so EXECUTE ... INTO silently uses the first row and > nothing is reported: > > set plpgsql.extra_warnings = 'too_many_rows'; > do $$ declare x int; > begin > execute 'select g from generate_series(1,2) g' into x; > end $$; > DO > > The same statement written as static SQL does warn (or fail, with > extra_errors). > > The attached patch computes too_many_rows_level in exec_stmt_dynexecute() > the same way exec_stmt_execsql() does, and reports at that level when > more than one row comes back and STRICT was not specified. With it: > > WARNING: query returned more than one row > HINT: Make sure the query returns a single row, or use LIMIT 1. > DO > > and with plpgsql.extra_errors the same message is raised as an ERROR. > Checking costs nothing extra here, since EXECUTE already fetches all the > rows (no row limit is passed to SPI). > > While there, I added the "Make sure the query returns a single row, or > use LIMIT 1." hint to the error raised for EXECUTE ... INTO STRICT, so > that it matches the error for static SQL. This changes the expected output > of three existing STRICT tests, which now show the HINT line. > > The new tests are in the "runtime extra checks" section of plpgsql.sql and > cover EXECUTE ... INTO with both extra_warnings and extra_errors. > > This is a behavior change: someone who has too_many_rows in extra_errors > may start getting errors from EXECUTE ... INTO statements that used to > pass. So I think this should go to master only (or v19, as it is yet to > release), but I'd like to hear what others think. > > Yes, this is my omission. The dynamic SQL is more problematic - it allows multiquery (2026-10-30 14:24:06) postgres=# do $$ declare r record; begin execute 'select 1 union select 2; select 2' into strict r; raise notice '%', r; end; $$; NOTICE: (2) DO Maybe extra errors should handle this case too. regards Pavel > Thanks > > *[1] > https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS > <https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS>* > > -- > *Jeevan Chalke* > *Senior Principal Engineer, Engineering Manager* > *Product Development* > > enterprisedb.com <https://www.enterprisedb.com> >
