postgres.git / summary / log / commit / refs
commit 35a8ff7ae64d15dc028c1e33d404738fcaa8d629
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Tue Oct 27 21:24:02 2026 +0000
First released in: 14.25, 15.20, 16.16, 17.12, 18.7, 19.0
Fix deparsing of AT TIME ZONE and similar as FROM-clause functions.
ruleutils.c's looks_like_function() assumed that all COERCE_SQL_SYNTAX
cases produce output that looks like a function call (or at least
like one of the syntaxes the func_expr_windowless production accepts).
But AT TIME ZONE, AT LOCAL, OVERLAPS and IS xxx NORMALIZED print as
other sorts of a_expr, so those cases would produce invalid SQL syntax
when used in a FROM clause. Extend the logic to detect which specific
functions are safe or unsafe.
Author: Henson Choi <assam258@gmail.com>
Reviewed-by: Prashant Chinnam <crprashant@hotmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/CAAAe_zC68s-DwVX1o7C1HVcRk+Z+RTS6Vw19auZFfz=KaX8tZA@mail.gmail.com
Backpatch-through: 14
src/backend/utils/adt/ruleutils.c | 51 +++++++++++++++++++++++++++++--
src/test/regress/expected/create_view.out | 33 +++++++++++---------
src/test/regress/sql/create_view.sql | 3 +-
3 files changed, 68 insertions(+), 19 deletions(-)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index a52e8235dc5..7e78740e9d8 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -9974,9 +9974,49 @@ looks_like_function(Node *node)
switch (nodeTag(node))
{
case T_FuncExpr:
- /* OK, unless it's going to deparse as a cast */
- return (((FuncExpr *) node)->funcformat == COERCE_EXPLICIT_CALL ||
- ((FuncExpr *) node)->funcformat == COERCE_SQL_SYNTAX);
+ /* OK if it will print as a normal function call */
+ if (((FuncExpr *) node)->funcformat == COERCE_EXPLICIT_CALL)
+ return true;
+ /* SQL_SYNTAX needs case-by-case analysis; all else is unsafe */
+ if (((FuncExpr *) node)->funcformat != COERCE_SQL_SYNTAX)
+ return false;
+
+ /*
+ * Most SQL-syntax functions are accepted by func_expr_windowless,
+ * but get_func_sql_syntax() prints AT TIME ZONE, OVERLAPS and IS
+ * xxx NORMALIZED in forms that aren't accepted. All of the
+ * associated functions must be listed here.
+ */
+ switch (((FuncExpr *) node)->funcid)
+ {
+ /* AT TIME ZONE: */
+ case F_TIMEZONE_INTERVAL_TIMESTAMP:
+ case F_TIMEZONE_INTERVAL_TIMESTAMPTZ:
+ case F_TIMEZONE_INTERVAL_TIMETZ:
+ case F_TIMEZONE_TEXT_TIMESTAMP:
+ case F_TIMEZONE_TEXT_TIMESTAMPTZ:
+ case F_TIMEZONE_TEXT_TIMETZ:
+ /* OVERLAPS: */
+ case F_OVERLAPS_TIMESTAMPTZ_INTERVAL_TIMESTAMPTZ_INTERVAL:
+ case F_OVERLAPS_TIMESTAMPTZ_INTERVAL_TIMESTAMPTZ_TIMESTAMPTZ:
+ case F_OVERLAPS_TIMESTAMPTZ_TIMESTAMPTZ_TIMESTAMPTZ_INTERVAL:
+ case F_OVERLAPS_TIMESTAMPTZ_TIMESTAMPTZ_TIMESTAMPTZ_TIMESTAMPTZ:
+ case F_OVERLAPS_TIMESTAMP_INTERVAL_TIMESTAMP_INTERVAL:
+ case F_OVERLAPS_TIMESTAMP_INTERVAL_TIMESTAMP_TIMESTAMP:
+ case F_OVERLAPS_TIMESTAMP_TIMESTAMP_TIMESTAMP_INTERVAL:
+ case F_OVERLAPS_TIMESTAMP_TIMESTAMP_TIMESTAMP_TIMESTAMP:
+ case F_OVERLAPS_TIMETZ_TIMETZ_TIMETZ_TIMETZ:
+ case F_OVERLAPS_TIME_INTERVAL_TIME_INTERVAL:
+ case F_OVERLAPS_TIME_INTERVAL_TIME_TIME:
+ case F_OVERLAPS_TIME_TIME_TIME_INTERVAL:
+ case F_OVERLAPS_TIME_TIME_TIME_TIME:
+ /* IS xxx NORMALIZED: */
+ case F_IS_NORMALIZED:
+ return false;
+ default:
+ break;
+ }
+ return true;
case T_NullIfExpr:
case T_CoalesceExpr:
case T_MinMaxExpr:
@@ -10331,6 +10371,11 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context)
*
* Returns true if we successfully deparsed, false if we did not
* recognize the function.
+ *
+ * Most of these print as constructs that func_expr_windowless accepts.
+ * But AT TIME ZONE, OVERLAPS and IS xxx NORMALIZED print as a
+ * parenthesized a_expr instead, so a function added to them, or another
+ * one printed that way, must also be listed in looks_like_function().
*/
static bool
get_func_sql_syntax(FuncExpr *expr, deparse_context *context)
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index 6d216019350..c839d80790d 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -1901,22 +1901,25 @@ select * from
current_date as d,
localtimestamp(3) as t,
cast(1+2 as int4) as i4,
- cast(1+2 as int8) as i8;
+ cast(1+2 as int8) as i8,
+ cast('foo' is normalized as boolean) as isn;
select pg_get_viewdef('tt20v', true);
- pg_get_viewdef
----------------------------------------------
- SELECT c.c, +
- col.col, +
- d.d, +
- t.t, +
- i4.i4, +
- i8.i8 +
- FROM COALESCE(1, 2) c(c), +
- COLLATION FOR ('x'::text) col(col), +
- CURRENT_DATE d(d), +
- LOCALTIMESTAMP(3) t(t), +
- CAST(1 + 2 AS integer) i4(i4), +
- CAST((1 + 2)::bigint AS bigint) i8(i8);
+ pg_get_viewdef
+------------------------------------------------------------
+ SELECT c.c, +
+ col.col, +
+ d.d, +
+ t.t, +
+ i4.i4, +
+ i8.i8, +
+ isn.isn +
+ FROM COALESCE(1, 2) c(c), +
+ COLLATION FOR ('x'::text) col(col), +
+ CURRENT_DATE d(d), +
+ LOCALTIMESTAMP(3) t(t), +
+ CAST(1 + 2 AS integer) i4(i4), +
+ CAST((1 + 2)::bigint AS bigint) i8(i8), +
+ CAST(('foo'::text IS NORMALIZED) AS boolean) isn(isn);
(1 row)
-- reverse-listing of various special function syntaxes required by SQL
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index 5f941b10e79..0c690806cda 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -696,7 +696,8 @@ select * from
current_date as d,
localtimestamp(3) as t,
cast(1+2 as int4) as i4,
- cast(1+2 as int8) as i8;
+ cast(1+2 as int8) as i8,
+ cast('foo' is normalized as boolean) as isn;
select pg_get_viewdef('tt20v', true);
-- reverse-listing of various special function syntaxes required by SQL
[parent: 1fff28b14af8]