From 71ccf60744557de300503479ca6b946126b90c2a Mon Sep 17 00:00:00 2001 From: jian he Date: Mon, 10 Aug 2026 23:54:55 +0900 Subject: [PATCH] Remove stray spaces and column qualifiers from deparsed RPR clauses Two defects in how ruleutils.c prints a row pattern window. Both of the functions below come from the base series' rewriter patch, which is where the RPR deparse was added. get_rule_windowspec() printed AFTER MATCH, INITIAL, PATTERN and DEFINE as four independent blocks. Each was gated on needspace and each opened with a newline of its own, so the separator space needspace inserted landed at the end of the preceding line -- the frame clause's line for the first of them. The DEFINE block also left one space before the window spec's closing parenthesis. The four clauses only ever appear together: PATTERN is mandatory in the row pattern common syntax, and yields INITIAL for both spellings the grammar accepts. Fold them into the rpPattern branch and drop needspace, emitting INITIAL as a literal. The closing parenthesis stays outside the branch so a non-RPR window still round-trips. get_rule_define() printed the columns a DEFINE clause references with whatever qualification the Var carried. The qualifier slot in a DEFINE expression is for pattern variables, so a table-qualified column is not something the parser reads back -- every qualified spelling is rejected, which leaves the bare name as the only re-parseable one. Set varprefix to false for the clause and restore it afterwards. A view over more than one relation can still deparse to a DEFINE column that has become ambiguous, and the expected output records that failure: the DEFINE grammar admits no qualifier, so the deparser has no other spelling to emit. The limitation is not confined to columns that become ambiguous later: a view created over a join whose sides share a column name can be unrestorable from the start if a DEFINE reads ROW(alias.*), because the expansion is deparsed unqualified. That spelling is handled with the whole-row gate, not here. The ambiguity itself is closed later in the series, by the commit that pins the DEFINE column names when deparsing; what this commit records is the state before that. The serialization tests, which keep their objects in place so that pg_dump and pg_upgrade exercise the round trip, had no view over more than one relation at all, so the varprefix change had no coverage among them; add one. Add the ambiguity cases to the same section, since that is where their subject belongs. Those views stay temporary and are dropped: sv is deliberately unrestorable, so leaving it in place would hand pg_dump a view that cannot be restored. sv4 shows the alias spelling that survives, so its definition is printed and not only selected from. Three comments no longer introduce themselves as regression tests for defects that only ever existed inside this series. The closing RESET client_min_messages goes as well: it was the last statement of the file, where nothing is left to observe the setting. The reformatting accounts for the bulk of the expected output changes -- the removed spaces and the column widths psql recomputes from them. --- src/backend/utils/adt/ruleutils.c | 61 +- src/test/regress/expected/rpr_base.out | 890 ++++++++++++---------- src/test/regress/expected/rpr_explain.out | 842 ++++++++++---------- src/test/regress/sql/rpr_base.sql | 57 +- src/test/regress/sql/rpr_explain.sql | 6 +- 5 files changed, 998 insertions(+), 858 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index d16af6b059c..180ad5627e7 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -7247,10 +7247,9 @@ static void get_rule_define(List *defineClause, deparse_context *context) { StringInfo buf = context->buf; - const char *sep; + const char *sep = " "; bool save_inrprdefine = context->inRPRDefine; - - sep = " "; + bool save_varprefix = context->varprefix; /* * Within the DEFINE clause an unqualified prev/next/first/last is a @@ -7259,12 +7258,17 @@ get_rule_define(List *defineClause, deparse_context *context) */ context->inRPRDefine = true; + /* + * DEFINE clause referenced columns cannot be table/schema-qualified: the + * qualifier slot is for pattern variables, so print bare column names. + */ + context->varprefix = false; + /* * A name here is always followed by AS and so cannot start a PERMUTE * construct, which is why plain quote_identifier() is enough: the same * variable may print bare here and quoted in the PATTERN. */ - foreach_node(TargetEntry, te, defineClause) { appendStringInfo(buf, "%s%s AS ", sep, quote_identifier(te->resname)); @@ -7272,6 +7276,7 @@ get_rule_define(List *defineClause, deparse_context *context) sep = ",\n "; } + context->varprefix = save_varprefix; context->inRPRDefine = save_inrprdefine; } @@ -7363,49 +7368,27 @@ get_rule_windowspec(WindowClause *wc, List *targetList, get_window_frame_options(wc->frameOptions, wc->startOffset, wc->endOffset, context); - needspace = true; } - /* RPR */ - if (wc->rpSkipTo == ST_NEXT_ROW) - { - if (needspace) - appendStringInfoChar(buf, ' '); - appendStringInfoString(buf, - "\n AFTER MATCH SKIP TO NEXT ROW "); - needspace = true; - } - else if (wc->rpSkipTo == ST_PAST_LAST_ROW) - { - if (needspace) - appendStringInfoChar(buf, ' '); - appendStringInfoString(buf, - "\n AFTER MATCH SKIP PAST LAST ROW "); - needspace = true; - } - if (wc->initial) - { - if (needspace) - appendStringInfoChar(buf, ' '); - appendStringInfoString(buf, "\n INITIAL"); - needspace = true; - } + /* RPR clauses start their own line, so no separator space is wanted */ if (wc->rpPattern) { - if (needspace) - appendStringInfoChar(buf, ' '); - appendStringInfoString(buf, "\n PATTERN "); + if (wc->rpSkipTo == ST_NEXT_ROW) + appendStringInfoString(buf, + "\n AFTER MATCH SKIP TO NEXT ROW"); + else + { + Assert(wc->rpSkipTo == ST_PAST_LAST_ROW); + + appendStringInfoString(buf, + "\n AFTER MATCH SKIP PAST LAST ROW"); + } + + appendStringInfoString(buf, "\n INITIAL\n PATTERN "); get_rule_pattern(wc->rpPattern, context); - needspace = true; - } - if (wc->defineClause) - { - if (needspace) - appendStringInfoChar(buf, ' '); appendStringInfoString(buf, "\n DEFINE\n"); get_rule_define(wc->defineClause, context); - appendStringInfoChar(buf, ' '); } appendStringInfoChar(buf, ')'); diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index 827dd5aac45..bd6904ecc66 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -2660,32 +2660,32 @@ CREATE VIEW navns_fn AS ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL PATTERN (A+) DEFINE A AS rpr_navns.prev(val) = -999); SELECT pg_get_viewdef('navns_nav'); - pg_get_viewdef ---------------------------------------------------------------------------------------------- - SELECT id, + - count(*) OVER w AS cnt + - FROM nt + - WINDOW w AS (PARTITION BY g ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (start up+) + - DEFINE + - start AS true, + - up AS (val > PREV(val)) ); + pg_get_viewdef +-------------------------------------------------------------------------------------------- + SELECT id, + + count(*) OVER w AS cnt + + FROM nt + + WINDOW w AS (PARTITION BY g ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (start up+) + + DEFINE + + start AS true, + + up AS (val > PREV(val))); (1 row) SELECT pg_get_viewdef('navns_fn'); - pg_get_viewdef ---------------------------------------------------------------------------------------------- - SELECT id, + - count(*) OVER w AS cnt + - FROM nt + - WINDOW w AS (PARTITION BY g ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a+) + - DEFINE + - a AS (rpr_navns.prev(val) = '-999'::integer) ); + pg_get_viewdef +-------------------------------------------------------------------------------------------- + SELECT id, + + count(*) OVER w AS cnt + + FROM nt + + WINDOW w AS (PARTITION BY g ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS (rpr_navns.prev(val) = '-999'::integer)); (1 row) DROP VIEW navns_nav, navns_fn; @@ -2698,17 +2698,17 @@ CREATE VIEW navns_fn_last AS ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL PATTERN (A+) DEFINE A AS rpr_navns.last(val) = -999); SELECT pg_get_viewdef('navns_fn_last'); - pg_get_viewdef ---------------------------------------------------------------------------------------------- - SELECT id, + - count(*) OVER w AS cnt + - FROM nt + - WINDOW w AS (PARTITION BY g ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a+) + - DEFINE + - a AS (rpr_navns.last(val) = '-999'::integer) ); + pg_get_viewdef +-------------------------------------------------------------------------------------------- + SELECT id, + + count(*) OVER w AS cnt + + FROM nt + + WINDOW w AS (PARTITION BY g ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS (rpr_navns.last(val) = '-999'::integer)); (1 row) DROP VIEW navns_fn_last; @@ -3023,17 +3023,17 @@ CREATE VIEW rpr_permute_v AS DEFINE PERMUTE AS val > 5, A AS val > 0 ); SELECT pg_get_viewdef('rpr_permute_v'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT count(*) OVER w AS cnt + - FROM rpr_permute + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN ("permute" (a)) + - DEFINE + - permute AS (val > 5), + - a AS (val > 0) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_permute + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN ("permute" (a)) + + DEFINE + + permute AS (val > 5), + + a AS (val > 0)); (1 row) -- Quoted even where no group follows: the deparser quotes the name wherever @@ -3048,17 +3048,17 @@ CREATE VIEW rpr_permute_v2 AS DEFINE PERMUTE AS val > 5, A AS val > 0 ); SELECT pg_get_viewdef('rpr_permute_v2'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT count(*) OVER w AS cnt + - FROM rpr_permute + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN ("permute" a) + - DEFINE + - permute AS (val > 5), + - a AS (val > 0) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_permute + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN ("permute" a) + + DEFINE + + permute AS (val > 5), + + a AS (val > 0)); (1 row) -- EXPLAIN deparses the compiled pattern with a printer of its own, so it has @@ -3118,18 +3118,18 @@ SELECT * FROM rpr_serial_v1 ORDER BY id; -- Verify deparsing SELECT pg_get_viewdef('rpr_serial_v1'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS cnt + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a+) + - DEFINE + - a AS (val > 0) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS cnt + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS (val > 0)); (1 row) -- Complex pattern with alternation @@ -3153,19 +3153,19 @@ SELECT * FROM rpr_serial_v2 ORDER BY id; (5 rows) SELECT pg_get_viewdef('rpr_serial_v2'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS cnt + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a+ | b*) + - DEFINE + - a AS (val > 20), + - b AS (val <= 20) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS cnt + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+ | b*) + + DEFINE + + a AS (val > 20), + + b AS (val <= 20)); (1 row) -- Pattern with grouping and quantifiers @@ -3192,20 +3192,20 @@ SELECT * FROM rpr_serial_v3 ORDER BY id; (5 rows) SELECT pg_get_viewdef('rpr_serial_v3'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS cnt + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN ((a b){2,5} | c*) + - DEFINE + - a AS (val > 10), + - b AS (val > 20), + - c AS (val <= 10) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS cnt + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN ((a b){2,5} | c*) + + DEFINE + + a AS (val > 10), + + b AS (val > 20), + + c AS (val <= 10)); (1 row) -- All features combined @@ -3235,21 +3235,21 @@ SELECT * FROM rpr_serial_v4 ORDER BY id; (5 rows) SELECT pg_get_viewdef('rpr_serial_v4'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS cnt + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP TO NEXT ROW + - INITIAL + - PATTERN (start (mid{1,3} | alt+) finish) + - DEFINE + - start AS (val > 5), + - mid AS ((val >= 10) AND (val <= 25)), + - alt AS (val > 25), + - finish AS (val > 15) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS cnt + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP TO NEXT ROW + + INITIAL + + PATTERN (start (mid{1,3} | alt+) finish) + + DEFINE + + start AS (val > 5), + + mid AS ((val >= 10) AND (val <= 25)), + + alt AS (val > 25), + + finish AS (val > 15)); (1 row) -- Additional quantifiers for deparsing coverage @@ -3274,19 +3274,19 @@ SELECT * FROM rpr_serial_v5 ORDER BY id; (5 rows) SELECT pg_get_viewdef('rpr_serial_v5'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS cnt + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a b?) + - DEFINE + - a AS (val > 10), + - b AS (val > 20) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS cnt + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a b?) + + DEFINE + + a AS (val > 10), + + b AS (val > 20)); (1 row) -- {n,} quantifier (n or more) @@ -3310,18 +3310,18 @@ SELECT * FROM rpr_serial_v6 ORDER BY id; (5 rows) SELECT pg_get_viewdef('rpr_serial_v6'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS cnt + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a{2,}) + - DEFINE + - a AS (val > 15) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS cnt + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a{2,}) + + DEFINE + + a AS (val > 15)); (1 row) -- {n} quantifier (exactly n) @@ -3345,18 +3345,18 @@ SELECT * FROM rpr_serial_v7 ORDER BY id; (5 rows) SELECT pg_get_viewdef('rpr_serial_v7'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS cnt + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a{3}) + - DEFINE + - a AS (val > 0) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS cnt + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a{3}) + + DEFINE + + a AS (val > 0)); (1 row) -- Nested ALT pattern (tests deparse of complex nested structure) @@ -3380,21 +3380,21 @@ SELECT * FROM rpr_serial_v8 ORDER BY id; (5 rows) SELECT pg_get_viewdef('rpr_serial_v8'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS cnt + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (((a+ b) | c) d | a b c) + - DEFINE + - a AS (val <= 15), + - b AS (val <= 25), + - c AS (val <= 30), + - d AS (val > 30) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS cnt + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (((a+ b) | c) d | a b c) + + DEFINE + + a AS (val <= 15), + + b AS (val <= 25), + + c AS (val <= 30), + + d AS (val > 30)); (1 row) -- Navigation function serialization: PREV with offset @@ -3406,19 +3406,19 @@ WINDOW w AS (ORDER BY id PATTERN (A B+) DEFINE A AS TRUE, B AS val > PREV(val, 2)); SELECT pg_get_viewdef('rpr_serial_nav1'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS count + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a b+) + - DEFINE + - a AS true, + - b AS (val > PREV(val, (2)::bigint)) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS count + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a b+) + + DEFINE + + a AS true, + + b AS (val > PREV(val, (2)::bigint))); (1 row) -- Navigation function serialization: FIRST and LAST @@ -3430,19 +3430,19 @@ WINDOW w AS (ORDER BY id PATTERN (A B+) DEFINE A AS TRUE, B AS FIRST(val) < LAST(val, 1)); SELECT pg_get_viewdef('rpr_serial_nav2'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS count + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a b+) + - DEFINE + - a AS true, + - b AS (FIRST(val) < LAST(val, (1)::bigint)) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS count + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a b+) + + DEFINE + + a AS true, + + b AS (FIRST(val) < LAST(val, (1)::bigint))); (1 row) -- Navigation function serialization: compound PREV(FIRST()) @@ -3454,19 +3454,19 @@ WINDOW w AS (ORDER BY id PATTERN (A B+) DEFINE A AS TRUE, B AS PREV(FIRST(val, 1), 2) > 0); SELECT pg_get_viewdef('rpr_serial_nav3'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS count + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a b+) + - DEFINE + - a AS true, + - b AS (PREV(FIRST(val, (1)::bigint), (2)::bigint) > 0) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS count + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a b+) + + DEFINE + + a AS true, + + b AS (PREV(FIRST(val, (1)::bigint), (2)::bigint) > 0)); (1 row) -- Navigation function serialization: compound NEXT(LAST()) @@ -3478,19 +3478,19 @@ WINDOW w AS (ORDER BY id PATTERN (A B+) DEFINE A AS TRUE, B AS NEXT(LAST(val), 2) IS NOT NULL); SELECT pg_get_viewdef('rpr_serial_nav4'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS count + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a b+) + - DEFINE + - a AS true, + - b AS (NEXT(LAST(val), (2)::bigint) IS NOT NULL) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS count + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a b+) + + DEFINE + + a AS true, + + b AS (NEXT(LAST(val), (2)::bigint) IS NOT NULL)); (1 row) -- Navigation function serialization: compound PREV(LAST()) @@ -3502,19 +3502,19 @@ WINDOW w AS (ORDER BY id PATTERN (A B+) DEFINE A AS TRUE, B AS PREV(LAST(val, 1), 2) > 0); SELECT pg_get_viewdef('rpr_serial_nav5'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS count + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a b+) + - DEFINE + - a AS true, + - b AS (PREV(LAST(val, (1)::bigint), (2)::bigint) > 0) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS count + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a b+) + + DEFINE + + a AS true, + + b AS (PREV(LAST(val, (1)::bigint), (2)::bigint) > 0)); (1 row) -- Navigation function serialization: compound NEXT(FIRST()) @@ -3526,19 +3526,19 @@ WINDOW w AS (ORDER BY id PATTERN (A B+) DEFINE A AS TRUE, B AS NEXT(FIRST(val), 3) > 0); SELECT pg_get_viewdef('rpr_serial_nav6'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS count + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a b+) + - DEFINE + - a AS true, + - b AS (NEXT(FIRST(val), (3)::bigint) > 0) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS count + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a b+) + + DEFINE + + a AS true, + + b AS (NEXT(FIRST(val), (3)::bigint) > 0)); (1 row) -- Pretty deparse: navigation calls are function-like and take no extra parens @@ -3553,19 +3553,19 @@ WINDOW w AS (ORDER BY id AND NEXT(val) > FIRST(val) AND PREV(FIRST(val)) > 0); SELECT pg_get_viewdef('rpr_nav_pretty_v'::regclass, true); - pg_get_viewdef ---------------------------------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS count + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a b+) + - DEFINE + - a AS true, + - b AS val > PREV(val) AND PREV(val) IS NOT NULL AND NEXT(val) > FIRST(val) AND PREV(FIRST(val)) > 0 ); + pg_get_viewdef +-------------------------------------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS count + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a b+) + + DEFINE + + a AS true, + + b AS val > PREV(val) AND PREV(val) IS NOT NULL AND NEXT(val) > FIRST(val) AND PREV(FIRST(val)) > 0); (1 row) -- Reluctant {1}? quantifier deparse through ruleutils @@ -3578,19 +3578,19 @@ WINDOW w AS (ORDER BY id PATTERN (A{1}? B) DEFINE A AS val > 0, B AS val > 0); SELECT pg_get_viewdef('rpr_quant_reluctant_v'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS count + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a{1}? b) + - DEFINE + - a AS (val > 0), + - b AS (val > 0) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS count + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a{1}? b) + + DEFINE + + a AS (val > 0), + + b AS (val > 0)); (1 row) -- Quoted identifier round-trip: mixed case and reserved words need quoting @@ -3602,19 +3602,19 @@ WINDOW w AS (ORDER BY id PATTERN ("Start" "Up"+) DEFINE "Start" AS TRUE, "Up" AS val > PREV(val)); SELECT pg_get_viewdef('rpr_serial_quoted'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS count + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN ("Start" "Up"+) + - DEFINE + - "Start" AS true, + - "Up" AS (val > PREV(val)) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS count + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN ("Start" "Up"+) + + DEFINE + + "Start" AS true, + + "Up" AS (val > PREV(val))); (1 row) -- Quoting the deparser adds on its own: permute is unreserved, so the stored @@ -3628,20 +3628,20 @@ WINDOW w AS (ORDER BY id PATTERN ("permute" (A | B)) DEFINE PERMUTE AS val > 0, A AS val > 10, B AS val > 20); SELECT pg_get_viewdef('rpr_serial_permute'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS count + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN ("permute" (a | b)) + - DEFINE + - permute AS (val > 0), + - a AS (val > 10), + - b AS (val > 20) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS count + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN ("permute" (a | b)) + + DEFINE + + permute AS (val > 0), + + a AS (val > 10), + + b AS (val > 20)); (1 row) -- Inline OVER round-trip: inline window spec (no WINDOW alias) deparses inside OVER (...) @@ -3653,20 +3653,123 @@ SELECT id, val, DEFINE A AS val > 10, B AS val > PREV(val)) AS cnt FROM rpr_serial; SELECT pg_get_viewdef('rpr_serial_inline_over'::regclass); - pg_get_viewdef ----------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a b+) + - DEFINE + - a AS (val > 10), + - b AS (val > PREV(val)) ) AS cnt + + pg_get_viewdef +--------------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a b+) + + DEFINE + + a AS (val > 10), + + b AS (val > PREV(val))) AS cnt + FROM rpr_serial; (1 row) +-- Multi-relation view: a DEFINE column is deparsed with no qualifier, so it +-- must stay unambiguous across the join for the view to re-parse. This one +-- is left in place like the rest of the section, which is what puts an +-- unqualified DEFINE column through the pg_dump round trip at all. +CREATE TABLE rpr_serial_j (id INT, qty INT); +INSERT INTO rpr_serial_j VALUES (1, 5), (2, 7), (3, 9), (4, 11), (5, 13); +CREATE VIEW rpr_serial_join AS +SELECT s.id, count(*) OVER w AS cnt +FROM rpr_serial s JOIN rpr_serial_j j ON s.id = j.id +WINDOW w AS (ORDER BY s.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (UP+) DEFINE UP AS val > 0); +SELECT pg_get_viewdef('rpr_serial_join'::regclass); + pg_get_viewdef +------------------------------------------------------------------------------- + SELECT s.id, + + count(*) OVER w AS cnt + + FROM (rpr_serial s + + JOIN rpr_serial_j j ON ((s.id = j.id))) + + WINDOW w AS (ORDER BY s.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (up+) + + DEFINE + + up AS (val > 0)); +(1 row) + +-- Ambiguity introduced after the view was created: ALTER TABLE adds a column +-- whose name already appears in the other side of the join, so the deparser +-- must qualify or alias it. sv3 shows the same text is rejected on a fresh +-- CREATE VIEW; sv4 shows the alias form that survives. These stay temporary +-- and are dropped at the end: sv is deliberately unrestorable, so leaving it +-- in place would hand pg_dump a view that cannot be restored. +CREATE TEMP TABLE sa (id int, price int); +CREATE TEMP TABLE sb (id int, qty int); +INSERT INTO sa VALUES (1,10),(2,20); +INSERT INTO sb VALUES (1,5),(2,7); +CREATE TEMP VIEW sv AS +SELECT a.id, count(*) OVER w AS cnt +FROM sa a JOIN sb b ON a.id = b.id +WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (UP+) DEFINE UP AS price > 0); +ALTER TABLE sb ADD COLUMN price int; +SELECT pg_get_viewdef('sv'::regclass, true); + pg_get_viewdef +------------------------------------------------------------------------------- + SELECT a.id, + + count(*) OVER w AS cnt + + FROM sa a + + JOIN sb b ON a.id = b.id + + WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (up+) + + DEFINE + + up AS price > 0); +(1 row) + +-- ERROR: the deparsed text above no longer re-parses +CREATE TEMP VIEW sv3 AS +SELECT a.id, count(*) OVER w AS cnt +FROM sa a JOIN sb b ON a.id = b.id +WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (UP+) DEFINE UP AS price > 0); +ERROR: column reference "price" is ambiguous +LINE 5: PATTERN (UP+) DEFINE UP AS price > 0); + ^ +CREATE TEMP VIEW sv4 AS +SELECT a.id, count(*) OVER w AS cnt +FROM sa a (id, price) JOIN sb b (id, qty, price_1) ON a.id = b.id +WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (UP+) DEFINE UP AS price > 0); +SELECT pg_get_viewdef('sv4'::regclass, true); + pg_get_viewdef +------------------------------------------------------------------------------- + SELECT a.id, + + count(*) OVER w AS cnt + + FROM sa a + + JOIN sb b(id, qty, price_1) ON a.id = b.id + + WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (up+) + + DEFINE + + up AS price > 0); +(1 row) + +SELECT * FROM sv4; + id | cnt +----+----- + 1 | 2 + 2 | 0 +(2 rows) + +SELECT * FROM sv; + id | cnt +----+----- + 1 | 2 + 2 | 0 +(2 rows) + +DROP VIEW sv4; +DROP VIEW sv; +DROP TABLE sa, sb; -- Materialized view (if supported) CREATE TABLE rpr_mview (id INT, val INT); INSERT INTO rpr_mview VALUES (1, 10), (2, 20), (3, 30); @@ -3688,18 +3791,18 @@ SELECT * FROM rpr_mview_v1 ORDER BY id; (3 rows) SELECT pg_get_viewdef('rpr_mview_v1'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS cnt + - FROM rpr_mview + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a+) + - DEFINE + - a AS (val > 0) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS cnt + + FROM rpr_mview + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS (val > 0)); (1 row) -- Refresh test @@ -4095,24 +4198,24 @@ SELECT * FROM rpr_multiwin_v ORDER BY id; (3 rows) SELECT pg_get_viewdef('rpr_multiwin_v'::regclass); - pg_get_viewdef -------------------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w1 AS cnt1, + - count(*) OVER w2 AS cnt2 + - FROM rpr_multiwin + - WINDOW w1 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a+) + - DEFINE + - a AS (val > 15) ), w2 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (b*) + - DEFINE + - b AS (val <= 15) ); + pg_get_viewdef +----------------------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w1 AS cnt1, + + count(*) OVER w2 AS cnt2 + + FROM rpr_multiwin + + WINDOW w1 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS (val > 15)), w2 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (b*) + + DEFINE + + b AS (val <= 15)); (1 row) -- {n} quantifier display in view @@ -4125,18 +4228,18 @@ WINDOW w AS (ORDER BY id PATTERN (A{3}) DEFINE A AS val > 0); SELECT pg_get_viewdef('rpr_quant_n_v'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS count + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a{3}) + - DEFINE + - a AS (val > 0) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS count + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a{3}) + + DEFINE + + a AS (val > 0)); (1 row) -- {n,} quantifier display in view @@ -4149,18 +4252,18 @@ WINDOW w AS (ORDER BY id PATTERN (A{2,}) DEFINE A AS val > 0); SELECT pg_get_viewdef('rpr_quant_n_plus_v'::regclass); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT id, + - val, + - count(*) OVER w AS count + - FROM rpr_serial + - WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a{2,}) + - DEFINE + - a AS (val > 0) ); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT id, + + val, + + count(*) OVER w AS count + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a{2,}) + + DEFINE + + a AS (val > 0)); (1 row) -- ============================================================ @@ -4183,14 +4286,14 @@ WINDOW w1 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTE w5 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A+?|B) DEFINE A AS val > 0, B AS val <= 0), w6 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A??|B) DEFINE A AS val > 0, B AS val <= 0); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_dp_op'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ----------------------- - PATTERN (a* | b) - PATTERN (a+ | b) - PATTERN (a? | b) - PATTERN (a*? | b) - PATTERN (a+? | b) - PATTERN (a?? | b) + line +--------------------- + PATTERN (a* | b) + PATTERN (a+ | b) + PATTERN (a? | b) + PATTERN (a*? | b) + PATTERN (a+? | b) + PATTERN (a?? | b) (6 rows) DROP VIEW rpr_dp_op; @@ -4201,10 +4304,10 @@ FROM rpr_glue WINDOW w1 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A* | B) DEFINE A AS val > 0, B AS val <= 0), w2 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A*? | B) DEFINE A AS val > 0, B AS val <= 0); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_dp_spc'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ----------------------- - PATTERN (a* | b) - PATTERN (a*? | b) + line +--------------------- + PATTERN (a* | b) + PATTERN (a*? | b) (2 rows) DROP VIEW rpr_dp_spc; @@ -4223,16 +4326,16 @@ WINDOW w1 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTE w7 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A{,3}?|B) DEFINE A AS val > 0, B AS val <= 0), w8 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A{2,3}?|B) DEFINE A AS val > 0, B AS val <= 0); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_dp_rng'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------------- - PATTERN (a{2} | b) - PATTERN (a{2,} | b) - PATTERN (a{0,3} | b) - PATTERN (a{2,3} | b) - PATTERN (a{2}? | b) - PATTERN (a{2,}? | b) - PATTERN (a{0,3}? | b) - PATTERN (a{2,3}? | b) + line +------------------------- + PATTERN (a{2} | b) + PATTERN (a{2,} | b) + PATTERN (a{0,3} | b) + PATTERN (a{2,3} | b) + PATTERN (a{2}? | b) + PATTERN (a{2,}? | b) + PATTERN (a{0,3}? | b) + PATTERN (a{2,3}? | b) (8 rows) DROP VIEW rpr_dp_rng; @@ -4244,11 +4347,11 @@ WINDOW w1 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTE w2 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A+ ?|B) DEFINE A AS val > 0, B AS val <= 0), w3 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A? ?|B) DEFINE A AS val > 0, B AS val <= 0); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_dp_mix'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ----------------------- - PATTERN (a*? | b) - PATTERN (a+? | b) - PATTERN (a?? | b) + line +--------------------- + PATTERN (a*? | b) + PATTERN (a+? | b) + PATTERN (a?? | b) (3 rows) DROP VIEW rpr_dp_mix; @@ -4265,14 +4368,14 @@ WINDOW w1 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTE w5 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A*|(B|C)) DEFINE A AS val > 0, B AS val <= 0, C AS val < 100), w6 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN ((A*|B)+) DEFINE A AS val > 0, B AS val <= 0); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_dp_struct'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------------- - PATTERN (a* | b c) - PATTERN (a* | b* | c) - PATTERN (a b* | c d) - PATTERN ((a* | b)) - PATTERN (a* | (b | c)) - PATTERN ((a* | b)+) + line +-------------------------- + PATTERN (a* | b c) + PATTERN (a* | b* | c) + PATTERN (a b* | c d) + PATTERN ((a* | b)) + PATTERN (a* | (b | c)) + PATTERN ((a* | b)+) (6 rows) DROP VIEW rpr_dp_struct; @@ -8810,4 +8913,3 @@ FROM (SELECT id, val, (2 rows) DROP TABLE rpr_plan; -RESET client_min_messages; diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out index ab7456454f1..8b2e8e5b493 100644 --- a/src/test/regress/expected/rpr_explain.out +++ b/src/test/regress/expected/rpr_explain.out @@ -122,9 +122,9 @@ WINDOW w AS ( DEFINE A AS cat = 'A', B AS cat = 'B' ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_basic_simple'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------- - PATTERN (a b) + line +----------------- + PATTERN (a b) (1 row) SELECT rpr_explain_filter(' @@ -161,9 +161,9 @@ WINDOW w AS ( DEFINE X AS cat = 'X', Y AS cat = 'Y', Z AS cat = 'Z' ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_basic_nomatch'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------- - PATTERN (x y z) + line +------------------- + PATTERN (x y z) (1 row) SELECT rpr_explain_filter(' @@ -199,9 +199,9 @@ WINDOW w AS ( DEFINE R AS TRUE ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_basic_allrows'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ----------------- - PATTERN (r) + line +--------------- + PATTERN (r) (1 row) SELECT rpr_explain_filter(' @@ -226,7 +226,7 @@ WINDOW w AS ( -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) (8 rows) --- Regression test: Space before parenthesis in pattern deparse +-- Space before parenthesis in pattern deparse -- Verifies that "A (B | C)" correctly outputs as "a (b | c)" with space CREATE VIEW rpr_ev_basic_deparse_space AS SELECT count(*) OVER w @@ -237,9 +237,9 @@ WINDOW w AS ( DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_basic_deparse_space'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------- - PATTERN (a (b | c)) + line +----------------------- + PATTERN (a (b | c)) (1 row) SELECT rpr_explain_filter(' @@ -275,9 +275,9 @@ WINDOW w AS ( DEFINE A AS v % 5 = 1, B AS v % 5 = 2, C AS v % 5 = 3, D AS v % 5 = 4, E AS v % 5 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_basic_deparse_seqalt'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------------------ - PATTERN (a ((b | c) (d | e))*) + line +---------------------------------- + PATTERN (a ((b | c) (d | e))*) (1 row) SELECT rpr_explain_filter(' @@ -301,7 +301,7 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=30.00 loops=1) (8 rows) --- Regression test: ALT branch whose tail is a group +-- ALT branch whose tail is a group -- (A | (B C)+ (D E)+) means A | ((B C)+ (D E)+) by precedence, so the two -- trailing groups must deparse inside one branch, not as separate branches. CREATE VIEW rpr_ev_basic_deparse_alttail AS @@ -313,9 +313,9 @@ WINDOW w AS ( DEFINE A AS v % 6 = 1, B AS v % 6 = 2, C AS v % 6 = 3, D AS v % 6 = 4, E AS v % 6 = 5 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_basic_deparse_alttail'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------------------- - PATTERN (a | (b c)+ (d e)+) + line +------------------------------- + PATTERN (a | (b c)+ (d e)+) (1 row) -- The viewdef above deparses the parse tree; this one deparses the compiled @@ -342,7 +342,7 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=30.00 loops=1) (9 rows) --- Regression test: Quoted identifiers in EXPLAIN pattern deparse +-- Quoted identifiers in EXPLAIN pattern deparse -- Mixed case names must be quoted to preserve round-trip safety SELECT rpr_explain_filter(' EXPLAIN (COSTS OFF) @@ -376,9 +376,9 @@ WINDOW w AS ( DEFINE A AS v % 2 = 1 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_state_simple_quant'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------ - PATTERN (a+) + line +---------------- + PATTERN (a+) (1 row) SELECT rpr_explain_filter(' @@ -416,9 +416,9 @@ WINDOW w AS ( D AS cat = 'D', E AS cat = 'E' ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_state_alt'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ----------------------------------- - PATTERN ((a | b | c) (d | e)) + line +--------------------------------- + PATTERN ((a | b | c) (d | e)) (1 row) SELECT rpr_explain_filter(' @@ -460,9 +460,9 @@ WINDOW w AS ( C AS v % 3 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_state_complex'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------ - PATTERN (a+ b* c+) + line +---------------------- + PATTERN (a+ b* c+) (1 row) SELECT rpr_explain_filter(' @@ -502,9 +502,9 @@ WINDOW w AS ( DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_state_group_quant'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------- - PATTERN ((a b)+) + line +-------------------- + PATTERN ((a b)+) (1 row) SELECT rpr_explain_filter(' @@ -541,9 +541,9 @@ WINDOW w AS ( DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_state_explosion'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------------------------------------------------------------- - PATTERN ((a | b) (a | b) (a | b) (a | b) (a | b) (a | b) (a | b) (a | b)) + line +----------------------------------------------------------------------------- + PATTERN ((a | b) (a | b) (a | b) (a | b) (a | b) (a | b) (a | b) (a | b)) (1 row) SELECT rpr_explain_filter(' @@ -581,9 +581,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_state_alt_merge_alt'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------------------------- - PATTERN ((a | b) (a | b) (c | d)) + line +------------------------------------- + PATTERN ((a | b) (a | b) (c | d)) (1 row) SELECT rpr_explain_filter(' @@ -621,9 +621,9 @@ WINDOW w AS ( DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_state_alt_merge_nonalt'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------------------- - PATTERN ((a | b) (a | b) c) + line +------------------------------- + PATTERN ((a | b) (a | b) c) (1 row) SELECT rpr_explain_filter(' @@ -660,9 +660,9 @@ WINDOW w AS ( DEFINE A AS v % 2 = 0, B AS v % 2 = 1 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_state_alt_absorb_group'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------------------------- - PATTERN ((a | b) (a | b)+ (a | b)) + line +-------------------------------------- + PATTERN ((a | b) (a | b)+ (a | b)) (1 row) SELECT rpr_explain_filter(' @@ -802,9 +802,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3, D AS v % 4 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_state_alt_plus'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------------ - PATTERN ((a | b | c)+ d) + line +---------------------------- + PATTERN ((a | b | c)+ d) (1 row) SELECT rpr_explain_filter(' @@ -842,9 +842,9 @@ WINDOW w AS ( DEFINE A AS v = 1, B AS v > 1 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_state_alt_prune'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------------- - PATTERN ((a | a b)+) + line +------------------------ + PATTERN ((a | a b)+) (1 row) SELECT rpr_explain_filter(' @@ -880,9 +880,9 @@ WINDOW w AS ( DEFINE A AS v % 3 = 1, B AS v % 3 = 2 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_state_nested_quant'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------------- - PATTERN (((a | b)+)+) + line +------------------------- + PATTERN (((a | b)+)+) (1 row) SELECT rpr_explain_filter(' @@ -920,9 +920,9 @@ WINDOW w AS ( DEFINE A AS v % 3 <> 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_nested_quant_no_flatten'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------ - PATTERN ((a{2,})*) + line +---------------------- + PATTERN ((a{2,})*) (1 row) SELECT rpr_explain_filter(' @@ -961,9 +961,9 @@ WINDOW w AS ( DEFINE A AS v % 2 = 1, B AS v % 3 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_state_dedup'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------------- - PATTERN ((a | b){2,4}) + line +-------------------------- + PATTERN ((a | b){2,4}) (1 row) SELECT rpr_explain_filter(' @@ -1003,9 +1003,9 @@ WINDOW w AS ( DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_absorb_unbounded'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -1043,9 +1043,9 @@ WINDOW w AS ( DEFINE A AS v > 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_absorb_plus'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------ - PATTERN (a+) + line +---------------- + PATTERN (a+) (1 row) SELECT rpr_explain_filter(' @@ -1083,9 +1083,9 @@ WINDOW w AS ( DEFINE A AS v > 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_absorb_star'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------ - PATTERN (a*) + line +---------------- + PATTERN (a*) (1 row) SELECT rpr_explain_filter(' @@ -1122,9 +1122,9 @@ WINDOW w AS ( DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_no_absorb'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------ - PATTERN (a{2,4} b) + line +---------------------- + PATTERN (a{2,4} b) (1 row) SELECT rpr_explain_filter(' @@ -1161,9 +1161,9 @@ WINDOW w AS ( DEFINE A AS v % 10 = 1, B AS v % 10 = 2, C AS v % 10 = 3 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_skip'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------- - PATTERN (a b c) + line +------------------- + PATTERN (a b c) (1 row) SELECT rpr_explain_filter(' @@ -1200,9 +1200,9 @@ WINDOW w AS ( DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_absorb_group'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------ - PATTERN ((a b)+ c) + line +---------------------- + PATTERN ((a b)+ c) (1 row) SELECT rpr_explain_filter(' @@ -1241,9 +1241,9 @@ WINDOW w AS ( DEFINE A AS v % 7 IN (1, 4), B AS v % 7 IN (2, 3, 5, 6), C AS v % 7 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_absorb_fixedvar'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------------- - PATTERN ((a b b)+ c) + line +------------------------ + PATTERN ((a b b)+ c) (1 row) SELECT rpr_explain_filter(' @@ -1283,9 +1283,9 @@ WINDOW w AS ( E AS v % 13 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_absorb_nested'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------------------- - PATTERN ((a (b c){2} d)+ e) + line +------------------------------- + PATTERN ((a (b c){2} d)+ e) (1 row) SELECT rpr_explain_filter(' @@ -1331,9 +1331,9 @@ WINDOW w AS ( F AS v % 41 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_absorb_deep'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------------------------------- - PATTERN ((a ((b c c c){2} d){2} e)+ f) + line +------------------------------------------ + PATTERN ((a ((b c c c){2} d){2} e)+ f) (1 row) SELECT rpr_explain_filter(' @@ -1380,9 +1380,9 @@ WINDOW w AS ( C AS v % 21 IN (3, 5, 8, 10, 13, 15, 18, 20) ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_absorb_endchain'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------------------- - PATTERN (((a (b c){2}){2})+) + line +-------------------------------- + PATTERN (((a (b c){2}){2})+) (1 row) SELECT rpr_explain_filter(' @@ -1423,9 +1423,9 @@ WINDOW w AS ( DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 AND v > FIRST(v) ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_no_absorb_first'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -1466,9 +1466,9 @@ WINDOW w AS ( DEFINE A AS v % 5 <> 0, B AS LAST(v) % 5 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_absorb_last'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -1542,9 +1542,9 @@ WINDOW w AS ( DEFINE A AS v % 10 NOT IN (0, 9), B AS v % 10 = 9, C AS v % 10 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_absorb_group_greedy'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------- - PATTERN ((a+ b)+ c) + line +----------------------- + PATTERN ((a+ b)+ c) (1 row) SELECT rpr_explain_filter(' @@ -1583,9 +1583,9 @@ WINDOW w AS ( DEFINE A AS v % 10 NOT IN (0, 9), B AS v % 10 = 9, C AS v % 10 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_no_absorb_reluctant_group'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------------- - PATTERN ((a+ b)+? c) + line +------------------------ + PATTERN ((a+ b)+? c) (1 row) SELECT rpr_explain_filter(' @@ -1625,9 +1625,9 @@ WINDOW w AS ( DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_absorb_fixed_greedy'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------- - PATTERN ((a+ b){2}) + line +----------------------- + PATTERN ((a+ b){2}) (1 row) SELECT rpr_explain_filter(' @@ -1663,9 +1663,9 @@ WINDOW w AS ( DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_absorb_fixed_reluctant'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------------- - PATTERN ((a+ b){2}?) + line +------------------------ + PATTERN ((a+ b){2}?) (1 row) SELECT rpr_explain_filter(' @@ -1708,9 +1708,9 @@ WINDOW w AS ( DEFINE A AS 'A' = ANY(flags), B AS 'B' = ANY(flags), C AS 'C' = ANY(flags) ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_absorb_alt_nonabsorb'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------ - PATTERN (a+ b | c) + line +---------------------- + PATTERN (a+ b | c) (1 row) SELECT rpr_explain_filter(' @@ -1761,9 +1761,9 @@ WINDOW w AS ( DEFINE A AS 'A' = ANY(flags), B AS 'B' = ANY(flags), C AS 'C' = ANY(flags) ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_absorb_alt_both'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------- - PATTERN (a+ c | b+) + line +----------------------- + PATTERN (a+ c | b+) (1 row) SELECT rpr_explain_filter(' @@ -1813,9 +1813,9 @@ WINDOW w AS ( D AS cat = 'D', E AS cat = 'E' ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_mlen_fixed'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------- - PATTERN (a b c d e) + line +----------------------- + PATTERN (a b c d e) (1 row) SELECT rpr_explain_filter(' @@ -1854,9 +1854,9 @@ WINDOW w AS ( DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_mlen_variable'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -1893,9 +1893,9 @@ WINDOW w AS ( DEFINE A AS v <= 195, B AS v > 195 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_mlen_long'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -1934,9 +1934,9 @@ WINDOW w AS ( B AS v % 20 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_mlen_with_mismatch'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -1986,9 +1986,9 @@ WINDOW w AS ( DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C' ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_mlen_no_mismatch'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ----------------------- - PATTERN (a+ b+ c) + line +--------------------- + PATTERN (a+ b+ c) (1 row) SELECT rpr_explain_filter(' @@ -2044,9 +2044,9 @@ WINDOW w AS ( DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C' ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_mlen_long_partial'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ----------------------- - PATTERN (a+ b+ c) + line +--------------------- + PATTERN (a+ b+ c) (1 row) SELECT rpr_explain_filter(' @@ -2098,9 +2098,9 @@ WINDOW w AS ( DEFINE A AS v % 3 = 1, B AS v % 3 = 2 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_json_basic'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------- - PATTERN (a+ b+) + line +------------------- + PATTERN (a+ b+) (1 row) SELECT rpr_explain_filter(' @@ -2175,9 +2175,9 @@ WINDOW w AS ( DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_json_matchlen'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -2256,9 +2256,9 @@ WINDOW w AS ( DEFINE A AS v = 1, B AS v = 2, C AS v = 3 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_json_mismatch'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------- - PATTERN (a b c) + line +------------------- + PATTERN (a b c) (1 row) SELECT rpr_explain_filter(' @@ -2336,9 +2336,9 @@ WINDOW w AS ( DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_json_skip'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------------------------------------------------------------- - PATTERN ((a | b) (a | b) (a | b) (a | b) (a | b) (a | b) (a | b) (a | b)) + line +----------------------------------------------------------------------------- + PATTERN ((a | b) (a | b) (a | b) (a | b) (a | b) (a | b) (a | b) (a | b)) (1 row) SELECT rpr_explain_filter(' @@ -2419,9 +2419,9 @@ WINDOW w AS ( DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_xml_basic'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------- - PATTERN (a b) + line +----------------- + PATTERN (a b) (1 row) SELECT rpr_explain_filter(' @@ -2498,9 +2498,9 @@ WINDOW w AS ( DEFINE A AS v % 10 <> 0 AND PREV(v) IS NOT NULL, B AS v % 10 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_xml_absorb'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------- - PATTERN (a+ b+) + line +------------------- + PATTERN (a+ b+) (1 row) SELECT rpr_explain_filter(' @@ -2580,9 +2580,9 @@ WINDOW w AS ( DEFINE A AS v % 2 = 1, B AS v % 3 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_xml_mismatch'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------------- - PATTERN ((a | b){2,4}) + line +-------------------------- + PATTERN ((a | b){2,4}) (1 row) SELECT rpr_explain_filter(' @@ -2668,9 +2668,9 @@ WINDOW w AS ( DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_part_multi'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -2723,9 +2723,9 @@ WINDOW w AS ( DEFINE A AS val < 5, B AS val >= 5 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_part_diff'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -2774,9 +2774,9 @@ WINDOW w AS ( DEFINE A AS v = 1, B AS v = 2 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------- - PATTERN (a b) + line +----------------- + PATTERN (a b) (1 row) SELECT rpr_explain_filter(' @@ -2813,9 +2813,9 @@ WINDOW w AS ( DEFINE A AS FALSE ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min0'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------- - PATTERN ((a?){0,3}) + line +----------------------- + PATTERN ((a?){0,3}) (1 row) SELECT rpr_explain_filter(' @@ -2851,9 +2851,9 @@ WINDOW w AS ( DEFINE A AS FALSE ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min1'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------- - PATTERN ((a?){1,3}) + line +----------------------- + PATTERN ((a?){1,3}) (1 row) SELECT rpr_explain_filter(' @@ -2889,9 +2889,9 @@ WINDOW w AS ( DEFINE A AS FALSE ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min2'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------- - PATTERN ((a?){2,3}) + line +----------------------- + PATTERN ((a?){2,3}) (1 row) SELECT rpr_explain_filter(' @@ -2927,9 +2927,9 @@ WINDOW w AS ( DEFINE A AS v <= 2 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_mixed'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------- - PATTERN ((a?){2,3}) + line +----------------------- + PATTERN ((a?){2,3}) (1 row) SELECT rpr_explain_filter(' @@ -2965,9 +2965,9 @@ WINDOW w AS ( DEFINE A AS FALSE, B AS FALSE ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_multi'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------------- - PATTERN ((a? b?){2,3}) + line +-------------------------- + PATTERN ((a? b?){2,3}) (1 row) SELECT rpr_explain_filter(' @@ -3003,9 +3003,9 @@ WINDOW w AS ( DEFINE A AS TRUE ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_single_row'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ----------------- - PATTERN (a) + line +--------------- + PATTERN (a) (1 row) SELECT rpr_explain_filter(' @@ -3043,9 +3043,9 @@ WINDOW w AS ( F AS v = 6, G AS v = 7, H AS v = 8, I AS v = 9, J AS v = 10 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_pattern_longer'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ----------------------------------- - PATTERN (a b c d e f g h i j) + line +--------------------------------- + PATTERN (a b c d e f g h i j) (1 row) SELECT rpr_explain_filter(' @@ -3083,9 +3083,9 @@ WINDOW w AS ( DEFINE A AS TRUE ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_single_match'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------ - PATTERN (a+) + line +---------------- + PATTERN (a+) (1 row) SELECT rpr_explain_filter(' @@ -3125,9 +3125,9 @@ WINDOW w AS ( DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_cpx_nested'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------------- - PATTERN (((a b) c)+) + line +------------------------ + PATTERN (((a b) c)+) (1 row) SELECT rpr_explain_filter(' @@ -3166,9 +3166,9 @@ WINDOW w AS ( D AS cat = 'D', E AS cat = 'E' ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_cpx_multi_alt'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ----------------------------------- - PATTERN ((a | b) (c | d | e)) + line +--------------------------------- + PATTERN ((a | b) (c | d | e)) (1 row) SELECT rpr_explain_filter(' @@ -3207,9 +3207,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_cpx_optional'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------- - PATTERN (a b? c) + line +-------------------- + PATTERN (a b? c) (1 row) SELECT rpr_explain_filter(' @@ -3246,9 +3246,9 @@ WINDOW w AS ( DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_cpx_bounded'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------ - PATTERN (a{2,5} b) + line +---------------------- + PATTERN (a{2,5} b) (1 row) SELECT rpr_explain_filter(' @@ -3285,9 +3285,9 @@ WINDOW w AS ( DEFINE A AS v % 10 = 1, B AS v % 10 IN (2,3,4,5,6,7,8), C AS v % 10 = 9 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_cpx_star'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------- - PATTERN (a b* c) + line +-------------------- + PATTERN (a b* c) (1 row) SELECT rpr_explain_filter(' @@ -3327,9 +3327,9 @@ WINDOW w AS ( DEFINE D AS trend = 'D', U AS trend = 'U' ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_real_vshape'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------- - PATTERN (d+ u+) + line +------------------- + PATTERN (d+ u+) (1 row) SELECT rpr_explain_filter(' @@ -3366,9 +3366,9 @@ WINDOW w AS ( DEFINE U AS trend = 'U', S AS trend = 'S', D AS trend = 'D' ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_real_peak'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------ - PATTERN (u+ s* d+) + line +---------------------- + PATTERN (u+ s* d+) (1 row) SELECT rpr_explain_filter(' @@ -3405,9 +3405,9 @@ WINDOW w AS ( DEFINE A AS v > PREV(v) OR PREV(v) IS NULL ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_real_increasing'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------- - PATTERN (a{3,}) + line +------------------- + PATTERN (a{3,}) (1 row) SELECT rpr_explain_filter(' @@ -3448,9 +3448,9 @@ WINDOW w AS ( DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_perf_large_simple'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------- - PATTERN (a b) + line +----------------- + PATTERN (a b) (1 row) SELECT rpr_explain_filter(' @@ -3487,9 +3487,9 @@ WINDOW w AS ( DEFINE A AS v % 100 <> 0, B AS v % 100 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_perf_large_absorb'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -3526,9 +3526,9 @@ WINDOW w AS ( DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_perf_high_merge'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------------- - PATTERN ((a | b)+ c) + line +------------------------ + PATTERN ((a | b)+ c) (1 row) SELECT rpr_explain_filter(' @@ -3569,9 +3569,9 @@ WINDOW w AS ( DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_initial_with'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -3609,9 +3609,9 @@ WINDOW w AS ( DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_initial_without'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -3651,9 +3651,9 @@ WINDOW w AS ( DEFINE A AS v % 4 <> 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_quant_plus'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------ - PATTERN (a+) + line +---------------- + PATTERN (a+) (1 row) SELECT rpr_explain_filter(' @@ -3690,9 +3690,9 @@ WINDOW w AS ( DEFINE A AS v % 4 IN (1, 2), B AS v % 4 = 3 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_quant_star'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a* b) + line +------------------ + PATTERN (a* b) (1 row) SELECT rpr_explain_filter(' @@ -3729,9 +3729,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_quant_question'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------- - PATTERN (a? b c) + line +-------------------- + PATTERN (a? b c) (1 row) SELECT rpr_explain_filter(' @@ -3768,9 +3768,9 @@ WINDOW w AS ( DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_quant_exact'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------- - PATTERN (a{3} b) + line +-------------------- + PATTERN (a{3} b) (1 row) SELECT rpr_explain_filter(' @@ -3807,9 +3807,9 @@ WINDOW w AS ( DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_quant_range'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------ - PATTERN (a{2,4} b) + line +---------------------- + PATTERN (a{2,4} b) (1 row) SELECT rpr_explain_filter(' @@ -3846,9 +3846,9 @@ WINDOW w AS ( DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_quant_atleast'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ----------------------- - PATTERN (a{3,} b) + line +--------------------- + PATTERN (a{3,} b) (1 row) SELECT rpr_explain_filter(' @@ -3889,9 +3889,9 @@ WINDOW w AS ( DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_reg_state_count'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -3928,9 +3928,9 @@ WINDOW w AS ( DEFINE A AS v % 10 IN (1,2,3,4,5,6,7), B AS v % 10 = 8, C AS v % 10 = 9 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_reg_ctx_absorb'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------- - PATTERN (a+ b c) + line +-------------------- + PATTERN (a+ b c) (1 row) SELECT rpr_explain_filter(' @@ -3967,9 +3967,9 @@ WINDOW w AS ( DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_reg_matchlen'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------- - PATTERN (a b c) + line +------------------- + PATTERN (a b c) (1 row) SELECT rpr_explain_filter(' @@ -4009,9 +4009,9 @@ WINDOW w AS ( DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C' ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_simple'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------- - PATTERN ((a | b) c) + line +----------------------- + PATTERN ((a | b) c) (1 row) SELECT rpr_explain_filter(' @@ -4050,9 +4050,9 @@ WINDOW w AS ( D AS cat = 'D', E AS cat = 'E' ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_multi_item'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------------------- - PATTERN ((a | b | c | d) e) + line +------------------------------- + PATTERN ((a | b | c | d) e) (1 row) SELECT rpr_explain_filter(' @@ -4091,9 +4091,9 @@ WINDOW w AS ( DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_with_quant'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------------- - PATTERN ((a | b)+ c) + line +------------------------ + PATTERN ((a | b)+ c) (1 row) SELECT rpr_explain_filter(' @@ -4129,9 +4129,9 @@ WINDOW w AS ( DEFINE A AS v % 5 = 0, B AS v % 5 = 1, C AS v % 5 = 2, D AS v % 5 = 3, E AS v % 5 = 4 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_four_plus'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------------------- - PATTERN (a | b | c | d | e) + line +------------------------------- + PATTERN (a | b | c | d | e) (1 row) SELECT rpr_explain_filter(' @@ -4165,9 +4165,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_at_start'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------------- - PATTERN ((a | b) c d) + line +------------------------- + PATTERN ((a | b) c d) (1 row) SELECT rpr_explain_filter(' @@ -4202,9 +4202,9 @@ WINDOW w AS ( DEFINE A AS v % 6 = 0, B AS v % 6 = 1, C AS v % 6 = 2, D AS v % 6 = 3, E AS v % 6 = 4, F AS v % 6 = 5 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_sequential'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ----------------------------------- - PATTERN ((a | b) c (d | e) f) + line +--------------------------------- + PATTERN ((a | b) c (d | e) f) (1 row) SELECT rpr_explain_filter(' @@ -4238,9 +4238,9 @@ WINDOW w AS ( DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_quantified'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------------- - PATTERN ((a+ | b+) c) + line +------------------------- + PATTERN ((a+ | b+) c) (1 row) SELECT rpr_explain_filter(' @@ -4275,9 +4275,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_at_end'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------------- - PATTERN (a b (c | d)) + line +------------------------- + PATTERN (a b (c | d)) (1 row) SELECT rpr_explain_filter(' @@ -4313,9 +4313,9 @@ WINDOW w AS ( DEFINE A AS v % 5 = 0, B AS v % 5 = 1, C AS v % 5 = 2, D AS v % 5 = 3, E AS v % 5 = 4 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_nested_start'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------------------- - PATTERN (a ((b | c) d | e)) + line +------------------------------- + PATTERN (a ((b | c) d | e)) (1 row) SELECT rpr_explain_filter(' @@ -4350,9 +4350,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_nested_end'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ----------------------------- - PATTERN (c (a | b) | d) + line +--------------------------- + PATTERN (c (a | b) | d) (1 row) SELECT rpr_explain_filter(' @@ -4387,9 +4387,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_grp_first'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------------- - PATTERN ((a b)+ | c) + line +------------------------ + PATTERN ((a b)+ | c) (1 row) SELECT rpr_explain_filter(' @@ -4425,9 +4425,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_grp_last'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------------- - PATTERN (c | (a b)+) + line +------------------------ + PATTERN (c | (a b)+) (1 row) SELECT rpr_explain_filter(' @@ -4463,9 +4463,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_grp_mid'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------------ - PATTERN (c | (a b)+ | d) + line +---------------------------- + PATTERN (c | (a b)+ | d) (1 row) SELECT rpr_explain_filter(' @@ -4501,9 +4501,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_grp_first3'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------------ - PATTERN ((a b)+ | c | d) + line +---------------------------- + PATTERN ((a b)+ | c | d) (1 row) SELECT rpr_explain_filter(' @@ -4539,9 +4539,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_grp_bounded'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------------- - PATTERN ((a b){2} | c) + line +-------------------------- + PATTERN ((a b){2} | c) (1 row) SELECT rpr_explain_filter(' @@ -4576,9 +4576,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_grp_both'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------------- - PATTERN ((a b)+ | (c d)+) + line +----------------------------- + PATTERN ((a b)+ | (c d)+) (1 row) SELECT rpr_explain_filter(' @@ -4614,9 +4614,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_grp_seq_head'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------------ - PATTERN (((a b)+ | c) d) + line +---------------------------- + PATTERN (((a b)+ | c) d) (1 row) SELECT rpr_explain_filter(' @@ -4652,9 +4652,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_grp_seq_tail'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------------ - PATTERN ((c | (a b)+) d) + line +---------------------------- + PATTERN ((c | (a b)+) d) (1 row) SELECT rpr_explain_filter(' @@ -4690,9 +4690,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_grp_quant'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------------- - PATTERN (((a b){2} | c)+) + line +----------------------------- + PATTERN (((a b){2} | c)+) (1 row) SELECT rpr_explain_filter(' @@ -4728,9 +4728,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_grp_unit'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------- - PATTERN ((a b) | c) + line +----------------------- + PATTERN ((a b) | c) (1 row) SELECT rpr_explain_filter(' @@ -4767,9 +4767,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, C AS v % 4 = 2 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_var_first'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------- - PATTERN (a+ | c) + line +-------------------- + PATTERN (a+ | c) (1 row) SELECT rpr_explain_filter(' @@ -4804,9 +4804,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_grp_last3'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------------ - PATTERN (c | d | (a b)+) + line +---------------------------- + PATTERN (c | d | (a b)+) (1 row) SELECT rpr_explain_filter(' @@ -4842,9 +4842,9 @@ WINDOW w AS ( DEFINE A AS v % 5 = 0, B AS v % 5 = 1, D AS v % 5 = 2, E AS v % 5 = 3 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_inner_bounded'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ----------------------------- - PATTERN (d (a | b) | e) + line +--------------------------- + PATTERN (d (a | b) | e) (1 row) SELECT rpr_explain_filter(' @@ -4879,9 +4879,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_grp_then_seq'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------------- - PATTERN (c | (a b)+ d) + line +-------------------------- + PATTERN (c | (a b)+ d) (1 row) SELECT rpr_explain_filter(' @@ -4916,9 +4916,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_grp_lone_alt'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------ - PATTERN ((a | b)+) + line +---------------------- + PATTERN ((a | b)+) (1 row) SELECT rpr_explain_filter(' @@ -4954,9 +4954,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_grp_seq_alt'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------------- - PATTERN ((a (b | c))+) + line +-------------------------- + PATTERN ((a (b | c))+) (1 row) SELECT rpr_explain_filter(' @@ -4992,9 +4992,9 @@ WINDOW w AS ( DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_grp_alt_seq'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------------- - PATTERN (((a | b) c)+) + line +-------------------------- + PATTERN (((a | b) c)+) (1 row) SELECT rpr_explain_filter(' @@ -5032,9 +5032,9 @@ WINDOW w AS ( D AS v % 6 = 3, E AS v % 6 = 4, F AS v % 6 = 5 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_stack3'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------------------- - PATTERN (((a | b) c | d) e | f) + line +----------------------------------- + PATTERN (((a | b) c | d) e | f) (1 row) SELECT rpr_explain_filter(' @@ -5072,9 +5072,9 @@ WINDOW w AS ( E AS v % 8 = 4, F AS v % 8 = 5, G AS v % 8 = 6, H AS v % 8 = 7 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_stack4'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------------------------------- - PATTERN ((((a | b) c | d) e | f) g | h) + line +------------------------------------------- + PATTERN ((((a | b) c | d) e | f) g | h) (1 row) SELECT rpr_explain_filter(' @@ -5113,9 +5113,9 @@ WINDOW w AS ( D AS v % 6 = 3, E AS v % 6 = 4, F AS v % 6 = 5 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_stack3_grp'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------------------------- - PATTERN (((a | b)+ c | d) e | f) + line +------------------------------------ + PATTERN (((a | b)+ c | d) e | f) (1 row) SELECT rpr_explain_filter(' @@ -5154,9 +5154,9 @@ WINDOW w AS ( D AS v % 6 = 3, E AS v % 6 = 4, F AS v % 6 = 5 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_tail3'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------------------- - PATTERN (a (b (c | d) | e) | f) + line +----------------------------------- + PATTERN (a (b (c | d) | e) | f) (1 row) SELECT rpr_explain_filter(' @@ -5194,9 +5194,9 @@ WINDOW w AS ( E AS v % 8 = 4, F AS v % 8 = 5, G AS v % 8 = 6, H AS v % 8 = 7 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_tail4'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------------------------------- - PATTERN (a (b (c (d | e) | f) | g) | h) + line +------------------------------------------- + PATTERN (a (b (c (d | e) | f) | g) | h) (1 row) SELECT rpr_explain_filter(' @@ -5235,9 +5235,9 @@ WINDOW w AS ( E AS v % 8 = 4, F AS v % 8 = 5, G AS v % 8 = 6, H AS v % 8 = 7 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_tail_seqbranch'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ----------------------------------------------- - PATTERN (a (b (c (d | e) | f) | g a) | h) + line +--------------------------------------------- + PATTERN (a (b (c (d | e) | f) | g a) | h) (1 row) SELECT rpr_explain_filter(' @@ -5276,9 +5276,9 @@ WINDOW w AS ( E AS v % 8 = 4, F AS v % 8 = 5, G AS v % 8 = 6, H AS v % 8 = 7 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_alt_mid_seqtail'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------------------------------- - PATTERN ((a (b (c | d) | e) | f) g | h) + line +------------------------------------------- + PATTERN ((a (b (c | d) | e) | f) g | h) (1 row) SELECT rpr_explain_filter(' @@ -5318,9 +5318,9 @@ WINDOW w AS ( DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_grp_simple'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------- - PATTERN ((a b)+) + line +-------------------- + PATTERN ((a b)+) (1 row) SELECT rpr_explain_filter(' @@ -5357,9 +5357,9 @@ WINDOW w AS ( DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_grp_bounded'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------------- - PATTERN ((a b){2,4}) + line +------------------------ + PATTERN ((a b){2,4}) (1 row) SELECT rpr_explain_filter(' @@ -5396,9 +5396,9 @@ WINDOW w AS ( DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_grp_nested'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------------- - PATTERN (((a b){2})+) + line +------------------------- + PATTERN (((a b){2})+) (1 row) SELECT rpr_explain_filter(' @@ -5434,9 +5434,9 @@ WINDOW w AS ( DEFINE A AS v % 2 = 0, B AS v % 2 = 1 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_grp_deep'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------------ - PATTERN ((((a | b)+)+)+) + line +---------------------------- + PATTERN ((((a | b)+)+)+) (1 row) SELECT rpr_explain_filter(' @@ -5471,9 +5471,9 @@ WINDOW w AS ( DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_grp_bounded_alt'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------------ - PATTERN ((a | b){2,3} c) + line +---------------------------- + PATTERN ((a | b){2,3} c) (1 row) SELECT rpr_explain_filter(' @@ -5508,9 +5508,9 @@ WINDOW w AS ( DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_grp_nested_quant'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------------- - PATTERN (((a b)+ c)*) + line +------------------------- + PATTERN (((a b)+ c)*) (1 row) SELECT rpr_explain_filter(' @@ -5545,9 +5545,9 @@ WINDOW w AS ( DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_grp_partial_quant'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line --------------------------- - PATTERN ((a (b c)+)*) + line +------------------------- + PATTERN ((a (b c)+)*) (1 row) SELECT rpr_explain_filter(' @@ -5586,9 +5586,9 @@ WINDOW w AS ( DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_wfn_count'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -5625,9 +5625,9 @@ WINDOW w AS ( DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_wfn_first_value'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -5664,9 +5664,9 @@ WINDOW w AS ( DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_wfn_last_value'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -5706,9 +5706,9 @@ WINDOW w AS ( DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_wfn_multi'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -5753,9 +5753,9 @@ WINDOW w AS ( B AS (v % 5 = 0) OR (v % 3 = 0) ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_def_complex_bool'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -5797,9 +5797,9 @@ WINDOW w AS ( D AS v < PREV(v) ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_def_prev'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ----------------------- - PATTERN (s u+ d+) + line +--------------------- + PATTERN (s u+ d+) (1 row) SELECT rpr_explain_filter(' @@ -5841,11 +5841,11 @@ WINDOW w AS ( B AS v > PREV(v) ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_nav_prev1'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT'; - line -------------------------- - PATTERN (a b+) + line +------------------------ + PATTERN (a b+) DEFINE - b AS (v > PREV(v)) ); + b AS (v > PREV(v))); (3 rows) -- Using 1-arg NEXT (implicit offset 1) @@ -5861,11 +5861,11 @@ WINDOW w AS ( B AS v < NEXT(v) ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_nav_next1'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT'; - line -------------------------- - PATTERN (a b+) + line +------------------------ + PATTERN (a b+) DEFINE - b AS (v < NEXT(v)) ); + b AS (v < NEXT(v))); (3 rows) -- Using 2-arg PREV (explicit offset) @@ -5881,11 +5881,11 @@ WINDOW w AS ( B AS v > PREV(v, 2) ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_nav_prev2'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT'; - line --------------------------------------- - PATTERN (a b+) + line +------------------------------------- + PATTERN (a b+) DEFINE - b AS (v > PREV(v, (2)::bigint)) ); + b AS (v > PREV(v, (2)::bigint))); (3 rows) -- Using 2-arg NEXT (explicit offset) @@ -5901,11 +5901,11 @@ WINDOW w AS ( B AS v < NEXT(v, 2) ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_nav_next2'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT'; - line --------------------------------------- - PATTERN (a b+) + line +------------------------------------- + PATTERN (a b+) DEFINE - b AS (v < NEXT(v, (2)::bigint)) ); + b AS (v < NEXT(v, (2)::bigint))); (3 rows) -- Using NULL comparisons @@ -5922,9 +5922,9 @@ WINDOW w AS ( DEFINE A AS v IS NOT NULL, B AS v IS NULL ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_def_null'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line -------------------- - PATTERN (a+ b) + line +------------------ + PATTERN (a+ b) (1 row) SELECT rpr_explain_filter(' @@ -5967,9 +5967,9 @@ WINDOW w AS ( DEFINE A AS v % 10 < 7, B AS v % 10 = 7, C AS v % 10 = 8 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_scale_500rows'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ---------------------- - PATTERN (a+ b c) + line +-------------------- + PATTERN (a+ b c) (1 row) SELECT rpr_explain_filter(' @@ -6006,9 +6006,9 @@ WINDOW w AS ( DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_scale_high_match'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------- - PATTERN (a b) + line +----------------- + PATTERN (a b) (1 row) SELECT rpr_explain_filter(' @@ -6050,9 +6050,9 @@ WINDOW w AS ( E AS v % 100 = 5 ); SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_scale_high_skip'), E'\n')) AS line WHERE line ~ 'PATTERN'; - line ------------------------- - PATTERN (a b c d e) + line +----------------------- + PATTERN (a b c d e) (1 row) SELECT rpr_explain_filter(' diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index e5da7b92ecf..361d0923c2a 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -2363,6 +2363,62 @@ SELECT id, val, FROM rpr_serial; SELECT pg_get_viewdef('rpr_serial_inline_over'::regclass); +-- Multi-relation view: a DEFINE column is deparsed with no qualifier, so it +-- must stay unambiguous across the join for the view to re-parse. This one +-- is left in place like the rest of the section, which is what puts an +-- unqualified DEFINE column through the pg_dump round trip at all. +CREATE TABLE rpr_serial_j (id INT, qty INT); +INSERT INTO rpr_serial_j VALUES (1, 5), (2, 7), (3, 9), (4, 11), (5, 13); + +CREATE VIEW rpr_serial_join AS +SELECT s.id, count(*) OVER w AS cnt +FROM rpr_serial s JOIN rpr_serial_j j ON s.id = j.id +WINDOW w AS (ORDER BY s.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (UP+) DEFINE UP AS val > 0); +SELECT pg_get_viewdef('rpr_serial_join'::regclass); + +-- Ambiguity introduced after the view was created: ALTER TABLE adds a column +-- whose name already appears in the other side of the join, so the deparser +-- must qualify or alias it. sv3 shows the same text is rejected on a fresh +-- CREATE VIEW; sv4 shows the alias form that survives. These stay temporary +-- and are dropped at the end: sv is deliberately unrestorable, so leaving it +-- in place would hand pg_dump a view that cannot be restored. +CREATE TEMP TABLE sa (id int, price int); +CREATE TEMP TABLE sb (id int, qty int); +INSERT INTO sa VALUES (1,10),(2,20); +INSERT INTO sb VALUES (1,5),(2,7); + +CREATE TEMP VIEW sv AS +SELECT a.id, count(*) OVER w AS cnt +FROM sa a JOIN sb b ON a.id = b.id +WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (UP+) DEFINE UP AS price > 0); + +ALTER TABLE sb ADD COLUMN price int; + +SELECT pg_get_viewdef('sv'::regclass, true); + +-- ERROR: the deparsed text above no longer re-parses +CREATE TEMP VIEW sv3 AS +SELECT a.id, count(*) OVER w AS cnt +FROM sa a JOIN sb b ON a.id = b.id +WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (UP+) DEFINE UP AS price > 0); + +CREATE TEMP VIEW sv4 AS +SELECT a.id, count(*) OVER w AS cnt +FROM sa a (id, price) JOIN sb b (id, qty, price_1) ON a.id = b.id +WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (UP+) DEFINE UP AS price > 0); + +SELECT pg_get_viewdef('sv4'::regclass, true); +SELECT * FROM sv4; +SELECT * FROM sv; + +DROP VIEW sv4; +DROP VIEW sv; +DROP TABLE sa, sb; + -- Materialized view (if supported) CREATE TABLE rpr_mview (id INT, val INT); @@ -5366,4 +5422,3 @@ FROM (SELECT id, val, ) s; DROP TABLE rpr_plan; -RESET client_min_messages; diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql index 4b1a76ac278..ccee047adc9 100644 --- a/src/test/regress/sql/rpr_explain.sql +++ b/src/test/regress/sql/rpr_explain.sql @@ -184,7 +184,7 @@ WINDOW w AS ( DEFINE R AS TRUE );'); --- Regression test: Space before parenthesis in pattern deparse +-- Space before parenthesis in pattern deparse -- Verifies that "A (B | C)" correctly outputs as "a (b | c)" with space CREATE VIEW rpr_ev_basic_deparse_space AS SELECT count(*) OVER w @@ -226,7 +226,7 @@ WINDOW w AS ( DEFINE A AS v % 5 = 1, B AS v % 5 = 2, C AS v % 5 = 3, D AS v % 5 = 4, E AS v % 5 = 0 );'); --- Regression test: ALT branch whose tail is a group +-- ALT branch whose tail is a group -- (A | (B C)+ (D E)+) means A | ((B C)+ (D E)+) by precedence, so the two -- trailing groups must deparse inside one branch, not as separate branches. CREATE VIEW rpr_ev_basic_deparse_alttail AS @@ -250,7 +250,7 @@ WINDOW w AS ( DEFINE A AS v % 6 = 1, B AS v % 6 = 2, C AS v % 6 = 3, D AS v % 6 = 4, E AS v % 6 = 5 );'); --- Regression test: Quoted identifiers in EXPLAIN pattern deparse +-- Quoted identifiers in EXPLAIN pattern deparse -- Mixed case names must be quoted to preserve round-trip safety SELECT rpr_explain_filter(' EXPLAIN (COSTS OFF)