postgres.git / summary / log / commit / refs

commit    17a0604c2d626fa4adb2a98f584fc6b7ee72e663
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 | 55 +++++++++++++++++++++++++++++-- src/test/regress/expected/create_view.out | 33 ++++++++++--------- src/test/regress/sql/create_view.sql | 3 +- 3 files changed, 72 insertions(+), 19 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 9981ecc0531..d9841d00991 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -10744,9 +10744,53 @@ 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, AT LOCAL, + * 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: + /* AT LOCAL: */ + case F_TIMEZONE_TIMESTAMP: + case F_TIMEZONE_TIMESTAMPTZ: + case F_TIMEZONE_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: @@ -11180,6 +11224,11 @@ get_windowfunc_expr_helper(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, AT LOCAL, 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 cc4c4aeea26..0833a5cd247 100644 --- a/src/test/regress/expected/create_view.out +++ b/src/test/regress/expected/create_view.out @@ -1949,22 +1949,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 316b60ea28b..e3db28999cb 100644 --- a/src/test/regress/sql/create_view.sql +++ b/src/test/regress/sql/create_view.sql @@ -712,7 +712,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: 35f60e5ceab7]