Improve the handling of result type coercions in SQL functions.
Tom Lane <tgl@sss.pgh.pa.us>
Improve the handling of result type coercions in SQL functions. Use the parser's standard type coercion machinery to convert the output column(s) of a SQL function's final SELECT or RETURNING to the type(s) they should have according to the function's declared result type. We'll allow any case where an assignment-level coercion is available. Previously, we failed unless the required coercion was a binary-compatible one (and the documentation ignored this, falsely claiming that the types must match exactly). Notably, the coercion now accounts for typmods, so that cases where a SQL function is declared to return a composite type whose columns are typmod-constrained now behave as one would expect. Arguably this aspect is a bug fix, but the overall behavioral change here seems too large to consider back-patching. A nice side-effect is that functions can now be inlined in a few cases where we previously failed to do so because of type mismatches. Discussion: https://postgr.es/m/18929.1574895430@sss.pgh.pa.us
Files
| Path | Change | +/− |
|---|---|---|
| doc/src/sgml/xfunc.sgml | modified | +21 −23 |
| src/backend/catalog/pg_proc.c | modified | +8 −3 |
| src/backend/executor/functions.c | modified | +291 −217 |
| src/backend/optimizer/util/clauses.c | modified | +48 −83 |
| src/include/executor/functions.h | modified | +4 −4 |
| src/test/regress/expected/rangefuncs.out | modified | +209 −1 |
| src/test/regress/sql/rangefuncs.sql | modified | +75 −1 |
Documentation touched
Discussion
- Modernizing SQL functions' result type coercions 3 messages · 2019-11-27 → 2020-01-08