From b3c04cbb30e21c431e32713f28547c17e231cab9 Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Mon, 28 Sep 2026 15:53:21 +0900 Subject: [PATCH 05/10] Make a deparsed DEFINE clause re-parse as written This commit makes the deparsed text of a view or rule holding a DEFINE clause re-parse to the same query, and in doing so fixes a few deparsed forms of queries without row pattern recognition as well. 1. The problem A DEFINE clause can name a column only without a qualifier, since the qualifier slot belongs to the pattern variable, and get_rule_define() prints it with varprefix off. So the bare name has to resolve exactly as printed when a view or rule is re-parsed, and nothing ensured that. Once another column of the same query level came to carry that name, the pg_get_viewdef() output failed to re-parse with "column reference ... is ambiguous" and the view could not be dumped and restored. The collision can arrive after the view is made, through ALTER TABLE ... ADD COLUMN or RENAME COLUMN, or be there from the start, because set_using_names() picked the name for a column merged by USING. And in a grouped query, a DEFINE reference to a column merged by FULL JOIN USING was printed as COALESCE(id, id). 2. Settle the names DEFINE clauses read first The new mark_define_columns() runs ahead of set_using_names() and, for each same-level Var of every DEFINE clause, resolves the name the way get_variable() will print it: through varnosyn, so that a column of an aliased join is named by the join, and to the user's column alias rather than the catalog name for a relation. It makes that name unique within its RTE and against the names reserved so far, stores it in the RTE's colnames entry, which exempts the column from renaming, and reserves it in dpns->using_names. A system column, or a column of a relation RTE outside the FROM clause, keeps its name and is only reserved. - using_names now holds every name reserved across the query level, not just globally unique USING names, and colname_is_unique() keeps all other RTEs off those names whatever unique_using says. A colliding column is renamed to name_N and its RTE prints a column alias list. - When a USING clause merges a column a DEFINE clause reads, set_using_names() adopts the name settled for it instead of inventing one. A merge needs both inputs to answer to the same name, so preset_input_colname() looks for that name in either input, following an input join down through its joinaliasvars, and the name goes to both inputs. - As with USING names, the reservation also reaches the inputs hidden under an aliased join, which costs at most an extra alias list. - New assertions check that a name pushed down into a join input never overwrites a different name assigned there. - A query without a DEFINE clause is not affected by any of this, and no alias list is printed unless a collision actually occurs. 3. Fold expanded merged join columns back together When a grouped query is deparsed, the GROUP Vars in a DEFINE clause are expanded into the grouping expressions, and over a column merged by USING that is the expression the parser built for the merge: a COALESCE of both inputs for a FULL JOIN, or an input coerced to the common type. Printed without qualifiers, that loses which input each arm came from and does not re-parse to the same tree. The new collapse_define_join_vars() compares subexpressions of the DEFINE clause, innermost first so that a merge over a merge also folds, against the merge expressions of the query's join RTEs, with nulling marks cleared on both sides by remove_nulling_relids(), and replaces each match with a Var for the merged column, which is what the user wrote. 4. Deparsed queries without row pattern recognition change too Three changes to column naming are needed for the above to hold, and they also change pg_get_viewdef() and pg_get_ruledef() output for queries without row pattern recognition. Each fixes output that failed to re-parse or re-parsed to a different query. - A column of a relation RTE outside the FROM clause (a rule's NEW or OLD, or the target of an UPDATE or DELETE) is no longer made unique. It has nowhere to print a column alias list, so a rename forced by, say, an unnamed FULL JOIN USING in a rule action printed a reference such as new.x_1 to a column that does not exist. Other RTE kinds outside the FROM clause, such as the subquery an INSERT ... SELECT reads from, are still renamed. - A TABLEFUNC RTE (JSON_TABLE, XMLTABLE) now prints a column alias list under the same rule as other non-relation RTEs: when one of its columns was renamed or the user wrote one. It used to print none, on the ground that the clause names the columns. So a user-written alias list was lost, and a column that had to be renamed, because a DEFINE clause reads the name or an unnamed FULL JOIN USING makes USING names unique query-wide, left references to it dangling. See the note below. - The column alias list of a function RTE with a single function, no WITH ORDINALITY and no column definition list now includes the columns its composite result type has gained since the query was parsed. Such a column can collide with a reserved name, and because alias lists are positional, leaving it out made the list of an aliased join above apply to the wrong columns on re-parse. 5. Tests Replace the rpr_base tests that recorded the unrestorable view with round-trip tests of the collision cases, the grouped merged-column case, and rules holding a row pattern query, and add create_view and rules tests for the naming changes that apply to all queries. Note on RTE_TABLEFUNC set_relation_column_names() has never printed a column alias list for a TABLEFUNC RTE. The exception, printaliases = false, came with XMLTABLE (fcec6caafa2, 2017), and JSON_TABLE reuses the RTE kind. The only reason on record is the comment that the column names are part of the clause itself, and the tests added with it read the columns without an alias list or a rename. Under that rule a user-written alias list is dropped, and a column that has to be renamed cannot be printed under its new name. Treating such a column as fixed, as is done for the NEW and OLD relations, would not cover a USING merge that has to name it. This patch therefore lets a TABLEFUNC print an alias list like any other non-relation RTE. As it reverses a decision made with XMLTABLE, it may be worth asking Alvaro Herrera, who committed it, whether there was a reason beyond that comment. Author: Henson Choi --- src/backend/utils/adt/ruleutils.c | 480 +++- src/test/regress/expected/create_view.out | 411 ++++ src/test/regress/expected/rpr_base.out | 2663 ++++++++++++++++++++- src/test/regress/expected/rules.out | 54 + src/test/regress/sql/create_view.sql | 165 ++ src/test/regress/sql/rpr_base.sql | 1141 ++++++++- src/test/regress/sql/rules.sql | 36 + src/tools/pgindent/typedefs.list | 1 + 8 files changed, 4834 insertions(+), 117 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 62a85c3d79a..378f10b5e88 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -148,7 +148,10 @@ typedef struct * In some cases we need to make names of merged JOIN USING columns unique * across the whole query, not only per-RTE. If so, unique_using is true * and using_names is a list of C strings representing names already assigned - * to USING columns. + * to USING columns. using_names also holds the names row pattern DEFINE + * clauses read, which are printed without a qualifier and so have to be + * unique across the query level whatever unique_using says; see + * mark_define_columns(). * * When deparsing plan trees, there is always just a single item in the * deparse_namespace list (since a plan tree never contains Vars with @@ -172,7 +175,7 @@ typedef struct char *ret_new_alias; /* alias for NEW in RETURNING list */ /* Workspace for column alias assignment: */ bool unique_using; /* Are we making USING names globally unique */ - List *using_names; /* List of assigned names for USING columns */ + List *using_names; /* Names reserved across the query level */ /* Remaining fields are used only when deparsing a Plan tree: */ Plan *plan; /* immediate parent of current expression */ List *ancestors; /* ancestors of plan */ @@ -322,6 +325,17 @@ typedef struct int counter; /* Largest addition used so far for name */ } NameHashEntry; +/* + * The merged join columns of a query that have an expression of their own, + * for collapse_define_join_vars() + */ +typedef struct +{ + List *exprs; /* merge expressions, nulling marks cleared */ + List *vars; /* the Var naming each merged column */ + Bitmapset *relids; /* every rtindex, to clear nulling marks */ +} collapse_define_context; + /* Callback signature for resolve_special_varno() */ typedef void (*rsv_callback) (Node *node, deparse_context *context, void *callback_arg); @@ -382,6 +396,16 @@ static void set_simple_column_names(deparse_namespace *dpns); static bool has_dangerous_join_using(deparse_namespace *dpns, Node *jtnode); static void set_using_names(deparse_namespace *dpns, Node *jtnode, List *parentUsing); +static bool colname_is_fixed(RangeTblEntry *rte); +static void mark_define_columns(deparse_namespace *dpns, Query *query); +static void mark_define_column(deparse_namespace *dpns, Var *var); +static char *preset_input_colname(deparse_namespace *dpns, int varno, + AttrNumber attno); +static void reserve_colname(deparse_namespace *dpns, char *colname); +static List *function_rte_late_colnames(RangeTblEntry *rte); +static void collapse_define_join_vars(Query *query); +static Node *collapse_define_join_vars_mutator(Node *node, + collapse_define_context *context); static void set_relation_column_names(deparse_namespace *dpns, RangeTblEntry *rte, deparse_columns *colinfo); @@ -4031,6 +4055,121 @@ set_rtable_names(deparse_namespace *dpns, List *parent_namespaces, hash_destroy(names_hash); } +/* + * collapse_define_join_vars: put an expanded merged join column back together + * + * Expanding the GROUP Vars of a DEFINE clause above leaves it holding whatever + * the grouping expression was written over, and over a column merged by USING + * that is the expression the parser built for the merge -- a COALESCE of the + * two inputs, where the join is a FULL one. Printing that is wrong twice. A + * DEFINE clause carries no qualifiers, so both arms come out spelled the same, + * COALESCE(id, id), and which column each came from is gone from the text; and + * re-parsing what is printed nests one merged column inside another, so even + * the shape no longer matches what the grouping step offers. + * + * The join RTE still holds the expression it built, so an expanded one can be + * recognised there and folded back into a Var naming the merged column itself. + * That is what the user wrote, and it re-parses. The inputs of a node are + * folded before the node is looked at, so that a merge built over another + * merge -- a USING join above a USING join -- is seen whole once its inner + * merge has become a Var again. + */ +static void +collapse_define_join_vars(Query *query) +{ + collapse_define_context context; + ListCell *lc; + int rtindex = 0; + + /* Collect the merge expressions once; most queries have none */ + context.exprs = NIL; + context.vars = NIL; + context.relids = bms_add_range(NULL, 1, list_length(query->rtable)); + foreach(lc, query->rtable) + { + RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc); + ListCell *lc2; + AttrNumber attno = 0; + + rtindex++; + if (rte->rtekind != RTE_JOIN) + continue; + + foreach(lc2, rte->joinaliasvars) + { + Node *aliasvar = (Node *) lfirst(lc2); + + if (++attno > rte->joinmergedcols) + break; + + /* Only a merge whose value is not just an input has one */ + if (aliasvar == NULL || IsA(aliasvar, Var)) + continue; + + context.exprs = lappend(context.exprs, + remove_nulling_relids(aliasvar, + context.relids, + NULL)); + context.vars = lappend(context.vars, + makeVar(rtindex, attno, exprType(aliasvar), + exprTypmod(aliasvar), + exprCollation(aliasvar), 0)); + } + } + + if (context.exprs == NIL) + return; + + foreach(lc, query->windowClause) + { + WindowClause *wc = lfirst_node(WindowClause, lc); + + if (wc->defineClause != NIL) + wc->defineClause = (List *) + collapse_define_join_vars_mutator((Node *) wc->defineClause, + &context); + } +} + +static Node * +collapse_define_join_vars_mutator(Node *node, collapse_define_context *context) +{ + if (node == NULL) + return NULL; + + /* Fold the inputs first, so that a merge over a merge is seen whole */ + node = expression_tree_mutator(node, collapse_define_join_vars_mutator, + context); + + /* + * Only what buildMergedJoinVar() builds can be a merge expression: a + * COALESCE of the two inputs, or one input coerced to the common type. + * Anything else is left alone without a comparison. + */ + if (IsA(node, CoalesceExpr) || IsA(node, FuncExpr) || + IsA(node, RelabelType) || IsA(node, CoerceViaIO) || + IsA(node, ArrayCoerceExpr) || IsA(node, CoerceToDomain)) + { + /* + * An outer join above the one that merged the column marks the copy + * the grouping expression carries and not the copy the join RTE + * keeps, and neither mark reaches the printed text. + */ + Node *stripped = remove_nulling_relids(node, context->relids, + NULL); + ListCell *lc; + ListCell *lc2; + + forboth(lc, context->exprs, lc2, context->vars) + { + if (equal(stripped, (Node *) lfirst(lc))) + return (Node *) copyObject((Var *) lfirst(lc2)); + } + } + + return node; +} + /* * set_deparse_for_query: set up deparse_namespace for deparsing a Query tree * @@ -4069,6 +4208,14 @@ set_deparse_for_query(deparse_namespace *dpns, Query *query, dpns->unique_using = has_dangerous_join_using(dpns, (Node *) query->jointree); + /* + * Settle the column names that DEFINE clauses reference, so that the + * USING names chosen next are chosen around them rather than over + * them, and so that they still resolve as written when the query is + * re-parsed. + */ + mark_define_columns(dpns, query); + /* * Select names for columns merged by USING, via a recursive pass over * the query jointree. @@ -4272,6 +4419,9 @@ set_using_names(deparse_namespace *dpns, Node *jtnode, List *parentUsing) if (leftattnos[i] > 0) { expand_colnames_array_to(leftcolinfo, leftattnos[i]); + Assert(leftcolinfo->colnames[leftattnos[i] - 1] == NULL || + strcmp(leftcolinfo->colnames[leftattnos[i] - 1], + colname) == 0); leftcolinfo->colnames[leftattnos[i] - 1] = colname; } @@ -4279,6 +4429,9 @@ set_using_names(deparse_namespace *dpns, Node *jtnode, List *parentUsing) if (rightattnos[i] > 0) { expand_colnames_array_to(rightcolinfo, rightattnos[i]); + Assert(rightcolinfo->colnames[rightattnos[i] - 1] == NULL || + strcmp(rightcolinfo->colnames[rightattnos[i] - 1], + colname) == 0); rightcolinfo->colnames[rightattnos[i] - 1] = colname; } } @@ -4307,7 +4460,10 @@ set_using_names(deparse_namespace *dpns, Node *jtnode, List *parentUsing) * * Though significantly different in results, these two strategies are * implemented by the same code, with only the difference of whether - * to put assigned names into dpns->using_names. + * to put assigned names into dpns->using_names. Either way a new + * name steers clear of what is in dpns->using_names already, which + * includes the names mark_define_columns() settled; and a merged + * column a DEFINE clause reads takes the name settled for it there. */ if (j->usingClause) { @@ -4320,6 +4476,7 @@ set_using_names(deparse_namespace *dpns, Node *jtnode, List *parentUsing) foreach(lc, j->usingClause) { char *colname = strVal(lfirst(lc)); + char *preset; /* Assert it's a merged column */ Assert(leftattnos[i] != 0 && rightattnos[i] != 0); @@ -4327,6 +4484,19 @@ set_using_names(deparse_namespace *dpns, Node *jtnode, List *parentUsing) /* Adopt passed-down name if any, else select unique name */ if (colinfo->colnames[i] != NULL) colname = colinfo->colnames[i]; + else if ((preset = preset_input_colname(dpns, colinfo->leftrti, + leftattnos[i])) != NULL || + (preset = preset_input_colname(dpns, colinfo->rightrti, + rightattnos[i])) != NULL) + { + /* + * A name settled below by mark_define_columns() is the + * name the merged column carries: a DEFINE clause prints + * it as is. It is unique already, and reserved. + */ + colname = preset; + colinfo->colnames[i] = colname; + } else { /* Prefer user-written output alias if any */ @@ -4349,6 +4519,9 @@ set_using_names(deparse_namespace *dpns, Node *jtnode, List *parentUsing) if (leftattnos[i] > 0) { expand_colnames_array_to(leftcolinfo, leftattnos[i]); + Assert(leftcolinfo->colnames[leftattnos[i] - 1] == NULL || + strcmp(leftcolinfo->colnames[leftattnos[i] - 1], + colname) == 0); leftcolinfo->colnames[leftattnos[i] - 1] = colname; } @@ -4356,6 +4529,9 @@ set_using_names(deparse_namespace *dpns, Node *jtnode, List *parentUsing) if (rightattnos[i] > 0) { expand_colnames_array_to(rightcolinfo, rightattnos[i]); + Assert(rightcolinfo->colnames[rightattnos[i] - 1] == NULL || + strcmp(rightcolinfo->colnames[rightattnos[i] - 1], + colname) == 0); rightcolinfo->colnames[rightattnos[i] - 1] = colname; } @@ -4376,6 +4552,263 @@ set_using_names(deparse_namespace *dpns, Node *jtnode, List *parentUsing) (int) nodeTag(jtnode)); } +/* + * colname_is_fixed: is this a column that no rename can reach? + * + * A relation RTE outside the FROM clause -- a rule's NEW or OLD, or the + * target of an UPDATE or DELETE -- has nowhere to print a column alias list, + * so a renamed column of one would reach the output only where it is + * referenced, naming a column that does not exist. + * set_relation_column_names() leaves such columns alone. + */ +static bool +colname_is_fixed(RangeTblEntry *rte) +{ + return rte->rtekind == RTE_RELATION && !rte->inFromCl; +} + +/* + * mark_define_columns: settle the names of the columns DEFINE clauses read + * + * Within a DEFINE clause a column can only be named without a qualifier, + * since the qualifier slot names a pattern variable; get_rule_define() + * deparses with varprefix off for that reason. So an unqualified reference + * there has to resolve exactly as printed, and its name has to be one that + * no other RTE of the query prints. + * + * We settle such a name before set_using_names() invents any: choose it + * unique within its RTE and against the names reserved so far, store it into + * the RTE's colnames entry, which exempts the column from being renamed for + * some other name's sake, and reserve it in dpns->using_names so that no + * other RTE is given it. A USING clause that merges the column adopts the + * settled name instead of inventing one (see set_using_names), and + * everything else falls to the ordinary machinery. A column that cannot be + * renamed keeps its name and is only reserved. + */ +static void +mark_define_columns(deparse_namespace *dpns, Query *query) +{ + ListCell *lc; + + foreach(lc, query->windowClause) + { + WindowClause *wc = lfirst_node(WindowClause, lc); + List *vars; + + /* DEFINE has no outer-level Vars or sub-selects */ + vars = pull_vars_of_level((Node *) wc->defineClause, 0); + foreach_node(Var, var, vars) + mark_define_column(dpns, var); + } +} + +/* + * Settle the printed name of one DEFINE-referenced column. + */ +static void +mark_define_column(deparse_namespace *dpns, Var *var) +{ + RangeTblEntry *rte; + deparse_columns *colinfo; + int varno; + AttrNumber attno; + char *colname; + + /* + * Resolve the reference the way get_variable() will when it prints this + * Var, or the name settled here is not the name that reaches the output. + * A Var that reads a join column carries the child relation in varno and + * the join RTE in varnosyn, and it is the latter that gets printed. + */ + Assert(var->varnosyn > 0); + varno = var->varnosyn; + attno = var->varattnosyn; + Assert(attno != InvalidAttrNumber); /* whole-row is rejected in DEFINE */ + + rte = rt_fetch(varno, dpns->rtable); + colinfo = deparse_columns_fetch(varno, dpns); + + /* + * A system column's name is fixed and get_variable() reads it from the + * catalog, so there is no alias to choose; just hold the name. + */ + if (attno < 0) + { + if (rte->rtekind == RTE_RELATION) + reserve_colname(dpns, get_rte_attribute_name(rte, attno)); + return; + } + + /* Settled already, by an earlier reference to the same column */ + if (attno <= colinfo->num_cols && colinfo->colnames[attno - 1] != NULL) + return; + + /* + * Find the name this column would be printed with. Resolve as + * set_relation_column_names() will: a column alias the user wrote is what + * gets printed, and the catalog name only stands in where there is none. + */ + if (rte->rtekind == RTE_RELATION) + { + char *real_colname = get_attname(rte->relid, attno, true); + + /* + * NULL would mean there is no such attribute at all. A dropped + * column still has its pg_attribute row and comes back with its + * placeholder name, but a column that a DEFINE clause reads cannot be + * dropped out from under it. + */ + Assert(real_colname != NULL); + + if (rte->alias && attno <= list_length(rte->alias->colnames)) + colname = strVal(list_nth(rte->alias->colnames, attno - 1)); + else + colname = real_colname; + } + else + { + Assert(attno <= list_length(rte->eref->colnames)); + colname = strVal(list_nth(rte->eref->colnames, attno - 1)); + Assert(colname[0] != '\0'); /* not a dropped column */ + } + + /* + * A column that cannot be renamed would only be held, but a DEFINE clause + * cannot name a column of a relation outside the FROM clause: the + * qualifier slot is taken by the pattern variables. + */ + Assert(!colname_is_fixed(rte)); + + /* + * Choose the name unique within the RTE and against what is reserved, + * store it, and reserve it. A later reference to another column of the + * same RTE that carries the same name -- possible once a column has been + * renamed under the view -- gets name_N here, and the DEFINE clause + * follows it, being printed from colinfo. + */ + expand_colnames_array_to(colinfo, attno); + colname = make_colname_unique(colname, dpns, colinfo); + colinfo->colnames[attno - 1] = colname; + reserve_colname(dpns, colname); +} + +/* + * preset_input_colname: the name settled below a join for one of its columns + * + * mark_define_columns() stores a name into the colnames entry of the RTE a + * DEFINE reference resolves to, and for a column merged by an unaliased + * INNER or LEFT JOIN that is an input of the join rather than the join + * itself. A merged column has to be named the same on both sides, so + * set_using_names() asks here, before inventing a name, whether one of the + * inputs has settled it already. A join input is followed down the way the + * parser built the merged column, through joinaliasvars. + */ +static char * +preset_input_colname(deparse_namespace *dpns, int varno, AttrNumber attno) +{ + RangeTblEntry *rte; + deparse_columns *colinfo; + + Assert(varno >= 1 && attno > 0); + + colinfo = deparse_columns_fetch(varno, dpns); + if (attno <= colinfo->num_cols && colinfo->colnames[attno - 1] != NULL) + return colinfo->colnames[attno - 1]; + + rte = rt_fetch(varno, dpns->rtable); + if (rte->rtekind == RTE_JOIN && + attno <= list_length(rte->joinaliasvars)) + { + Node *aliasvar = (Node *) list_nth(rte->joinaliasvars, attno - 1); + List *vars = pull_var_clause(aliasvar, 0); + ListCell *lc; + + foreach(lc, vars) + { + Var *var = (Var *) lfirst(lc); + char *colname; + + colname = preset_input_colname(dpns, var->varno, var->varattno); + if (colname != NULL) + return colname; + } + } + + return NULL; +} + +/* + * reserve_colname: keep any other RTE from being given this column name + */ +static void +reserve_colname(deparse_namespace *dpns, char *colname) +{ + ListCell *lc; + + foreach(lc, dpns->using_names) + { + if (strcmp((char *) lfirst(lc), colname) == 0) + return; + } + dpns->using_names = lappend(dpns->using_names, colname); +} + +/* + * function_rte_late_colnames: the names of the columns a function RTE's result + * type has grown since the query was parsed + * + * expandRTE() stops at the column count recorded at parse time, so a column + * the type has gained since then is not in the list the deparser works from. + * Yet it is a column the RTE has now: a name that has to resolve exactly as + * printed -- a globally unique USING name, or one a DEFINE clause reads -- + * must be kept off it, and the column alias list being positional, an + * aliased join above lays its own list over this one end to end, so the + * grown columns have to be counted in. + * + * Returns NIL unless there is one function and no WITH ORDINALITY, those being + * the cases where the grown columns land at the end of the RTE. Anywhere else + * they land in the middle, shifting the attnos this query was parsed with. + */ +static List * +function_rte_late_colnames(RangeTblEntry *rte) +{ + RangeTblFunction *rtfunc; + TypeFuncClass functypclass; + Oid funcrettype; + TupleDesc tupdesc; + List *result = NIL; + int i; + + if (rte->funcordinality || list_length(rte->functions) != 1) + return NIL; + + rtfunc = (RangeTblFunction *) linitial(rte->functions); + + /* A coldeflist fixes the column set, and pins the return type at RECORD */ + if (rtfunc->funccolnames != NIL) + return NIL; + + functypclass = get_expr_result_type(rtfunc->funcexpr, &funcrettype, + &tupdesc); + if (functypclass != TYPEFUNC_COMPOSITE && + functypclass != TYPEFUNC_COMPOSITE_DOMAIN) + return NIL; + + for (i = rtfunc->funccolcount; i < tupdesc->natts; i++) + { + Form_pg_attribute attr = TupleDescAttr(tupdesc, i); + + /* Spell a dropped column the way expandRTE() does */ + if (attr->attisdropped) + result = lappend(result, makeString(pstrdup(""))); + else + result = lappend(result, + makeString(pstrdup(NameStr(attr->attname)))); + } + + return result; +} + /* * set_relation_column_names: select column aliases for a non-join RTE * @@ -4448,6 +4881,15 @@ set_relation_column_names(deparse_namespace *dpns, RangeTblEntry *rte, /* Since we're not creating Vars, rtindex etc. don't matter */ expandRTE(rte, 1, 0, VAR_RETURNING_DEFAULT, -1, true /* include dropped */ , &colnames, NULL); + + /* + * Take the columns the result type has grown since parse time as + * well: they are columns the RTE has now, so a name that has to + * resolve exactly as printed must be kept off of them too, and + * the alias list is positional, so they are printed in full. + */ + colnames = list_concat(colnames, + function_rte_late_colnames(rte)); } else colnames = rte->eref->colnames; @@ -4526,8 +4968,17 @@ set_relation_column_names(deparse_namespace *dpns, RangeTblEntry *rte, else colname = real_colname; - /* Unique-ify and insert into colinfo */ - colname = make_colname_unique(colname, dpns, colinfo); + /* + * Unique-ify and insert into colinfo, unless this is a column no + * rename can reach: a relation RTE outside the FROM clause has + * nowhere to carry a column alias list, so a renamed column would + * reach the output only where it is referenced, naming a column + * that does not exist. Other kinds reach here with inFromCl + * clear and still get printed, the subquery an INSERT ... SELECT + * reads from among them, so those are renamed as before. + */ + if (!colname_is_fixed(rte)) + colname = make_colname_unique(colname, dpns, colinfo); colinfo->colnames[i] = colname; add_to_names_hash(colinfo, colname); @@ -4560,17 +5011,17 @@ set_relation_column_names(deparse_namespace *dpns, RangeTblEntry *rte, * are different from the underlying "real" names. For a function RTE, * always emit a complete column alias list; this is to protect against * possible instability of the default column names (eg, from altering - * parameter names). For tablefunc RTEs, we never print aliases, because - * the column names are part of the clause itself. For other RTE types, - * print if we changed anything OR if there were user-written column - * aliases (since the latter would be part of the underlying "reality"). + * parameter names). For other RTE types, print if we changed anything OR + * if there were user-written column aliases (since the latter would be + * part of the underlying "reality"). A tablefunc RTE is among those: its + * clause names the columns it produces, but it accepts a column alias + * list like any other, and one is needed once a column has had to be + * renamed. */ if (rte->rtekind == RTE_RELATION) colinfo->printaliases = changed_any; else if (rte->rtekind == RTE_FUNCTION) colinfo->printaliases = true; - else if (rte->rtekind == RTE_TABLEFUNC) - colinfo->printaliases = false; else if (rte->alias && rte->alias->colnames != NIL) colinfo->printaliases = true; else @@ -4912,8 +5363,9 @@ colname_is_unique(const char *colname, deparse_namespace *dpns, } /* - * Also check against USING-column names that must be globally unique. - * These are not hashed, but there should be few of them. + * Also check against the names reserved across the query level: USING + * column names that must be globally unique, and the names DEFINE clauses + * read. These are not hashed, as they are expected to be few. */ foreach(lc, dpns->using_names) { @@ -5679,6 +6131,8 @@ get_query_def(Query *query, StringInfo buf, List *parentnamespace, wc->defineClause = (List *) flatten_group_exprs(NULL, query, (Node *) wc->defineClause); } + + collapse_define_join_vars(query); } /* diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out index 053fa56573f..c471a3eb974 100644 --- a/src/test/regress/expected/create_view.out +++ b/src/test/regress/expected/create_view.out @@ -971,6 +971,417 @@ select pg_get_viewdef('view_of_joins_2d', true); JOIN tbl1a USING (a) AS x) y; (1 row) +-- A TABLEFUNC RTE names its columns in the clause that produces them, but it +-- accepts a column alias list like any other RTE, and that list is where a +-- rename of one of its columns has to be printed: the ON clause below refers +-- to the column by the name the query sees, and without the list the two +-- would not agree. The anonymous FULL JOIN is what forces USING names to be +-- unique query-wide, which is what pushes the JSON_TABLE column aside. +create table tblnr (m int); +create table tblnu (x int, m int); +create table tblnl (x int); +create table tblnm (x int); +create table tblnw (a int, c int, d int); +create table tblnv (c int); +create view view_of_unrenamable as +select j.m +from (tblnl full join tblnm using (x)), + (json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt + join tblnr on jt.x > 0) j; +select pg_get_viewdef('view_of_unrenamable', true); + pg_get_viewdef +---------------------------------------------------------- + SELECT j.m + + FROM tblnl + + FULL JOIN tblnm USING (x), + + (JSON_TABLE( + + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0+ + COLUMNS ( + + x integer PATH '$' + + ) + + ) jt(x_1) + + JOIN tblnr ON jt.x_1 > 0) j; +(1 row) + +-- and that text is what has to reparse +select 'create view view_of_unrenamable_2 as ' + || pg_get_viewdef('view_of_unrenamable', true) \gexec +create view view_of_unrenamable_2 as SELECT j.m + FROM tblnl + FULL JOIN tblnm USING (x), + (JSON_TABLE( + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + COLUMNS ( + x integer PATH '$' + ) + ) jt(x_1) + JOIN tblnr ON jt.x_1 > 0) j; +select pg_get_viewdef('view_of_unrenamable', true) + = pg_get_viewdef('view_of_unrenamable_2', true) as round_trips; + round_trips +------------- + t +(1 row) + +-- The name of a merged column is the other place a rename lands, and it lands +-- on both sides at once: whatever is picked, each input has to answer to it, +-- the TABLEFUNC through its alias list like the table through its own. +create view view_of_unrenamable_using as +select j.m +from (tblnl full join tblnm using (x)), + (json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt + join tblnu using (x)) j; +select pg_get_viewdef('view_of_unrenamable_using', true); + pg_get_viewdef +---------------------------------------------------------- + SELECT j.m + + FROM tblnl + + FULL JOIN tblnm USING (x), + + (JSON_TABLE( + + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0+ + COLUMNS ( + + x integer PATH '$' + + ) + + ) jt(x_1) + + JOIN tblnu tblnu(x_1, m) USING (x_1)) j; +(1 row) + +select 'create view view_of_unrenamable_using_2 as ' + || pg_get_viewdef('view_of_unrenamable_using', true) \gexec +create view view_of_unrenamable_using_2 as SELECT j.m + FROM tblnl + FULL JOIN tblnm USING (x), + (JSON_TABLE( + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + COLUMNS ( + x integer PATH '$' + ) + ) jt(x_1) + JOIN tblnu tblnu(x_1, m) USING (x_1)) j; +select pg_get_viewdef('view_of_unrenamable_using', true) + = pg_get_viewdef('view_of_unrenamable_using_2', true) as round_trips; + round_trips +------------- + t +(1 row) + +-- The far side of an INNER or LEFT JOIN is as much a side as the near one: +-- the merged column's expression names only the near input, but the rename +-- reaches both. +create view view_of_unrenamable_right as +select j.m +from (tblnl full join tblnm using (x)), + ((tblnu join json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt + using (x)) full join tblnm t2 using (x)) j; +select pg_get_viewdef('view_of_unrenamable_right', true); + pg_get_viewdef +---------------------------------------------------------- + SELECT j.m + + FROM tblnl + + FULL JOIN tblnm USING (x), + + (tblnu tblnu(x_1, m) + + JOIN JSON_TABLE( + + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0+ + COLUMNS ( + + x integer PATH '$' + + ) + + ) jt(x_1) USING (x_1) + + FULL JOIN tblnm t2(x_1) USING (x_1)) j; +(1 row) + +select 'create view view_of_unrenamable_right_2 as ' + || pg_get_viewdef('view_of_unrenamable_right', true) \gexec +create view view_of_unrenamable_right_2 as SELECT j.m + FROM tblnl + FULL JOIN tblnm USING (x), + (tblnu tblnu(x_1, m) + JOIN JSON_TABLE( + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + COLUMNS ( + x integer PATH '$' + ) + ) jt(x_1) USING (x_1) + FULL JOIN tblnm t2(x_1) USING (x_1)) j; +select pg_get_viewdef('view_of_unrenamable_right', true) + = pg_get_viewdef('view_of_unrenamable_right_2', true) as round_trips; + round_trips +------------- + t +(1 row) + +create view view_of_unrenamable_left as +select j.m +from (tblnl full join tblnm using (x)), + ((tblnu left join json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt + using (x)) full join tblnm t2 using (x)) j; +select pg_get_viewdef('view_of_unrenamable_left', true); + pg_get_viewdef +---------------------------------------------------------- + SELECT j.m + + FROM tblnl + + FULL JOIN tblnm USING (x), + + (tblnu tblnu(x_1, m) + + LEFT JOIN JSON_TABLE( + + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0+ + COLUMNS ( + + x integer PATH '$' + + ) + + ) jt(x_1) USING (x_1) + + FULL JOIN tblnm t2(x_1) USING (x_1)) j; +(1 row) + +select 'create view view_of_unrenamable_left_2 as ' + || pg_get_viewdef('view_of_unrenamable_left', true) \gexec +create view view_of_unrenamable_left_2 as SELECT j.m + FROM tblnl + FULL JOIN tblnm USING (x), + (tblnu tblnu(x_1, m) + LEFT JOIN JSON_TABLE( + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + COLUMNS ( + x integer PATH '$' + ) + ) jt(x_1) USING (x_1) + FULL JOIN tblnm t2(x_1) USING (x_1)) j; +select pg_get_viewdef('view_of_unrenamable_left', true) + = pg_get_viewdef('view_of_unrenamable_left_2', true) as round_trips; + round_trips +------------- + t +(1 row) + +-- An aliased join hides its inputs, and a USING name above it is taken by +-- the join's own column: the join renames it on its alias list, and the +-- TABLEFUNC underneath, being held to the query-wide USING names as well, +-- renames its column on its own list. +create view view_of_unrenamable_hidden as +select j.m +from (tblnl full join tblnm using (x)), + ((json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt + join tblnr on true) j full join tblnm t2 using (x)); +select pg_get_viewdef('view_of_unrenamable_hidden', true); + pg_get_viewdef +---------------------------------------------------------- + SELECT j.m + + FROM tblnl + + FULL JOIN tblnm USING (x), + + (JSON_TABLE( + + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0+ + COLUMNS ( + + x integer PATH '$' + + ) + + ) jt(x_2) + + JOIN tblnr ON true) j(x_1, m) + + FULL JOIN tblnm t2(x_1) USING (x_1); +(1 row) + +select 'create view view_of_unrenamable_hidden_2 as ' + || pg_get_viewdef('view_of_unrenamable_hidden', true) \gexec +create view view_of_unrenamable_hidden_2 as SELECT j.m + FROM tblnl + FULL JOIN tblnm USING (x), + (JSON_TABLE( + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + COLUMNS ( + x integer PATH '$' + ) + ) jt(x_2) + JOIN tblnr ON true) j(x_1, m) + FULL JOIN tblnm t2(x_1) USING (x_1); +select pg_get_viewdef('view_of_unrenamable_hidden', true) + = pg_get_viewdef('view_of_unrenamable_hidden_2', true) as round_trips; + round_trips +------------- + t +(1 row) + +-- A name a parent join pushes down onto a merged column has to be unique in +-- the input it lands in, whatever the USING clause underneath spells. +create view view_of_unrenamable_pushed as +select j2.d +from (tblnv join (tblnw join json_table(jsonb '[1]', '$[*]' columns (a int path '$')) x + using (a)) using (c)) as j2(a); +select pg_get_viewdef('view_of_unrenamable_pushed', true); + pg_get_viewdef +------------------------------------------------------- + SELECT j2.d + + FROM (tblnv tblnv(a) + + JOIN (tblnw tblnw(a_1, a, d) + + JOIN JSON_TABLE( + + '[1]'::jsonb, '$[*]' AS json_table_path_0+ + COLUMNS ( + + a integer PATH '$' + + ) + + ) x(a_1) USING (a_1)) USING (a)) j2; +(1 row) + +select 'create view view_of_unrenamable_pushed_2 as ' + || pg_get_viewdef('view_of_unrenamable_pushed', true) \gexec +create view view_of_unrenamable_pushed_2 as SELECT j2.d + FROM (tblnv tblnv(a) + JOIN (tblnw tblnw(a_1, a, d) + JOIN JSON_TABLE( + '[1]'::jsonb, '$[*]' AS json_table_path_0 + COLUMNS ( + a integer PATH '$' + ) + ) x(a_1) USING (a_1)) USING (a)) j2; +select pg_get_viewdef('view_of_unrenamable_pushed', true) + = pg_get_viewdef('view_of_unrenamable_pushed_2', true) as round_trips; + round_trips +------------- + t +(1 row) + +-- Two anonymous FULL JOINs merging the same name through a TABLEFUNC each +-- still get distinct names. +create view view_of_unrenamable_twice as +select count(*) as n +from (tblnl full join json_table(jsonb '[1]', '$[*]' columns (x int path '$')) jt1 + using (x)), + (tblnm full join json_table(jsonb '[1]', '$[*]' columns (x int path '$')) jt2 + using (x)); +select pg_get_viewdef('view_of_unrenamable_twice', true); + pg_get_viewdef +------------------------------------------------------- + SELECT count(*) AS n + + FROM tblnl + + FULL JOIN JSON_TABLE( + + '[1]'::jsonb, '$[*]' AS json_table_path_0+ + COLUMNS ( + + x integer PATH '$' + + ) + + ) jt1 USING (x), + + tblnm tblnm(x_1) + + FULL JOIN JSON_TABLE( + + '[1]'::jsonb, '$[*]' AS json_table_path_0+ + COLUMNS ( + + x integer PATH '$' + + ) + + ) jt2(x_1) USING (x_1); +(1 row) + +select 'create view view_of_unrenamable_twice_2 as ' + || pg_get_viewdef('view_of_unrenamable_twice', true) \gexec +create view view_of_unrenamable_twice_2 as SELECT count(*) AS n + FROM tblnl + FULL JOIN JSON_TABLE( + '[1]'::jsonb, '$[*]' AS json_table_path_0 + COLUMNS ( + x integer PATH '$' + ) + ) jt1 USING (x), + tblnm tblnm(x_1) + FULL JOIN JSON_TABLE( + '[1]'::jsonb, '$[*]' AS json_table_path_0 + COLUMNS ( + x integer PATH '$' + ) + ) jt2(x_1) USING (x_1); +select pg_get_viewdef('view_of_unrenamable_twice', true) + = pg_get_viewdef('view_of_unrenamable_twice_2', true) as round_trips; + round_trips +------------- + t +(1 row) + +-- and a column alias list the user wrote on a TABLEFUNC is part of the query +create view view_of_tablefunc_alias as +select t.a from json_table(jsonb '[1]', '$[*]' columns (c int path '$')) as t(a); +select pg_get_viewdef('view_of_tablefunc_alias', true); + pg_get_viewdef +------------------------------------------------------- + SELECT a + + FROM JSON_TABLE( + + '[1]'::jsonb, '$[*]' AS json_table_path_0+ + COLUMNS ( + + c integer PATH '$' + + ) + + ) t(a); +(1 row) + +select 'create view view_of_tablefunc_alias_2 as ' + || pg_get_viewdef('view_of_tablefunc_alias', true) \gexec +create view view_of_tablefunc_alias_2 as SELECT a + FROM JSON_TABLE( + '[1]'::jsonb, '$[*]' AS json_table_path_0 + COLUMNS ( + c integer PATH '$' + ) + ) t(a); +select pg_get_viewdef('view_of_tablefunc_alias', true) + = pg_get_viewdef('view_of_tablefunc_alias_2', true) as round_trips; + round_trips +------------- + t +(1 row) + +drop view view_of_tablefunc_alias_2, view_of_tablefunc_alias; +drop view view_of_unrenamable_twice_2, view_of_unrenamable_twice; +drop view view_of_unrenamable_pushed_2, view_of_unrenamable_pushed; +drop view view_of_unrenamable_hidden_2, view_of_unrenamable_hidden; +drop view view_of_unrenamable_left_2, view_of_unrenamable_left; +drop view view_of_unrenamable_right_2, view_of_unrenamable_right; +drop view view_of_unrenamable_using_2, view_of_unrenamable_using; +drop view view_of_unrenamable_2, view_of_unrenamable; +drop table tblnr, tblnu, tblnl, tblnm, tblnw, tblnv; +-- The columns a function's result type grows after the view is made are +-- columns the RTE has now, and the alias list being positional they are +-- printed in full: an aliased join above lays its own list over its inputs' +-- lists end to end, so a grown column left off would shift the input after +-- it, and a reference into that input would silently land on another column. +create table tblfc (a int, z int); +insert into tblfc values (1, 5); +create function tblfc_f() returns setof tblfc language sql + as $$ select * from tblfc $$; +create table tblfr (z int, val int); +insert into tblfr values (1, 7); +create view view_of_grown_input as +select j.a, j.val from (tblfc_f() f join tblfr on true) j; +select * from view_of_grown_input; + a | val +---+----- + 1 | 7 +(1 row) + +alter table tblfc add column val int, add column spare int; +select pg_get_viewdef('view_of_grown_input', true); + pg_get_viewdef +----------------------------------------------------------- + SELECT j.a, + + j.val + + FROM (tblfc_f() f(a, z, val, spare) + + JOIN tblfr ON true) j(a, z, val_1, spare, z_1, val); +(1 row) + +select 'create view view_of_grown_input_2 as ' + || pg_get_viewdef('view_of_grown_input', true) \gexec +create view view_of_grown_input_2 as SELECT j.a, + j.val + FROM (tblfc_f() f(a, z, val, spare) + JOIN tblfr ON true) j(a, z, val_1, spare, z_1, val); +select pg_get_viewdef('view_of_grown_input', true) + = pg_get_viewdef('view_of_grown_input_2', true) as round_trips; + round_trips +------------- + t +(1 row) + +select * from view_of_grown_input; + a | val +---+----- + 1 | 7 +(1 row) + +select * from view_of_grown_input_2; + a | val +---+----- + 1 | 7 +(1 row) + +drop view view_of_grown_input_2, view_of_grown_input; +drop function tblfc_f(); +drop table tblfc, tblfr; -- Test view decompilation in the face of column addition/deletion/renaming create table tt2 (a int, b int, c int); create table tt3 (ax int8, b int2, c numeric); diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index 83e653241a6..112892451ec 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -3998,83 +3998,2620 @@ SELECT pg_get_viewdef('rpr_serial_join'::regclass); 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); +-- A DEFINE clause can only name a column without a qualifier, so the name has +-- to resolve exactly as printed. When another relation of the query acquires +-- a column of that name, the deparser pushes the newcomer aside with a column +-- alias list, the same way it protects a column merged by USING. +CREATE TABLE rpr_pin (id INT, val INT); +CREATE TABLE rpr_pin_other (id INT); +INSERT INTO rpr_pin VALUES (1, 10), (2, 20), (3, 15); +INSERT INTO rpr_pin_other VALUES (1), (2), (3); +CREATE VIEW rpr_pin_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_pin, rpr_pin_other +WHERE rpr_pin.id = rpr_pin_other.id +WINDOW w AS (ORDER BY rpr_pin.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS val > 0); +-- names reached through a navigation operation are pinned too +CREATE VIEW rpr_pin_nav_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_pin, rpr_pin_other +WHERE rpr_pin.id = rpr_pin_other.id +WINDOW w AS (ORDER BY rpr_pin.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(val) < val); +-- no collision yet, so no column alias list +SELECT pg_get_viewdef('rpr_pin_v'::regclass, true); + pg_get_viewdef +------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_pin, + + rpr_pin_other + + WHERE rpr_pin.id = rpr_pin_other.id + + WINDOW w AS (ORDER BY rpr_pin.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS val > 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); +ALTER TABLE rpr_pin_other ADD COLUMN val INT; +SELECT pg_get_viewdef('rpr_pin_v'::regclass, true); + pg_get_viewdef +------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_pin, + + rpr_pin_other rpr_pin_other(id, val_1) + + WHERE rpr_pin.id = rpr_pin_other.id + + WINDOW w AS (ORDER BY rpr_pin.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS val > 0); +(1 row) + +SELECT pg_get_viewdef('rpr_pin_nav_v'::regclass, true); + pg_get_viewdef +------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_pin, + + rpr_pin_other rpr_pin_other(id, val_1) + + WHERE rpr_pin.id = rpr_pin_other.id + + WINDOW w AS (ORDER BY rpr_pin.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS PREV(val) < val); +(1 row) + +-- and the deparsed text builds an identical view +CREATE VIEW rpr_pin_v2 AS + SELECT count(*) OVER w AS cnt + FROM rpr_pin, + rpr_pin_other rpr_pin_other(id, val_1) + WHERE rpr_pin.id = rpr_pin_other.id + WINDOW w AS (ORDER BY rpr_pin.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS val > 0); +SELECT pg_get_viewdef('rpr_pin_v'::regclass, true) + = pg_get_viewdef('rpr_pin_v2'::regclass, true) AS identical; + identical +----------- + t +(1 row) + +-- The hazard this section guards against cannot be written in the first +-- place: a whole-row reference through a row constructor is rejected in +-- DEFINE, so no view can carry one as far as the deparser. +CREATE VIEW rpr_pin_row_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_pin, rpr_pin_other +WHERE rpr_pin.id = rpr_pin_other.id +WINDOW w AS (ORDER BY rpr_pin.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS ROW(rpr_pin.*) IS NOT NULL); +ERROR: whole-row reference is not allowed in DEFINE clause +LINE 8: DEFINE A AS ROW(rpr_pin.*) IS NOT NULL); + ^ +HINT: A DEFINE condition may reference individual columns only. +-- a column merged by USING is pinned the same way +CREATE TABLE rpr_pin_l (x INT, y INT); +CREATE TABLE rpr_pin_r (x INT, z INT); +CREATE TABLE rpr_pin_x (id INT); +CREATE VIEW rpr_pin_using_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_pin_l JOIN rpr_pin_r USING (x), rpr_pin_x +WINDOW w AS (ORDER BY rpr_pin_l.y + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS x > 0); +ALTER TABLE rpr_pin_x ADD COLUMN x INT; +SELECT pg_get_viewdef('rpr_pin_using_v'::regclass, true); + pg_get_viewdef +-------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_pin_l + + JOIN rpr_pin_r USING (x), + + rpr_pin_x rpr_pin_x(id, x_1) + + WINDOW w AS (ORDER BY rpr_pin_l.y ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS x > 0); +(1 row) + +-- a JOIN ... ON behaves the same, and the view keeps returning its rows +CREATE TABLE rpr_pin_j1 (id INT, price INT); +CREATE TABLE rpr_pin_j2 (id INT, qty INT); +INSERT INTO rpr_pin_j1 VALUES (1, 10), (2, 20); +INSERT INTO rpr_pin_j2 VALUES (1, 5), (2, 7); +CREATE VIEW rpr_pin_on_v AS +SELECT j1.id, count(*) OVER w AS cnt +FROM rpr_pin_j1 j1 JOIN rpr_pin_j2 j2 ON j1.id = j2.id +WINDOW w AS (ORDER BY j1.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS price > 0); +ALTER TABLE rpr_pin_j2 ADD COLUMN price INT; +SELECT pg_get_viewdef('rpr_pin_on_v'::regclass, true); + pg_get_viewdef +-------------------------------------------------------------------------------- + SELECT j1.id, + + count(*) OVER w AS cnt + + FROM rpr_pin_j1 j1 + + JOIN rpr_pin_j2 j2(id, qty, price_1) ON j1.id = j2.id + + WINDOW w AS (ORDER BY j1.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS price > 0); (1 row) -SELECT * FROM sv4; +SELECT * FROM rpr_pin_on_v ORDER BY id; id | cnt ----+----- 1 | 2 2 | 0 (2 rows) -SELECT * FROM sv; +-- An aliased join hides its inputs, so the name that gets printed is the join's +-- own, taken from varnosyn, not the child column the Var carries in varno. +-- Pinning the child instead would reserve a name that never reaches the output +-- and leave the printed one free for a later column to collide with. +CREATE TABLE rpr_pin_a (i INT, x INT); +CREATE TABLE rpr_pin_b (k INT, y INT); +CREATE TABLE rpr_pin_c (m INT); +INSERT INTO rpr_pin_a VALUES (1, 10), (2, 20); +INSERT INTO rpr_pin_b VALUES (1, 5), (2, 7); +INSERT INTO rpr_pin_c VALUES (100), (200); +CREATE VIEW rpr_pin_alias_v AS +SELECT count(*) OVER w AS cnt +FROM (rpr_pin_a JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j(p, q, r, s), + rpr_pin_c +WINDOW w AS (ORDER BY rpr_pin_c.m + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS q > 0); +ALTER TABLE rpr_pin_c ADD COLUMN q INT; +SELECT pg_get_viewdef('rpr_pin_alias_v'::regclass, true); + pg_get_viewdef +-------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM (rpr_pin_a + + JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j(p, q, r, s), + + rpr_pin_c rpr_pin_c(m, q_1) + + WINDOW w AS (ORDER BY rpr_pin_c.m ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a) + + DEFINE + + a AS q > 0); +(1 row) + +SELECT * FROM rpr_pin_alias_v; + cnt +----- + 1 + 1 + 1 + 1 +(4 rows) + +-- and the deparsed text builds a view that returns the same rows +CREATE VIEW rpr_pin_alias_v2 AS +SELECT count(*) OVER w AS cnt +FROM (rpr_pin_a JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j(p, q, r, s), + rpr_pin_c rpr_pin_c(m, q_1) +WINDOW w AS (ORDER BY rpr_pin_c.m + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a) + DEFINE a AS q > 0); +SELECT * FROM rpr_pin_alias_v2; + cnt +----- + 1 + 1 + 1 + 1 +(4 rows) + +DROP VIEW rpr_pin_alias_v2; +-- Without a user column alias list the join still keeps the printed name, and +-- the input relation is the one that moves aside. +CREATE VIEW rpr_pin_alias_v3 AS +SELECT count(*) OVER w AS cnt +FROM (rpr_pin_a JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j, rpr_pin_c +WINDOW w AS (ORDER BY rpr_pin_c.m + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS x > 0); +SELECT pg_get_viewdef('rpr_pin_alias_v3'::regclass, true); + pg_get_viewdef +-------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM (rpr_pin_a rpr_pin_a(i, x_1) + + JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j(i, x, k, y), + + rpr_pin_c + + WINDOW w AS (ORDER BY rpr_pin_c.m ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a) + + DEFINE + + a AS x > 0); +(1 row) + +DROP VIEW rpr_pin_alias_v3; +DROP VIEW rpr_pin_alias_v; +DROP TABLE rpr_pin_a, rpr_pin_b, rpr_pin_c; +-- A relation RTE prints the column alias the user wrote, not the catalog name, +-- so the alias is the name to reserve. Pinning the catalog name would replace +-- the alias in the printed text and push aside an unrelated column that +-- collides only with a name nobody prints. +CREATE TABLE rpr_pin_d (i INT, k INT); +CREATE TABLE rpr_pin_e (m INT); +INSERT INTO rpr_pin_d VALUES (1, 10), (2, 20); +INSERT INTO rpr_pin_e VALUES (100), (200); +CREATE VIEW rpr_pin_rel_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_pin_d d(p, q), rpr_pin_e +WINDOW w AS (ORDER BY rpr_pin_e.m + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS q > 0); +-- a column named after the catalog name is no collision: q is what is printed +ALTER TABLE rpr_pin_e ADD COLUMN k INT; +SELECT pg_get_viewdef('rpr_pin_rel_v'::regclass, true); + pg_get_viewdef +-------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_pin_d d(p, q), + + rpr_pin_e + + WINDOW w AS (ORDER BY rpr_pin_e.m ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a) + + DEFINE + + a AS q > 0); +(1 row) + +-- one named after the alias is, and moves aside +ALTER TABLE rpr_pin_e ADD COLUMN q INT; +SELECT pg_get_viewdef('rpr_pin_rel_v'::regclass, true); + pg_get_viewdef +-------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_pin_d d(p, q), + + rpr_pin_e rpr_pin_e(m, k, q_1) + + WINDOW w AS (ORDER BY rpr_pin_e.m ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a) + + DEFINE + + a AS q > 0); +(1 row) + +SELECT * FROM rpr_pin_rel_v; + cnt +----- + 1 + 1 + 1 + 1 +(4 rows) + +DROP VIEW rpr_pin_rel_v; +DROP TABLE rpr_pin_d, rpr_pin_e; +-- A name a DEFINE clause reads is the one name in the query that cannot be +-- spelled any other way, the qualifier slot being reserved for a pattern +-- variable. set_using_names() picks the name of every column merged by USING +-- before it, and is free to pick any name at all, renaming the merged inputs +-- to match. So the DEFINE names that are settled already are reserved first +-- and the merged names are chosen around them. Here the second USING would +-- otherwise reach for x_1, the very column the DEFINE clause reads: the +-- anonymous FULL JOIN forces USING names to be unique query-wide, which takes +-- plain x, and x_1 is what the next one counts up to. +CREATE TABLE rpr_res_t (x_1 INT, id INT); +CREATE TABLE rpr_res_l1 (x INT); +CREATE TABLE rpr_res_r1 (x INT); +CREATE TABLE rpr_res_l2 (x INT); +CREATE TABLE rpr_res_r2 (x INT); +INSERT INTO rpr_res_t VALUES (1, 1), (2, 2); +INSERT INTO rpr_res_l1 VALUES (1); +INSERT INTO rpr_res_r1 VALUES (1); +INSERT INTO rpr_res_l2 VALUES (1); +INSERT INTO rpr_res_r2 VALUES (1); +CREATE VIEW rpr_res_using_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_t, + (rpr_res_l1 FULL JOIN rpr_res_r1 USING (x)), + (rpr_res_l2 JOIN rpr_res_r2 USING (x)) +WINDOW w AS (ORDER BY rpr_res_t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS x_1 > 0); +SELECT pg_get_viewdef('rpr_res_using_v'::regclass, true); + pg_get_viewdef +--------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_res_t, + + rpr_res_l1 + + FULL JOIN rpr_res_r1 USING (x), + + rpr_res_l2 rpr_res_l2(x_2) + + JOIN rpr_res_r2 rpr_res_r2(x_2) USING (x_2) + + WINDOW w AS (ORDER BY rpr_res_t.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS x_1 > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_using_rt AS ' + || pg_get_viewdef('rpr_res_using_v'::regclass, true) \gexec +CREATE VIEW rpr_res_using_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_res_t, + rpr_res_l1 + FULL JOIN rpr_res_r1 USING (x), + rpr_res_l2 rpr_res_l2(x_2) + JOIN rpr_res_r2 rpr_res_r2(x_2) USING (x_2) + WINDOW w AS (ORDER BY rpr_res_t.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS x_1 > 0); +SELECT pg_get_viewdef('rpr_res_using_v'::regclass, true) + = pg_get_viewdef('rpr_res_using_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_using_v; + cnt +----- + 2 + 0 +(2 rows) + +SELECT * FROM rpr_res_using_rt; + cnt +----- + 2 + 0 +(2 rows) + +DROP VIEW rpr_res_using_rt, rpr_res_using_v; +DROP TABLE rpr_res_t, rpr_res_l1, rpr_res_r1, rpr_res_l2, rpr_res_r2; +-- A merged column keeps its natural name and collides all the same. This one +-- is the column set_relation_column_names() cannot push aside afterwards: its +-- name was settled and handed to both inputs before that function ran, so the +-- loop there passes over it. An ordinary column in its place does move aside, +-- which is what the rpr_pin views above cover. +CREATE TABLE rpr_res_a (j INT, p INT); +CREATE TABLE rpr_res_b (j INT, q INT); +CREATE TABLE rpr_res_c (r INT, s INT); +INSERT INTO rpr_res_a VALUES (1, 10); +INSERT INTO rpr_res_b VALUES (1, 30); +INSERT INTO rpr_res_c VALUES (1, 10), (2, 20), (3, 15); +CREATE VIEW rpr_res_merged_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_a JOIN rpr_res_b USING (j) CROSS JOIN rpr_res_c +WINDOW w AS (ORDER BY rpr_res_c.s + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL PATTERN (X Y+) + DEFINE X AS true, Y AS s > PREV(s)); +-- the collision arrives only now: the merged column has been named j all along +ALTER TABLE rpr_res_c RENAME COLUMN s TO j; +SELECT pg_get_viewdef('rpr_res_merged_v'::regclass, true); + pg_get_viewdef +-------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_res_a rpr_res_a(j_1, p) + + JOIN rpr_res_b rpr_res_b(j_1, q) USING (j_1) + + CROSS JOIN rpr_res_c + + WINDOW w AS (ORDER BY rpr_res_c.j ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (x y+) + + DEFINE + + x AS true, + + y AS j > PREV(j)); +(1 row) + +SELECT 'CREATE VIEW rpr_res_merged_rt AS ' + || pg_get_viewdef('rpr_res_merged_v'::regclass, true) \gexec +CREATE VIEW rpr_res_merged_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_res_a rpr_res_a(j_1, p) + JOIN rpr_res_b rpr_res_b(j_1, q) USING (j_1) + CROSS JOIN rpr_res_c + WINDOW w AS (ORDER BY rpr_res_c.j ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (x y+) + DEFINE + x AS true, + y AS j > PREV(j)); +SELECT pg_get_viewdef('rpr_res_merged_v'::regclass, true) + = pg_get_viewdef('rpr_res_merged_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_merged_v; + cnt +----- + 3 + 0 + 0 +(3 rows) + +SELECT * FROM rpr_res_merged_rt; + cnt +----- + 3 + 0 + 0 +(3 rows) + +DROP VIEW rpr_res_merged_rt, rpr_res_merged_v; +DROP TABLE rpr_res_a, rpr_res_b, rpr_res_c; +-- Naming a merged column renames the columns it merges, and the name can land +-- in an RTE that has a real column of that name already. The column the +-- DEFINE clause reads is the one that cannot move, so the merge counts past it +-- instead and the RTE prints two distinct aliases. +CREATE TABLE rpr_res_fa (k INT); +CREATE TABLE rpr_res_fb (k INT); +CREATE TABLE rpr_res_ga (k INT, k_1 INT); +CREATE TABLE rpr_res_gb (k INT); +CREATE TABLE rpr_res_ord (id INT); +INSERT INTO rpr_res_fa VALUES (1); +INSERT INTO rpr_res_fb VALUES (1); +INSERT INTO rpr_res_ga VALUES (1, 5); +INSERT INTO rpr_res_gb VALUES (1); +INSERT INTO rpr_res_ord VALUES (1), (2); +CREATE VIEW rpr_res_dup_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_fa FULL JOIN rpr_res_fb USING (k), + rpr_res_ga JOIN rpr_res_gb USING (k), + rpr_res_ord +WINDOW w AS (ORDER BY rpr_res_ord.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS k_1 > 0); +SELECT pg_get_viewdef('rpr_res_dup_v'::regclass, true); + pg_get_viewdef +----------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_res_fa + + FULL JOIN rpr_res_fb USING (k), + + rpr_res_ga rpr_res_ga(k_2, k_1) + + JOIN rpr_res_gb rpr_res_gb(k_2) USING (k_2), + + rpr_res_ord + + WINDOW w AS (ORDER BY rpr_res_ord.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS k_1 > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_dup_rt AS ' + || pg_get_viewdef('rpr_res_dup_v'::regclass, true) \gexec +CREATE VIEW rpr_res_dup_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_res_fa + FULL JOIN rpr_res_fb USING (k), + rpr_res_ga rpr_res_ga(k_2, k_1) + JOIN rpr_res_gb rpr_res_gb(k_2) USING (k_2), + rpr_res_ord + WINDOW w AS (ORDER BY rpr_res_ord.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS k_1 > 0); +SELECT pg_get_viewdef('rpr_res_dup_v'::regclass, true) + = pg_get_viewdef('rpr_res_dup_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_dup_v; + cnt +----- + 2 + 0 +(2 rows) + +SELECT * FROM rpr_res_dup_rt; + cnt +----- + 2 + 0 +(2 rows) + +DROP VIEW rpr_res_dup_rt, rpr_res_dup_v; +DROP TABLE rpr_res_fa, rpr_res_fb, rpr_res_ga, rpr_res_gb, rpr_res_ord; +-- A merged column a DEFINE clause reads is named by the DEFINE clause: its +-- name is settled before set_using_names() runs, and the USING clause adopts +-- it rather than inventing one. The merged name therefore stays id here, +-- and the newcomer is the one that moves aside. +CREATE TABLE rpr_res_p (id INT, v INT); +CREATE TABLE rpr_res_q (id INT, w INT); +CREATE TABLE rpr_res_s (n INT); +INSERT INTO rpr_res_p VALUES (1, 10), (2, 20); +INSERT INTO rpr_res_q VALUES (1, 30), (3, 40); +INSERT INTO rpr_res_s VALUES (7); +CREATE VIEW rpr_res_full_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_p FULL JOIN rpr_res_q USING (id), rpr_res_s +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS id > 0); +ALTER TABLE rpr_res_s ADD COLUMN id INT; +SELECT pg_get_viewdef('rpr_res_full_v'::regclass, true); + pg_get_viewdef +----------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_res_p + + FULL JOIN rpr_res_q USING (id), + + rpr_res_s rpr_res_s(n, id_1) + + 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 id > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_full_rt AS ' + || pg_get_viewdef('rpr_res_full_v'::regclass, true) \gexec +CREATE VIEW rpr_res_full_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_res_p + FULL JOIN rpr_res_q USING (id), + rpr_res_s rpr_res_s(n, id_1) + 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 id > 0); +SELECT pg_get_viewdef('rpr_res_full_v'::regclass, true) + = pg_get_viewdef('rpr_res_full_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_full_v; + cnt +----- + 3 + 0 + 0 +(3 rows) + +SELECT * FROM rpr_res_full_rt; + cnt +----- + 3 + 0 + 0 +(3 rows) + +DROP VIEW rpr_res_full_rt, rpr_res_full_v; +DROP TABLE rpr_res_p, rpr_res_q, rpr_res_s; +-- An aliased join answers for its inputs. The parser gives every column of +-- such a join the join's own varnosyn, so a DEFINE clause reading a column the +-- inputs brought in names the join and not the relation it came from. The +-- name is settled all the same, the join's USING clause not being the thing +-- that produced it, and so it is reserved: a column of a join that its own +-- USING clause does not name is no more merged than a relation's would be. +-- Left unreserved, the merged name counts up onto it and the join prints the +-- one alias twice, which reparses as an ambiguous column. +CREATE TABLE rpr_res_ja (x INT, x_1 INT); +CREATE TABLE rpr_res_jb (x INT, z INT); +CREATE TABLE rpr_res_m1 (x INT); +CREATE TABLE rpr_res_m2 (x INT); +CREATE TABLE rpr_res_ordj (id INT); +INSERT INTO rpr_res_ja VALUES (1, 7); +INSERT INTO rpr_res_jb VALUES (1, 9); +INSERT INTO rpr_res_m1 VALUES (1); +INSERT INTO rpr_res_m2 VALUES (1); +INSERT INTO rpr_res_ordj VALUES (1), (2); +CREATE VIEW rpr_res_alias_v AS +SELECT count(*) OVER w AS cnt +FROM (rpr_res_m1 FULL JOIN rpr_res_m2 USING (x)), + (rpr_res_ja JOIN rpr_res_jb USING (x)) AS jx, + rpr_res_ordj +WINDOW w AS (ORDER BY rpr_res_ordj.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS x_1 > 0); +SELECT pg_get_viewdef('rpr_res_alias_v'::regclass, true); + pg_get_viewdef +------------------------------------------------------------------------------------------ + SELECT count(*) OVER w AS cnt + + FROM rpr_res_m1 + + FULL JOIN rpr_res_m2 USING (x), + + (rpr_res_ja rpr_res_ja(x_2, x_1_1) + + JOIN rpr_res_jb rpr_res_jb(x_2, z) USING (x_2)) jx(x_2, x_1, z), + + rpr_res_ordj + + WINDOW w AS (ORDER BY rpr_res_ordj.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS x_1 > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_alias_rt AS ' + || pg_get_viewdef('rpr_res_alias_v'::regclass, true) \gexec +CREATE VIEW rpr_res_alias_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_res_m1 + FULL JOIN rpr_res_m2 USING (x), + (rpr_res_ja rpr_res_ja(x_2, x_1_1) + JOIN rpr_res_jb rpr_res_jb(x_2, z) USING (x_2)) jx(x_2, x_1, z), + rpr_res_ordj + WINDOW w AS (ORDER BY rpr_res_ordj.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS x_1 > 0); +SELECT pg_get_viewdef('rpr_res_alias_v'::regclass, true) + = pg_get_viewdef('rpr_res_alias_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_alias_v; + cnt +----- + 2 + 0 +(2 rows) + +SELECT * FROM rpr_res_alias_rt; + cnt +----- + 2 + 0 +(2 rows) + +-- The same through NATURAL JOIN, which names no column in the query text. The +-- test above reads a join's USING clause to tell a merged column from one that +-- only passes through, and the analyzed tree is where it reads it: the parser +-- works out which columns NATURAL merges and files them there. +CREATE VIEW rpr_res_nat_v AS +SELECT count(*) OVER w AS cnt +FROM (rpr_res_m1 FULL JOIN rpr_res_m2 USING (x)), + (rpr_res_ja NATURAL JOIN rpr_res_jb) AS jx, + rpr_res_ordj +WINDOW w AS (ORDER BY rpr_res_ordj.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS x_1 > 0); +SELECT pg_get_viewdef('rpr_res_nat_v'::regclass, true); + pg_get_viewdef +------------------------------------------------------------------------------------------ + SELECT count(*) OVER w AS cnt + + FROM rpr_res_m1 + + FULL JOIN rpr_res_m2 USING (x), + + (rpr_res_ja rpr_res_ja(x_2, x_1_1) + + JOIN rpr_res_jb rpr_res_jb(x_2, z) USING (x_2)) jx(x_2, x_1, z), + + rpr_res_ordj + + WINDOW w AS (ORDER BY rpr_res_ordj.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS x_1 > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_nat_rt AS ' + || pg_get_viewdef('rpr_res_nat_v'::regclass, true) \gexec +CREATE VIEW rpr_res_nat_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_res_m1 + FULL JOIN rpr_res_m2 USING (x), + (rpr_res_ja rpr_res_ja(x_2, x_1_1) + JOIN rpr_res_jb rpr_res_jb(x_2, z) USING (x_2)) jx(x_2, x_1, z), + rpr_res_ordj + WINDOW w AS (ORDER BY rpr_res_ordj.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS x_1 > 0); +SELECT pg_get_viewdef('rpr_res_nat_v'::regclass, true) + = pg_get_viewdef('rpr_res_nat_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +DROP VIEW rpr_res_nat_rt, rpr_res_nat_v; +DROP VIEW rpr_res_alias_rt, rpr_res_alias_v; +DROP TABLE rpr_res_ja, rpr_res_jb, rpr_res_m1, rpr_res_m2, rpr_res_ordj; +-- A column a function's result type grows after the view is made is one the +-- deparser does not see at all: expandRTE() stops at the column count the +-- query was parsed with. A column it cannot see is one it cannot rename out +-- of the way, and the name it collides with here is the one a DEFINE clause +-- has to resolve to as printed. So the grown columns are looked up and named +-- too, and printed in full, the alias list being positional. +CREATE TABLE rpr_res_fn (id INT, val INT); +INSERT INTO rpr_res_fn VALUES (1, 1), (2, 2), (3, 3); +CREATE TABLE rpr_res_cfg (a INT); +INSERT INTO rpr_res_cfg VALUES (1); +CREATE FUNCTION rpr_res_fcfg() RETURNS SETOF rpr_res_cfg LANGUAGE sql + AS $$ SELECT * FROM rpr_res_cfg $$; +CREATE VIEW rpr_res_fn_v AS +SELECT rpr_res_fn.id, count(*) OVER w AS cnt +FROM rpr_res_fn, rpr_res_fcfg() f +WINDOW w AS (ORDER BY rpr_res_fn.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS val > 0); +-- nothing to keep off yet +SELECT pg_get_viewdef('rpr_res_fn_v'::regclass, true); + pg_get_viewdef +---------------------------------------------------------------------------------------- + SELECT rpr_res_fn.id, + + count(*) OVER w AS cnt + + FROM rpr_res_fn, + + rpr_res_fcfg() f(a) + + WINDOW w AS (ORDER BY rpr_res_fn.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS val > 0); +(1 row) + +ALTER TABLE rpr_res_cfg ADD COLUMN val INT; +SELECT pg_get_viewdef('rpr_res_fn_v'::regclass, true); + pg_get_viewdef +---------------------------------------------------------------------------------------- + SELECT rpr_res_fn.id, + + count(*) OVER w AS cnt + + FROM rpr_res_fn, + + rpr_res_fcfg() f(a, val_1) + + WINDOW w AS (ORDER BY rpr_res_fn.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS val > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_fn_rt AS ' + || pg_get_viewdef('rpr_res_fn_v'::regclass, true) \gexec +CREATE VIEW rpr_res_fn_rt AS SELECT rpr_res_fn.id, + count(*) OVER w AS cnt + FROM rpr_res_fn, + rpr_res_fcfg() f(a, val_1) + WINDOW w AS (ORDER BY rpr_res_fn.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS val > 0); +SELECT pg_get_viewdef('rpr_res_fn_v'::regclass, true) + = pg_get_viewdef('rpr_res_fn_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_fn_v; id | cnt ----+----- - 1 | 2 + 1 | 3 2 | 0 -(2 rows) + 3 | 0 +(3 rows) + +SELECT * FROM rpr_res_fn_rt; + id | cnt +----+----- + 1 | 3 + 2 | 0 + 3 | 0 +(3 rows) + +-- a grown column that collides with nothing is still on the list +ALTER TABLE rpr_res_cfg ADD COLUMN spare INT; +SELECT pg_get_viewdef('rpr_res_fn_v'::regclass, true); + pg_get_viewdef +---------------------------------------------------------------------------------------- + SELECT rpr_res_fn.id, + + count(*) OVER w AS cnt + + FROM rpr_res_fn, + + rpr_res_fcfg() f(a, val_1, spare) + + WINDOW w AS (ORDER BY rpr_res_fn.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS val > 0); +(1 row) + +DROP VIEW rpr_res_fn_rt, rpr_res_fn_v; +DROP FUNCTION rpr_res_fcfg(); +DROP TABLE rpr_res_fn, rpr_res_cfg; +-- A system column is named from the catalog, not from the deparser's own +-- choice, so there is no alias to pick for it and nothing to exempt from +-- renaming. Its name still has to be held against the rest of the query, or +-- a column that turns up later answers to it as well. +CREATE TABLE rpr_res_sys (id INT, v INT); +INSERT INTO rpr_res_sys VALUES (1, 1), (2, 2); +CREATE TYPE rpr_res_ct AS (a INT); +CREATE FUNCTION rpr_res_fct() RETURNS SETOF rpr_res_ct LANGUAGE sql + AS $$ SELECT ROW(1)::rpr_res_ct $$; +CREATE VIEW rpr_res_sys_v AS +SELECT rpr_res_sys.id, count(*) OVER w AS cnt +FROM rpr_res_sys, rpr_res_fct() f +WINDOW w AS (ORDER BY rpr_res_sys.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS ctid IS NOT NULL); +ALTER TYPE rpr_res_ct ADD ATTRIBUTE ctid INT; +SELECT pg_get_viewdef('rpr_res_sys_v'::regclass, true); + pg_get_viewdef +----------------------------------------------------------------------------------------- + SELECT rpr_res_sys.id, + + count(*) OVER w AS cnt + + FROM rpr_res_sys, + + rpr_res_fct() f(a, ctid_1) + + WINDOW w AS (ORDER BY rpr_res_sys.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS ctid IS NOT NULL); +(1 row) + +SELECT 'CREATE VIEW rpr_res_sys_rt AS ' + || pg_get_viewdef('rpr_res_sys_v'::regclass, true) \gexec +CREATE VIEW rpr_res_sys_rt AS SELECT rpr_res_sys.id, + count(*) OVER w AS cnt + FROM rpr_res_sys, + rpr_res_fct() f(a, ctid_1) + WINDOW w AS (ORDER BY rpr_res_sys.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS ctid IS NOT NULL); +SELECT pg_get_viewdef('rpr_res_sys_v'::regclass, true) + = pg_get_viewdef('rpr_res_sys_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_sys_v; +ERROR: cannot cast type record to rpr_res_ct +LINE 1: SELECT ROW(1)::rpr_res_ct + ^ +DETAIL: Input has too few columns. +QUERY: SELECT ROW(1)::rpr_res_ct +CONTEXT: SQL function "rpr_res_fct" statement 1 +SELECT * FROM rpr_res_sys_rt; +ERROR: cannot cast type record to rpr_res_ct +LINE 1: SELECT ROW(1)::rpr_res_ct + ^ +DETAIL: Input has too few columns. +QUERY: SELECT ROW(1)::rpr_res_ct +CONTEXT: SQL function "rpr_res_fct" statement 1 +DROP VIEW rpr_res_sys_rt, rpr_res_sys_v; +DROP FUNCTION rpr_res_fct(); +DROP TYPE rpr_res_ct; +DROP TABLE rpr_res_sys; +-- A TABLEFUNC RTE writes its column names into the clause that produces them, +-- but it accepts a column alias list like any other RTE, and a rename of one +-- of its columns is printed there. So a TABLEFUNC column that comes to +-- answer to the name a DEFINE clause reads is renamed like any other column +-- would be, and the DEFINE clause keeps its spelling. +CREATE TABLE rpr_res_tf (id INT, s INT); +INSERT INTO rpr_res_tf VALUES (1, 1), (2, 2); +CREATE VIEW rpr_res_tf_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_tf, + JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jx +WINDOW w AS (ORDER BY rpr_res_tf.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS s > 0); +-- the collision arrives only now +ALTER TABLE rpr_res_tf RENAME COLUMN s TO c1; +SELECT pg_get_viewdef('rpr_res_tf_v'::regclass, true); + pg_get_viewdef +---------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_res_tf, + + JSON_TABLE( + + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + + COLUMNS ( + + c1 integer PATH '$' + + ) + + ) jx(c1_1) + + WINDOW w AS (ORDER BY rpr_res_tf.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS c1 > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_tf_rt AS ' + || pg_get_viewdef('rpr_res_tf_v'::regclass, true) \gexec +CREATE VIEW rpr_res_tf_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_res_tf, + JSON_TABLE( + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + COLUMNS ( + c1 integer PATH '$' + ) + ) jx(c1_1) + WINDOW w AS (ORDER BY rpr_res_tf.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS c1 > 0); +SELECT pg_get_viewdef('rpr_res_tf_v'::regclass, true) + = pg_get_viewdef('rpr_res_tf_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_tf_v; + cnt +----- + 4 + 0 + 0 + 0 +(4 rows) + +SELECT * FROM rpr_res_tf_rt; + cnt +----- + 4 + 0 + 0 + 0 +(4 rows) -DROP VIEW sv4; -DROP VIEW sv; -DROP TABLE sa, sb; +DROP VIEW rpr_res_tf_rt, rpr_res_tf_v; +DROP TABLE rpr_res_tf; +-- An aliased join answers for its inputs and hides them, so the DEFINE +-- clause here reads the join's own column x. That name is reserved across +-- the query level, which reaches the TABLEFUNC column underneath as well: +-- it is renamed on its own alias list, and the join prints x on its list to +-- keep the name the query sees. Out of reach of an unqualified reference, +-- the TABLEFUNC column would not have collided, but a reserved name is kept +-- off every RTE of the level, as a globally unique USING name is. +CREATE TABLE rpr_res_hid (id INT, v INT); +CREATE TABLE rpr_res_hu (m INT); +INSERT INTO rpr_res_hid VALUES (1, 1), (2, 2), (3, 3); +INSERT INTO rpr_res_hu VALUES (9); +CREATE VIEW rpr_res_hid_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_hid, + (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (x int PATH '$')) AS jt + JOIN rpr_res_hu ON jt.x > 0) j +WINDOW w AS (ORDER BY rpr_res_hid.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS x > 0); +SELECT pg_get_viewdef('rpr_res_hid_v'::regclass, true); + pg_get_viewdef +----------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_res_hid, + + (JSON_TABLE( + + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + + COLUMNS ( + + x integer PATH '$' + + ) + + ) jt(x_1) + + JOIN rpr_res_hu ON jt.x_1 > 0) j(x, m) + + WINDOW w AS (ORDER BY rpr_res_hid.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS x > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_hid_rt AS ' + || pg_get_viewdef('rpr_res_hid_v'::regclass, true) \gexec +CREATE VIEW rpr_res_hid_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_res_hid, + (JSON_TABLE( + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + COLUMNS ( + x integer PATH '$' + ) + ) jt(x_1) + JOIN rpr_res_hu ON jt.x_1 > 0) j(x, m) + WINDOW w AS (ORDER BY rpr_res_hid.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS x > 0); +SELECT pg_get_viewdef('rpr_res_hid_v'::regclass, true) + = pg_get_viewdef('rpr_res_hid_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_hid_v; + cnt +----- + 6 + 0 + 0 + 0 + 0 + 0 +(6 rows) + +SELECT * FROM rpr_res_hid_rt; + cnt +----- + 6 + 0 + 0 + 0 + 0 + 0 +(6 rows) + +DROP VIEW rpr_res_hid_rt, rpr_res_hid_v; +DROP TABLE rpr_res_hid, rpr_res_hu; +-- A column can come to carry the name a DEFINE clause reads only after the +-- view is made, by being renamed, and from then on the two have to be told +-- apart in the printed text. The DEFINE column keeps its spelling, being +-- settled first, and the newcomer gets name_N -- whether it sits in the same +-- RTE or in another one where a DEFINE clause reads it too, is the one a +-- USING clause merges, or is not merged but carries the USING clause's +-- spelling, which is no business of the deparser's to guess from. A USING +-- clause elsewhere that spells the newcomer's new name moves out of the way +-- as well. +CREATE TABLE rpr_res_ren (c INT, d INT); +INSERT INTO rpr_res_ren VALUES (1, 1), (2, 2); +CREATE TABLE rpr_res_ren2 (a INT, e INT); +INSERT INTO rpr_res_ren2 VALUES (1, 0), (2, 0); +CREATE TABLE rpr_res_renl (a_1 INT); +CREATE TABLE rpr_res_renr (a_1 INT); +INSERT INTO rpr_res_renl VALUES (1); +INSERT INTO rpr_res_renr VALUES (1); +CREATE TABLE rpr_res_rs (x INT); +CREATE TABLE rpr_res_rr (x INT, y INT); +INSERT INTO rpr_res_rs VALUES (1), (2); +INSERT INTO rpr_res_rr VALUES (1, 5), (2, 6); +CREATE TABLE rpr_res_rena (id INT, a INT); +CREATE TABLE rpr_res_renb (id INT, b INT); +INSERT INTO rpr_res_rena VALUES (1, 1), (2, 2); +INSERT INTO rpr_res_renb VALUES (1, 5), (2, 0); +-- the same RTE, its alias list shorter than the table +CREATE VIEW rpr_res_ren_v AS +SELECT x.a, x.d, count(*) OVER w AS cnt +FROM rpr_res_ren AS x(a) +WINDOW w AS (ORDER BY x.a + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (P Q*) + DEFINE P AS a > 0, Q AS d > 0); +-- the column a USING clause merges +CREATE VIEW rpr_res_renu_v AS +SELECT a, x.d, count(*) OVER w AS cnt +FROM rpr_res_ren AS x(a) JOIN rpr_res_ren2 USING (a) +WINDOW w AS (ORDER BY a + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (P Q*) + DEFINE P AS d > 0); +-- a USING clause elsewhere that spells the name the newcomer is going to get +CREATE VIEW rpr_res_renx_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_ren AS x(a), rpr_res_renl JOIN rpr_res_renr USING (a_1) +WINDOW w AS (ORDER BY x.a + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (P Q*) + DEFINE P AS a > 0, Q AS d > 0); +-- a merged column that is renamed away, and a DEFINE column renamed onto +-- the USING clause's spelling +CREATE VIEW rpr_res_renm_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_rs JOIN rpr_res_rr USING (x) +WINDOW w AS (ORDER BY y + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS y > 0); +-- another RTE, both of its columns read by the DEFINE clause +CREATE VIEW rpr_res_reno_v AS +SELECT rpr_res_rena.id, count(*) OVER w AS cnt +FROM rpr_res_rena JOIN rpr_res_renb ON rpr_res_rena.id = rpr_res_renb.id +WINDOW w AS (ORDER BY rpr_res_rena.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (P Q*) + DEFINE P AS a > 0, Q AS b > 0); +-- the collisions arrive only now +ALTER TABLE rpr_res_ren RENAME d TO a; +ALTER TABLE rpr_res_rr RENAME x TO z; +ALTER TABLE rpr_res_rr RENAME y TO x; +ALTER TABLE rpr_res_renb RENAME b TO a; +SELECT pg_get_viewdef('rpr_res_ren_v'::regclass, true); + pg_get_viewdef +---------------------------------------------------------------------------- + SELECT a, + + a_1 AS d, + + count(*) OVER w AS cnt + + FROM rpr_res_ren x(a, a_1) + + WINDOW w AS (ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (p q*) + + DEFINE + + p AS a > 0, + + q AS a_1 > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_ren_rt AS ' + || pg_get_viewdef('rpr_res_ren_v'::regclass, true) \gexec +CREATE VIEW rpr_res_ren_rt AS SELECT a, + a_1 AS d, + count(*) OVER w AS cnt + FROM rpr_res_ren x(a, a_1) + WINDOW w AS (ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (p q*) + DEFINE + p AS a > 0, + q AS a_1 > 0); +SELECT pg_get_viewdef('rpr_res_ren_v'::regclass, true) + = pg_get_viewdef('rpr_res_ren_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_ren_v; + a | d | cnt +---+---+----- + 1 | 1 | 2 + 2 | 2 | 0 +(2 rows) + +SELECT * FROM rpr_res_ren_rt; + a | d | cnt +---+---+----- + 1 | 1 | 2 + 2 | 2 | 0 +(2 rows) + +SELECT pg_get_viewdef('rpr_res_renu_v'::regclass, true); + pg_get_viewdef +-------------------------------------------------------------------------------- + SELECT x.a_1 AS a, + + x.a AS d, + + count(*) OVER w AS cnt + + FROM rpr_res_ren x(a_1, a) + + JOIN rpr_res_ren2 rpr_res_ren2(a_1, e) USING (a_1) + + WINDOW w AS (ORDER BY x.a_1 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (p q*) + + DEFINE + + p AS a > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_renu_rt AS ' + || pg_get_viewdef('rpr_res_renu_v'::regclass, true) \gexec +CREATE VIEW rpr_res_renu_rt AS SELECT x.a_1 AS a, + x.a AS d, + count(*) OVER w AS cnt + FROM rpr_res_ren x(a_1, a) + JOIN rpr_res_ren2 rpr_res_ren2(a_1, e) USING (a_1) + WINDOW w AS (ORDER BY x.a_1 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (p q*) + DEFINE + p AS a > 0); +SELECT pg_get_viewdef('rpr_res_renu_v'::regclass, true) + = pg_get_viewdef('rpr_res_renu_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_renu_v; + a | d | cnt +---+---+----- + 1 | 1 | 2 + 2 | 2 | 0 +(2 rows) + +SELECT * FROM rpr_res_renu_rt; + a | d | cnt +---+---+----- + 1 | 1 | 2 + 2 | 2 | 0 +(2 rows) + +SELECT pg_get_viewdef('rpr_res_renx_v'::regclass, true); + pg_get_viewdef +------------------------------------------------------------------------------ + SELECT count(*) OVER w AS cnt + + FROM rpr_res_ren x(a, a_1), + + rpr_res_renl rpr_res_renl(a_1_1) + + JOIN rpr_res_renr rpr_res_renr(a_1_1) USING (a_1_1) + + WINDOW w AS (ORDER BY x.a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (p q*) + + DEFINE + + p AS a > 0, + + q AS a_1 > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_renx_rt AS ' + || pg_get_viewdef('rpr_res_renx_v'::regclass, true) \gexec +CREATE VIEW rpr_res_renx_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_res_ren x(a, a_1), + rpr_res_renl rpr_res_renl(a_1_1) + JOIN rpr_res_renr rpr_res_renr(a_1_1) USING (a_1_1) + WINDOW w AS (ORDER BY x.a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (p q*) + DEFINE + p AS a > 0, + q AS a_1 > 0); +SELECT pg_get_viewdef('rpr_res_renx_v'::regclass, true) + = pg_get_viewdef('rpr_res_renx_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_renx_v; + cnt +----- + 2 + 0 +(2 rows) + +SELECT * FROM rpr_res_renx_rt; + cnt +----- + 2 + 0 +(2 rows) + +SELECT pg_get_viewdef('rpr_res_renm_v'::regclass, true); + pg_get_viewdef +--------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_res_rs rpr_res_rs(x_1) + + JOIN rpr_res_rr rpr_res_rr(x_1, x) USING (x_1) + + WINDOW w AS (ORDER BY rpr_res_rr.x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS x > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_renm_rt AS ' + || pg_get_viewdef('rpr_res_renm_v'::regclass, true) \gexec +CREATE VIEW rpr_res_renm_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_res_rs rpr_res_rs(x_1) + JOIN rpr_res_rr rpr_res_rr(x_1, x) USING (x_1) + WINDOW w AS (ORDER BY rpr_res_rr.x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS x > 0); +SELECT pg_get_viewdef('rpr_res_renm_v'::regclass, true) + = pg_get_viewdef('rpr_res_renm_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_renm_v; + cnt +----- + 2 + 0 +(2 rows) + +SELECT * FROM rpr_res_renm_rt; + cnt +----- + 2 + 0 +(2 rows) + +SELECT pg_get_viewdef('rpr_res_reno_v'::regclass, true); + pg_get_viewdef +------------------------------------------------------------------------------------------ + SELECT rpr_res_rena.id, + + count(*) OVER w AS cnt + + FROM rpr_res_rena + + JOIN rpr_res_renb rpr_res_renb(id, a_1) ON rpr_res_rena.id = rpr_res_renb.id + + WINDOW w AS (ORDER BY rpr_res_rena.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (p q*) + + DEFINE + + p AS a > 0, + + q AS a_1 > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_reno_rt AS ' + || pg_get_viewdef('rpr_res_reno_v'::regclass, true) \gexec +CREATE VIEW rpr_res_reno_rt AS SELECT rpr_res_rena.id, + count(*) OVER w AS cnt + FROM rpr_res_rena + JOIN rpr_res_renb rpr_res_renb(id, a_1) ON rpr_res_rena.id = rpr_res_renb.id + WINDOW w AS (ORDER BY rpr_res_rena.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (p q*) + DEFINE + p AS a > 0, + q AS a_1 > 0); +SELECT pg_get_viewdef('rpr_res_reno_v'::regclass, true) + = pg_get_viewdef('rpr_res_reno_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_reno_v; + id | cnt +----+----- + 1 | 1 + 2 | 1 +(2 rows) + +SELECT * FROM rpr_res_reno_rt; + id | cnt +----+----- + 1 | 1 + 2 | 1 +(2 rows) + +DROP VIEW rpr_res_reno_rt, rpr_res_reno_v; +DROP VIEW rpr_res_renm_rt, rpr_res_renm_v; +DROP VIEW rpr_res_renx_rt, rpr_res_renx_v; +DROP VIEW rpr_res_renu_rt, rpr_res_renu_v; +DROP VIEW rpr_res_ren_rt, rpr_res_ren_v; +DROP TABLE rpr_res_ren, rpr_res_ren2, rpr_res_renl, rpr_res_renr; +DROP TABLE rpr_res_rs, rpr_res_rr; +DROP TABLE rpr_res_rena, rpr_res_renb; +-- A merged column an aliased join carries can spell the DEFINE name from the +-- start, and a TABLEFUNC among the join's inputs changes nothing about that: +-- the join's own alias list names the merged column, the inputs answer to +-- it, and the DEFINE column is left alone. Nor does the order of the FROM +-- list matter. +CREATE TABLE rpr_res_tj (id INT, c1 INT); +INSERT INTO rpr_res_tj VALUES (1, 1), (2, 2); +CREATE TABLE rpr_res_tb (c1 INT, y INT); +INSERT INTO rpr_res_tb VALUES (1, 10), (2, 20); +CREATE TABLE rpr_res_ti (id INT); +INSERT INTO rpr_res_ti VALUES (1), (2); +CREATE VIEW rpr_res_tj_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_tj, + (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jt + JOIN rpr_res_tb USING (c1)) AS j(k, m) +WINDOW w AS (ORDER BY rpr_res_tj.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS c1 > 0); +CREATE VIEW rpr_res_tjr_v AS +SELECT count(*) OVER w AS cnt +FROM (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jt + JOIN rpr_res_tb USING (c1)) AS j(k, m), + rpr_res_tj +WINDOW w AS (ORDER BY rpr_res_tj.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS c1 > 0); +-- the join's alias list puts the USING spelling on a column it does not +-- merge, and that column is the one the DEFINE clause reads +CREATE VIEW rpr_res_tja_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_ti, + (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jt + JOIN rpr_res_tb USING (c1)) AS j(k, c1) +WINDOW w AS (ORDER BY rpr_res_ti.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS c1 > 0); +-- the same with the TABLEFUNC on the right +CREATE VIEW rpr_res_tjb_v AS +SELECT count(*) OVER w AS cnt +FROM (rpr_res_tb JOIN JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jt + USING (c1)) AS j(a, c1) +WINDOW w AS (ORDER BY j.a + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS c1 > 0); +SELECT pg_get_viewdef('rpr_res_tj_v'::regclass, true); + pg_get_viewdef +---------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_res_tj, + + (JSON_TABLE( + + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + + COLUMNS ( + + c1 integer PATH '$' + + ) + + ) jt(k) + + JOIN rpr_res_tb rpr_res_tb(k, y) USING (k)) j(k, m) + + WINDOW w AS (ORDER BY rpr_res_tj.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS c1 > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_tj_rt AS ' + || pg_get_viewdef('rpr_res_tj_v'::regclass, true) \gexec +CREATE VIEW rpr_res_tj_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_res_tj, + (JSON_TABLE( + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + COLUMNS ( + c1 integer PATH '$' + ) + ) jt(k) + JOIN rpr_res_tb rpr_res_tb(k, y) USING (k)) j(k, m) + WINDOW w AS (ORDER BY rpr_res_tj.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS c1 > 0); +SELECT pg_get_viewdef('rpr_res_tj_v'::regclass, true) + = pg_get_viewdef('rpr_res_tj_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_tj_v; + cnt +----- + 4 + 0 + 0 + 0 +(4 rows) + +SELECT * FROM rpr_res_tj_rt; + cnt +----- + 4 + 0 + 0 + 0 +(4 rows) + +SELECT pg_get_viewdef('rpr_res_tjr_v'::regclass, true); + pg_get_viewdef +---------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM (JSON_TABLE( + + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + + COLUMNS ( + + c1 integer PATH '$' + + ) + + ) jt(k) + + JOIN rpr_res_tb rpr_res_tb(k, y) USING (k)) j(k, m), + + rpr_res_tj + + WINDOW w AS (ORDER BY rpr_res_tj.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS c1 > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_tjr_rt AS ' + || pg_get_viewdef('rpr_res_tjr_v'::regclass, true) \gexec +CREATE VIEW rpr_res_tjr_rt AS SELECT count(*) OVER w AS cnt + FROM (JSON_TABLE( + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + COLUMNS ( + c1 integer PATH '$' + ) + ) jt(k) + JOIN rpr_res_tb rpr_res_tb(k, y) USING (k)) j(k, m), + rpr_res_tj + WINDOW w AS (ORDER BY rpr_res_tj.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS c1 > 0); +SELECT pg_get_viewdef('rpr_res_tjr_v'::regclass, true) + = pg_get_viewdef('rpr_res_tjr_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT pg_get_viewdef('rpr_res_tja_v'::regclass, true); + pg_get_viewdef +---------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_res_ti, + + (JSON_TABLE( + + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + + COLUMNS ( + + c1 integer PATH '$' + + ) + + ) jt(k) + + JOIN rpr_res_tb rpr_res_tb(k, y) USING (k)) j(k, c1) + + WINDOW w AS (ORDER BY rpr_res_ti.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS c1 > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_tja_rt AS ' + || pg_get_viewdef('rpr_res_tja_v'::regclass, true) \gexec +CREATE VIEW rpr_res_tja_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_res_ti, + (JSON_TABLE( + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + COLUMNS ( + c1 integer PATH '$' + ) + ) jt(k) + JOIN rpr_res_tb rpr_res_tb(k, y) USING (k)) j(k, c1) + WINDOW w AS (ORDER BY rpr_res_ti.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS c1 > 0); +SELECT pg_get_viewdef('rpr_res_tja_v'::regclass, true) + = pg_get_viewdef('rpr_res_tja_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_tja_v; + cnt +----- + 4 + 0 + 0 + 0 +(4 rows) + +SELECT * FROM rpr_res_tja_rt; + cnt +----- + 4 + 0 + 0 + 0 +(4 rows) + +SELECT pg_get_viewdef('rpr_res_tjb_v'::regclass, true); + pg_get_viewdef +------------------------------------------------------------------------------ + SELECT count(*) OVER w AS cnt + + FROM (rpr_res_tb rpr_res_tb(a, y) + + JOIN JSON_TABLE( + + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + + COLUMNS ( + + c1 integer PATH '$' + + ) + + ) jt(a) USING (a)) j(a, c1) + + WINDOW w AS (ORDER BY j.a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS c1 > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_tjb_rt AS ' + || pg_get_viewdef('rpr_res_tjb_v'::regclass, true) \gexec +CREATE VIEW rpr_res_tjb_rt AS SELECT count(*) OVER w AS cnt + FROM (rpr_res_tb rpr_res_tb(a, y) + JOIN JSON_TABLE( + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + COLUMNS ( + c1 integer PATH '$' + ) + ) jt(a) USING (a)) j(a, c1) + WINDOW w AS (ORDER BY j.a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS c1 > 0); +SELECT pg_get_viewdef('rpr_res_tjb_v'::regclass, true) + = pg_get_viewdef('rpr_res_tjb_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_tjb_v; + cnt +----- + 2 + 0 +(2 rows) + +SELECT * FROM rpr_res_tjb_rt; + cnt +----- + 2 + 0 +(2 rows) + +DROP VIEW rpr_res_tjb_rt, rpr_res_tjb_v; +DROP VIEW rpr_res_tja_rt, rpr_res_tja_v; +DROP VIEW rpr_res_tjr_rt, rpr_res_tjr_v; +DROP VIEW rpr_res_tj_rt, rpr_res_tj_v; +DROP TABLE rpr_res_tj, rpr_res_tb, rpr_res_ti; +-- The name a USING clause is given for an aliased join has to stay clear of +-- the names the join's alias list already carries, and of what a DEFINE +-- clause reads among them. The anonymous FULL JOIN takes the plain spelling +-- first, so the second USING has to move past both. +CREATE TABLE rpr_res_fa (x INT); +CREATE TABLE rpr_res_fb (x INT); +CREATE TABLE rpr_res_fc (x INT, y INT); +INSERT INTO rpr_res_fa VALUES (1); +INSERT INTO rpr_res_fb VALUES (1); +INSERT INTO rpr_res_fc VALUES (1, 3); +CREATE VIEW rpr_res_fa_v AS +SELECT count(*) OVER w AS cnt +FROM (rpr_res_fa FULL JOIN rpr_res_fb USING (x)), + ((rpr_res_fc JOIN rpr_res_fa t4 USING (x)) AS j(x, x_1) + JOIN rpr_res_fb t5 USING (x)) +WINDOW w AS (ORDER BY j.x + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS x_1 > 0); +SELECT pg_get_viewdef('rpr_res_fa_v'::regclass, true); + pg_get_viewdef +-------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_res_fa + + FULL JOIN rpr_res_fb USING (x), + + (rpr_res_fc rpr_res_fc(x_2, y) + + JOIN rpr_res_fa t4(x_2) USING (x_2)) j(x_2, x_1) + + JOIN rpr_res_fb t5(x_2) USING (x_2) + + WINDOW w AS (ORDER BY j.x_2 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS x_1 > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_fa_rt AS ' + || pg_get_viewdef('rpr_res_fa_v'::regclass, true) \gexec +CREATE VIEW rpr_res_fa_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_res_fa + FULL JOIN rpr_res_fb USING (x), + (rpr_res_fc rpr_res_fc(x_2, y) + JOIN rpr_res_fa t4(x_2) USING (x_2)) j(x_2, x_1) + JOIN rpr_res_fb t5(x_2) USING (x_2) + WINDOW w AS (ORDER BY j.x_2 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS x_1 > 0); +SELECT pg_get_viewdef('rpr_res_fa_v'::regclass, true) + = pg_get_viewdef('rpr_res_fa_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_fa_v; + cnt +----- + 1 +(1 row) + +SELECT * FROM rpr_res_fa_rt; + cnt +----- + 1 +(1 row) + +DROP VIEW rpr_res_fa_rt, rpr_res_fa_v; +DROP TABLE rpr_res_fa, rpr_res_fb, rpr_res_fc; +-- A TABLEFUNC that merges through an anonymous join, or that carries a +-- column alias list of its own, is no different: when a relation column is +-- renamed onto the TABLEFUNC's name, it is the TABLEFUNC column that moves, +-- and a third RTE that already spells the name it would have moved to is +-- kept clear as well. +CREATE TABLE rpr_res_tk (id INT, y INT); +INSERT INTO rpr_res_tk VALUES (1, 1), (2, 2); +CREATE TABLE rpr_res_th (x INT, z INT); +INSERT INTO rpr_res_th VALUES (1, 1), (2, 2); +CREATE TABLE rpr_res_ta (id INT, c1 INT); +INSERT INTO rpr_res_ta VALUES (1, 1), (2, 2); +CREATE TABLE rpr_res_ts (id INT, s INT); +INSERT INTO rpr_res_ts VALUES (1, 1), (2, 2); +CREATE TABLE rpr_res_to (c1_1 INT); +INSERT INTO rpr_res_to VALUES (9); +CREATE VIEW rpr_res_tk_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_tk, + (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (x int PATH '$')) AS jt + JOIN rpr_res_th USING (x)) j +WINDOW w AS (ORDER BY rpr_res_tk.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS y > 0); +CREATE VIEW rpr_res_ta_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_ta, + JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jx(a) +WINDOW w AS (ORDER BY rpr_res_ta.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS c1 > 0); +CREATE VIEW rpr_res_ts_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_ts, rpr_res_to, + JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jx +WINDOW w AS (ORDER BY rpr_res_ts.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS s > 0); +ALTER TABLE rpr_res_tk RENAME y TO x; +ALTER TABLE rpr_res_ts RENAME s TO c1; +SELECT pg_get_viewdef('rpr_res_tk_v'::regclass, true); + pg_get_viewdef +---------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_res_tk, + + (JSON_TABLE( + + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + + COLUMNS ( + + x integer PATH '$' + + ) + + ) jt(x_1) + + JOIN rpr_res_th rpr_res_th(x_1, z) USING (x_1)) j + + WINDOW w AS (ORDER BY rpr_res_tk.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS x > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_tk_rt AS ' + || pg_get_viewdef('rpr_res_tk_v'::regclass, true) \gexec +CREATE VIEW rpr_res_tk_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_res_tk, + (JSON_TABLE( + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + COLUMNS ( + x integer PATH '$' + ) + ) jt(x_1) + JOIN rpr_res_th rpr_res_th(x_1, z) USING (x_1)) j + WINDOW w AS (ORDER BY rpr_res_tk.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS x > 0); +SELECT pg_get_viewdef('rpr_res_tk_v'::regclass, true) + = pg_get_viewdef('rpr_res_tk_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_tk_v; + cnt +----- + 4 + 0 + 0 + 0 +(4 rows) + +SELECT * FROM rpr_res_tk_rt; + cnt +----- + 4 + 0 + 0 + 0 +(4 rows) + +SELECT pg_get_viewdef('rpr_res_ta_v'::regclass, true); + pg_get_viewdef +---------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_res_ta, + + JSON_TABLE( + + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + + COLUMNS ( + + c1 integer PATH '$' + + ) + + ) jx(a) + + WINDOW w AS (ORDER BY rpr_res_ta.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS c1 > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_ta_rt AS ' + || pg_get_viewdef('rpr_res_ta_v'::regclass, true) \gexec +CREATE VIEW rpr_res_ta_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_res_ta, + JSON_TABLE( + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + COLUMNS ( + c1 integer PATH '$' + ) + ) jx(a) + WINDOW w AS (ORDER BY rpr_res_ta.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS c1 > 0); +SELECT pg_get_viewdef('rpr_res_ta_v'::regclass, true) + = pg_get_viewdef('rpr_res_ta_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_ta_v; + cnt +----- + 4 + 0 + 0 + 0 +(4 rows) + +SELECT * FROM rpr_res_ta_rt; + cnt +----- + 4 + 0 + 0 + 0 +(4 rows) + +SELECT pg_get_viewdef('rpr_res_ts_v'::regclass, true); + pg_get_viewdef +---------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_res_ts, + + rpr_res_to, + + JSON_TABLE( + + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + + COLUMNS ( + + c1 integer PATH '$' + + ) + + ) jx(c1_1) + + WINDOW w AS (ORDER BY rpr_res_ts.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS c1 > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_ts_rt AS ' + || pg_get_viewdef('rpr_res_ts_v'::regclass, true) \gexec +CREATE VIEW rpr_res_ts_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_res_ts, + rpr_res_to, + JSON_TABLE( + '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + COLUMNS ( + c1 integer PATH '$' + ) + ) jx(c1_1) + WINDOW w AS (ORDER BY rpr_res_ts.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS c1 > 0); +SELECT pg_get_viewdef('rpr_res_ts_v'::regclass, true) + = pg_get_viewdef('rpr_res_ts_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_ts_v; + cnt +----- + 4 + 0 + 0 + 0 +(4 rows) + +SELECT * FROM rpr_res_ts_rt; + cnt +----- + 4 + 0 + 0 + 0 +(4 rows) + +DROP VIEW rpr_res_ts_rt, rpr_res_ts_v; +DROP VIEW rpr_res_ta_rt, rpr_res_ta_v; +DROP VIEW rpr_res_tk_rt, rpr_res_tk_v; +DROP TABLE rpr_res_tk, rpr_res_th, rpr_res_ta, rpr_res_ts, rpr_res_to; +-- A merged column the DEFINE clause reads is named by the DEFINE clause, not +-- by the USING clause: the name settled for it is the one the merge adopts. +-- So a join whose alias list renames the merged column prints the same text +-- with the DEFINE clause as without it, and a merge two joins deep is reached +-- through the input the reference resolves to. A column that turns up later +-- under the same name elsewhere is the one that moves. +CREATE TABLE rpr_res_ma (x INT, y INT); +CREATE TABLE rpr_res_mb (x INT, z INT); +CREATE TABLE rpr_res_mc (x INT, r INT); +CREATE TABLE rpr_res_mo (xx INT); +INSERT INTO rpr_res_ma VALUES (1, 1), (2, 2); +INSERT INTO rpr_res_mb VALUES (1, 1), (2, 2); +INSERT INTO rpr_res_mc VALUES (1, 1), (2, 2); +INSERT INTO rpr_res_mo VALUES (1); +CREATE VIEW rpr_res_ma_v AS +SELECT count(*) OVER w AS cnt +FROM (rpr_res_ma JOIN rpr_res_mb USING (x)) AS j(x1, y, z) +WINDOW w AS (ORDER BY j.y + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS x1 > 0); +CREATE VIEW rpr_res_ma_nodef AS +SELECT count(*) OVER w AS cnt +FROM (rpr_res_ma JOIN rpr_res_mb USING (x)) AS j(x1, y, z) +WINDOW w AS (ORDER BY j.y + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING); +CREATE VIEW rpr_res_mm_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_mo, (rpr_res_ma JOIN rpr_res_mb USING (x)) JOIN rpr_res_mc USING (x) +WINDOW w AS (ORDER BY rpr_res_ma.y + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS x > 0); +SELECT pg_get_viewdef('rpr_res_ma_v'::regclass, true); + pg_get_viewdef +------------------------------------------------------------------------------ + SELECT count(*) OVER w AS cnt + + FROM (rpr_res_ma rpr_res_ma(x1, y) + + JOIN rpr_res_mb rpr_res_mb(x1, z) USING (x1)) j + + WINDOW w AS (ORDER BY j.y ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS x1 > 0); +(1 row) + +SELECT pg_get_viewdef('rpr_res_ma_nodef'::regclass, true); + pg_get_viewdef +-------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM (rpr_res_ma rpr_res_ma(x1, y) + + JOIN rpr_res_mb rpr_res_mb(x1, z) USING (x1)) j + + WINDOW w AS (ORDER BY j.y ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING); +(1 row) + +SELECT 'CREATE VIEW rpr_res_ma_rt AS ' + || pg_get_viewdef('rpr_res_ma_v'::regclass, true) \gexec +CREATE VIEW rpr_res_ma_rt AS SELECT count(*) OVER w AS cnt + FROM (rpr_res_ma rpr_res_ma(x1, y) + JOIN rpr_res_mb rpr_res_mb(x1, z) USING (x1)) j + WINDOW w AS (ORDER BY j.y ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS x1 > 0); +SELECT pg_get_viewdef('rpr_res_ma_v'::regclass, true) + = pg_get_viewdef('rpr_res_ma_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_ma_v; + cnt +----- + 2 + 0 +(2 rows) + +SELECT * FROM rpr_res_ma_rt; + cnt +----- + 2 + 0 +(2 rows) + +SELECT pg_get_viewdef('rpr_res_mm_v'::regclass, true); + pg_get_viewdef +--------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_res_mo, + + rpr_res_ma + + JOIN rpr_res_mb USING (x) + + JOIN rpr_res_mc USING (x) + + WINDOW w AS (ORDER BY rpr_res_ma.y ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS x > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_mm_rt AS ' + || pg_get_viewdef('rpr_res_mm_v'::regclass, true) \gexec +CREATE VIEW rpr_res_mm_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_res_mo, + rpr_res_ma + JOIN rpr_res_mb USING (x) + JOIN rpr_res_mc USING (x) + WINDOW w AS (ORDER BY rpr_res_ma.y ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS x > 0); +SELECT pg_get_viewdef('rpr_res_mm_v'::regclass, true) + = pg_get_viewdef('rpr_res_mm_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_mm_v; + cnt +----- + 2 + 0 +(2 rows) + +SELECT * FROM rpr_res_mm_rt; + cnt +----- + 2 + 0 +(2 rows) + +-- the name turns up elsewhere only now +DROP VIEW rpr_res_mm_rt; +ALTER TABLE rpr_res_mo RENAME xx TO x; +SELECT pg_get_viewdef('rpr_res_mm_v'::regclass, true); + pg_get_viewdef +--------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_res_mo rpr_res_mo(x_1), + + rpr_res_ma + + JOIN rpr_res_mb USING (x) + + JOIN rpr_res_mc USING (x) + + WINDOW w AS (ORDER BY rpr_res_ma.y ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS x > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_res_mm_rt AS ' + || pg_get_viewdef('rpr_res_mm_v'::regclass, true) \gexec +CREATE VIEW rpr_res_mm_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_res_mo rpr_res_mo(x_1), + rpr_res_ma + JOIN rpr_res_mb USING (x) + JOIN rpr_res_mc USING (x) + WINDOW w AS (ORDER BY rpr_res_ma.y ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS x > 0); +SELECT pg_get_viewdef('rpr_res_mm_v'::regclass, true) + = pg_get_viewdef('rpr_res_mm_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_res_mm_v; + cnt +----- + 2 + 0 +(2 rows) + +SELECT * FROM rpr_res_mm_rt; + cnt +----- + 2 + 0 +(2 rows) + +DROP VIEW rpr_res_mm_rt, rpr_res_mm_v; +DROP VIEW rpr_res_ma_rt, rpr_res_ma_nodef, rpr_res_ma_v; +DROP TABLE rpr_res_ma, rpr_res_mb, rpr_res_mc, rpr_res_mo; +-- Deparsing a query whose DEFINE clause reads the grouping step expands the +-- clause's GROUP Vars into the grouping expressions, and over a column merged +-- by a FULL JOIN USING that expands to the COALESCE the parser built for the +-- merge. Printed, both arms come out spelled the same -- a DEFINE clause +-- carries no qualifier -- so COALESCE(id, id) says nothing about where either +-- came from, and re-parsing nests one merged column inside another. The join +-- RTE still holds what it built, so the expansion is folded back into the +-- merged column itself. +CREATE TABLE rpr_cds_l (id INT PRIMARY KEY, val INT); +CREATE TABLE rpr_cds_r (id INT, val INT); +CREATE TABLE rpr_cds_o (k INT); +INSERT INTO rpr_cds_l VALUES (1, 1), (2, 2); +INSERT INTO rpr_cds_r VALUES (1, 1), (3, 3); +INSERT INTO rpr_cds_o VALUES (1), (2); +CREATE VIEW rpr_cds_v AS +SELECT COALESCE(rpr_cds_l.id, rpr_cds_r.id) + 1 AS idp1, count(*) OVER w AS cnt +FROM rpr_cds_l FULL JOIN rpr_cds_r USING (id) +GROUP BY COALESCE(rpr_cds_l.id, rpr_cds_r.id) + 1 +WINDOW w AS (ORDER BY COALESCE(rpr_cds_l.id, rpr_cds_r.id) + 1 + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS id + 1 > 0); +SELECT pg_get_viewdef('rpr_cds_v'::regclass, true); + pg_get_viewdef +--------------------------------------------------------------------------------------------------------------------- + SELECT COALESCE(rpr_cds_l.id, rpr_cds_r.id) + 1 AS idp1, + + count(*) OVER w AS cnt + + FROM rpr_cds_l + + FULL JOIN rpr_cds_r USING (id) + + GROUP BY (COALESCE(rpr_cds_l.id, rpr_cds_r.id) + 1) + + WINDOW w AS (ORDER BY (COALESCE(rpr_cds_l.id, rpr_cds_r.id) + 1) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS (id + 1) > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_cds_rt AS ' + || pg_get_viewdef('rpr_cds_v'::regclass, true) \gexec +CREATE VIEW rpr_cds_rt AS SELECT COALESCE(rpr_cds_l.id, rpr_cds_r.id) + 1 AS idp1, + count(*) OVER w AS cnt + FROM rpr_cds_l + FULL JOIN rpr_cds_r USING (id) + GROUP BY (COALESCE(rpr_cds_l.id, rpr_cds_r.id) + 1) + WINDOW w AS (ORDER BY (COALESCE(rpr_cds_l.id, rpr_cds_r.id) + 1) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS (id + 1) > 0); +SELECT pg_get_viewdef('rpr_cds_v'::regclass, true) + = pg_get_viewdef('rpr_cds_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_cds_v ORDER BY idp1; + idp1 | cnt +------+----- + 2 | 3 + 3 | 0 + 4 | 0 +(3 rows) + +SELECT * FROM rpr_cds_rt ORDER BY idp1; + idp1 | cnt +------+----- + 2 | 3 + 3 | 0 + 4 | 0 +(3 rows) + +-- An outer join above the merge marks the copy the grouping expression +-- carries and not the copy the join RTE keeps. Neither mark reaches the +-- printed text, so the two still name one column. +CREATE VIEW rpr_cds_null_v AS +SELECT COALESCE(l.id, r.id) + 1 AS idp1, count(*) OVER w AS cnt +FROM rpr_cds_o LEFT JOIN (rpr_cds_l l FULL JOIN rpr_cds_r r USING (id)) ON true +GROUP BY COALESCE(l.id, r.id) + 1 +WINDOW w AS (ORDER BY COALESCE(l.id, r.id) + 1 + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS id + 1 > 0); +SELECT pg_get_viewdef('rpr_cds_null_v'::regclass, true); + pg_get_viewdef +----------------------------------------------------------------------------------------------------- + SELECT COALESCE(l.id, r.id) + 1 AS idp1, + + count(*) OVER w AS cnt + + FROM rpr_cds_o + + LEFT JOIN (rpr_cds_l l + + FULL JOIN rpr_cds_r r USING (id)) ON true + + GROUP BY (COALESCE(l.id, r.id) + 1) + + WINDOW w AS (ORDER BY (COALESCE(l.id, r.id) + 1) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS (id + 1) > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_cds_null_rt AS ' + || pg_get_viewdef('rpr_cds_null_v'::regclass, true) \gexec +CREATE VIEW rpr_cds_null_rt AS SELECT COALESCE(l.id, r.id) + 1 AS idp1, + count(*) OVER w AS cnt + FROM rpr_cds_o + LEFT JOIN (rpr_cds_l l + FULL JOIN rpr_cds_r r USING (id)) ON true + GROUP BY (COALESCE(l.id, r.id) + 1) + WINDOW w AS (ORDER BY (COALESCE(l.id, r.id) + 1) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS (id + 1) > 0); +SELECT pg_get_viewdef('rpr_cds_null_v'::regclass, true) + = pg_get_viewdef('rpr_cds_null_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_cds_null_v ORDER BY idp1; + idp1 | cnt +------+----- + 2 | 3 + 3 | 0 + 4 | 0 +(3 rows) + +SELECT * FROM rpr_cds_null_rt ORDER BY idp1; + idp1 | cnt +------+----- + 2 | 3 + 3 | 0 + 4 | 0 +(3 rows) + +-- and the same nesting where the join above nulls nothing, which the exact +-- match takes +CREATE VIEW rpr_cds_inner_v AS +SELECT COALESCE(l.id, r.id) + 1 AS idp1, count(*) OVER w AS cnt +FROM (rpr_cds_l l FULL JOIN rpr_cds_r r USING (id)) JOIN rpr_cds_o ON true +GROUP BY COALESCE(l.id, r.id) + 1 +WINDOW w AS (ORDER BY COALESCE(l.id, r.id) + 1 + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS id + 1 > 0); +SELECT pg_get_viewdef('rpr_cds_inner_v'::regclass, true); + pg_get_viewdef +----------------------------------------------------------------------------------------------------- + SELECT COALESCE(l.id, r.id) + 1 AS idp1, + + count(*) OVER w AS cnt + + FROM rpr_cds_l l + + FULL JOIN rpr_cds_r r USING (id) + + JOIN rpr_cds_o ON true + + GROUP BY (COALESCE(l.id, r.id) + 1) + + WINDOW w AS (ORDER BY (COALESCE(l.id, r.id) + 1) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS (id + 1) > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_cds_inner_rt AS ' + || pg_get_viewdef('rpr_cds_inner_v'::regclass, true) \gexec +CREATE VIEW rpr_cds_inner_rt AS SELECT COALESCE(l.id, r.id) + 1 AS idp1, + count(*) OVER w AS cnt + FROM rpr_cds_l l + FULL JOIN rpr_cds_r r USING (id) + JOIN rpr_cds_o ON true + GROUP BY (COALESCE(l.id, r.id) + 1) + WINDOW w AS (ORDER BY (COALESCE(l.id, r.id) + 1) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS (id + 1) > 0); +SELECT pg_get_viewdef('rpr_cds_inner_v'::regclass, true) + = pg_get_viewdef('rpr_cds_inner_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_cds_inner_v ORDER BY idp1; + idp1 | cnt +------+----- + 2 | 3 + 3 | 0 + 4 | 0 +(3 rows) + +SELECT * FROM rpr_cds_inner_rt ORDER BY idp1; + idp1 | cnt +------+----- + 2 | 3 + 3 | 0 + 4 | 0 +(3 rows) + +-- A merge built over another merge -- a FULL JOIN USING above a FULL JOIN +-- USING -- expands to a COALESCE over a COALESCE. The inner one is folded +-- first, so that the outer one is seen whole and folds in turn; folded +-- inside out, it re-parses to itself rather than to twice the nesting. +CREATE VIEW rpr_cds_nest_v AS +SELECT count(*) OVER w AS cnt +FROM (rpr_cds_l FULL JOIN rpr_cds_r USING (id)) FULL JOIN rpr_cds_r t USING (id) +GROUP BY rpr_cds_l.id, rpr_cds_r.id, t.id +WINDOW w AS (ORDER BY rpr_cds_l.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS id > 0); +SELECT pg_get_viewdef('rpr_cds_nest_v'::regclass, true); + pg_get_viewdef +--------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_cds_l + + FULL JOIN rpr_cds_r USING (id) + + FULL JOIN rpr_cds_r t USING (id) + + GROUP BY rpr_cds_l.id, rpr_cds_r.id, t.id + + WINDOW w AS (ORDER BY rpr_cds_l.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS id > 0); +(1 row) + +SELECT 'CREATE VIEW rpr_cds_nest_rt AS ' + || pg_get_viewdef('rpr_cds_nest_v'::regclass, true) \gexec +CREATE VIEW rpr_cds_nest_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_cds_l + FULL JOIN rpr_cds_r USING (id) + FULL JOIN rpr_cds_r t USING (id) + GROUP BY rpr_cds_l.id, rpr_cds_r.id, t.id + WINDOW w AS (ORDER BY rpr_cds_l.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS id > 0); +SELECT pg_get_viewdef('rpr_cds_nest_v'::regclass, true) + = pg_get_viewdef('rpr_cds_nest_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_cds_nest_v; + cnt +----- + 3 + 0 + 0 +(3 rows) + +SELECT * FROM rpr_cds_nest_rt; + cnt +----- + 3 + 0 + 0 +(3 rows) + +DROP VIEW rpr_cds_nest_rt, rpr_cds_nest_v; +DROP VIEW rpr_cds_inner_rt, rpr_cds_inner_v; +DROP VIEW rpr_cds_null_rt, rpr_cds_null_v; +DROP VIEW rpr_cds_rt, rpr_cds_v; +DROP TABLE rpr_cds_l, rpr_cds_r, rpr_cds_o; +-- A rule deparses with varprefix on no matter what its action query looks +-- like, the range table always holding *OLD* and *NEW*, so a DEFINE clause in +-- one would be printed with qualifiers that the parser rejects outright. +-- get_rule_define() turns the prefix off for the clause, and without that a +-- rule holding a row pattern query could not be restored at all -- not even a +-- single-table one, which is what makes this its own case and not the view's. +CREATE TABLE rpr_rule_t (id INT, val INT); +CREATE TABLE rpr_rule_log (id INT, cnt BIGINT); +CREATE RULE rpr_rule_r AS ON INSERT TO rpr_rule_t DO ALSO + INSERT INTO rpr_rule_log + SELECT id, count(*) OVER w FROM rpr_rule_t + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS val > 0); +SELECT pg_get_ruledef(oid, true) FROM pg_rewrite WHERE rulename = 'rpr_rule_r'; + pg_get_ruledef +------------------------------------------------------------------------------------------------ + CREATE RULE rpr_rule_r AS + + ON INSERT TO rpr_rule_t DO INSERT INTO rpr_rule_log (id, cnt) SELECT rpr_rule_t.id, + + count(*) OVER w AS count + + FROM rpr_rule_t + + WINDOW w AS (ORDER BY rpr_rule_t.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS val > 0); +(1 row) + +-- and that text is what has to reparse +CREATE TABLE rpr_rule_saved AS + SELECT pg_get_ruledef(oid, true) AS def + FROM pg_rewrite WHERE rulename = 'rpr_rule_r'; +DROP RULE rpr_rule_r ON rpr_rule_t; +SELECT def FROM rpr_rule_saved \gexec +CREATE RULE rpr_rule_r AS + ON INSERT TO rpr_rule_t DO INSERT INTO rpr_rule_log (id, cnt) SELECT rpr_rule_t.id, + count(*) OVER w AS count + FROM rpr_rule_t + WINDOW w AS (ORDER BY rpr_rule_t.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS val > 0); +SELECT (SELECT def FROM rpr_rule_saved) = pg_get_ruledef(oid, true) AS round_trips + FROM pg_rewrite WHERE rulename = 'rpr_rule_r'; + round_trips +------------- + t +(1 row) + +-- the restored rule still fires. A rule action is run against the rows the +-- statement supplies as well as the table, so a two-row INSERT gives the +-- window four rows to order by two distinct ids; sort the result on both +-- columns, the pairs within an id being interchangeable. +INSERT INTO rpr_rule_t VALUES (1, 1), (2, 2); +SELECT * FROM rpr_rule_log ORDER BY id, cnt; + id | cnt +----+----- + 1 | 0 + 1 | 4 + 2 | 0 + 2 | 0 +(4 rows) + +DROP TABLE rpr_rule_saved; +DROP TABLE rpr_rule_t, rpr_rule_log; +-- A relation alias that happens to spell a pattern variable is the other way +-- the prefix goes wrong. Printed as up.price it does not come back as a +-- range variable qualifier, which is merely rejected, but as a pattern +-- variable one, which is rejected by a different rule and with a different +-- message. Two RTEs are what turns the prefix on. +CREATE TABLE rpr_pvar_a (id INT, price INT); +CREATE TABLE rpr_pvar_b (id INT); +INSERT INTO rpr_pvar_a VALUES (1, 10), (2, 20), (3, 5); +INSERT INTO rpr_pvar_b VALUES (1), (2), (3); +CREATE VIEW rpr_pvar_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_pvar_a up, rpr_pvar_b +WHERE up.id = rpr_pvar_b.id +WINDOW w AS (ORDER BY up.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (up+) + DEFINE up AS price > 0); +SELECT pg_get_viewdef('rpr_pvar_v'::regclass, true); + pg_get_viewdef +-------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_pvar_a up, + + rpr_pvar_b + + WHERE up.id = rpr_pvar_b.id + + WINDOW w AS (ORDER BY up.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 'CREATE VIEW rpr_pvar_rt AS ' + || pg_get_viewdef('rpr_pvar_v'::regclass, true) \gexec +CREATE VIEW rpr_pvar_rt AS SELECT count(*) OVER w AS cnt + FROM rpr_pvar_a up, + rpr_pvar_b + WHERE up.id = rpr_pvar_b.id + WINDOW w AS (ORDER BY up.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (up+) + DEFINE + up AS price > 0); +SELECT pg_get_viewdef('rpr_pvar_v'::regclass, true) + = pg_get_viewdef('rpr_pvar_rt'::regclass, true) AS round_trips; + round_trips +------------- + t +(1 row) + +SELECT * FROM rpr_pvar_v; + cnt +----- + 3 + 0 + 0 +(3 rows) + +SELECT * FROM rpr_pvar_rt; + cnt +----- + 3 + 0 + 0 +(3 rows) + +DROP VIEW rpr_pvar_rt, rpr_pvar_v; +DROP TABLE rpr_pvar_a, rpr_pvar_b; +-- The same query written fresh is rejected, since nothing pins the name for +-- it. Pinning is what lets the stored definition above still reparse. +SELECT j1.id, count(*) OVER w AS cnt +FROM rpr_pin_j1 j1 JOIN rpr_pin_j2 j2 ON j1.id = j2.id +WINDOW w AS (ORDER BY j1.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS price > 0); +ERROR: column reference "price" is ambiguous +LINE 6: DEFINE A AS price > 0); + ^ -- Materialized view (if supported) CREATE TABLE rpr_mview (id INT, val INT); INSERT INTO rpr_mview VALUES (1, 10), (2, 20), (3, 30); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 4a8cc759d7b..90e6f5dfc56 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -3777,6 +3777,60 @@ INSERT INTO hats VALUES ('h7', 'black') RETURNING *; DROP RULE hat_confsel ON hats; drop table hats; drop table hat_data; +-- An unnamed FULL JOIN USING makes USING names unique query-wide, but an RTE +-- outside the FROM clause has no alias list to carry a renamed column. +create table rule_uniq1 (x int, y int); +create table rule_uniq2 (x int, z int); +create table rule_uniq3 (x int, w int); +create table rule_uniq_log (x int); +create rule rule_uniq_r as on update to rule_uniq1 do also + insert into rule_uniq_log + select g1.y from rule_uniq1 g1, rule_uniq2 full join rule_uniq3 using (x) + where g1.x <> new.x; +create rule rule_uniq_u as on update to rule_uniq1 do also + update rule_uniq_log set x = 1 + from rule_uniq2 full join rule_uniq3 using (x) + where rule_uniq_log.x = 0; +-- g1 is in the FROM clause and is re-aliased to x_1; new and the update +-- target are not, so they keep x +select rulename, definition from pg_rules where tablename = 'rule_uniq1' + order by rulename; + rulename | definition +-------------+----------------------------------------------------------------------------------- + rule_uniq_r | CREATE RULE rule_uniq_r AS + + | ON UPDATE TO public.rule_uniq1 DO INSERT INTO rule_uniq_log (x) SELECT g1.y+ + | FROM rule_uniq1 g1(x_1, y), + + | (rule_uniq2 + + | FULL JOIN rule_uniq3 USING (x)) + + | WHERE (g1.x_1 <> new.x); + rule_uniq_u | CREATE RULE rule_uniq_u AS + + | ON UPDATE TO public.rule_uniq1 DO UPDATE rule_uniq_log SET x = 1 + + | FROM (rule_uniq2 + + | FULL JOIN rule_uniq3 USING (x)) + + | WHERE (rule_uniq_log.x = 0); +(2 rows) + +drop table rule_uniq1, rule_uniq2, rule_uniq3, rule_uniq_log; +-- The subquery an INSERT ... SELECT reads from is outside the FROM clause +-- too, but it is printed, so its unnamed columns still need unique names. +-- LIMIT keeps the subquery from being pulled up, and the unfilled column +-- makes the plan project through a subquery scan. +create table rule_uniq_ins (a int, b int, c text, d int); +explain (verbose, costs off) + insert into rule_uniq_ins (a, b, c) + select a + 1, b + 1, c || c from rule_uniq_ins limit 2; + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------- + Insert on public.rule_uniq_ins + -> Subquery Scan on unnamed_subquery + Output: unnamed_subquery."?column?", unnamed_subquery."?column?_1", unnamed_subquery."?column?_2", NULL::integer + -> Limit + Output: ((rule_uniq_ins_1.a + 1)), ((rule_uniq_ins_1.b + 1)), ((rule_uniq_ins_1.c || rule_uniq_ins_1.c)) + -> Seq Scan on public.rule_uniq_ins rule_uniq_ins_1 + Output: (rule_uniq_ins_1.a + 1), (rule_uniq_ins_1.b + 1), (rule_uniq_ins_1.c || rule_uniq_ins_1.c) +(7 rows) + +drop table rule_uniq_ins; -- test for pg_get_functiondef properly regurgitating SET parameters -- Note that the function is kept around to stress pg_dump. CREATE FUNCTION func_with_set_params() RETURNS integer diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql index 75b8cd5d0fb..e9fb187d616 100644 --- a/src/test/regress/sql/create_view.sql +++ b/src/test/regress/sql/create_view.sql @@ -409,6 +409,171 @@ select pg_get_viewdef('view_of_joins_2b', true); select pg_get_viewdef('view_of_joins_2c', true); select pg_get_viewdef('view_of_joins_2d', true); +-- A TABLEFUNC RTE names its columns in the clause that produces them, but it +-- accepts a column alias list like any other RTE, and that list is where a +-- rename of one of its columns has to be printed: the ON clause below refers +-- to the column by the name the query sees, and without the list the two +-- would not agree. The anonymous FULL JOIN is what forces USING names to be +-- unique query-wide, which is what pushes the JSON_TABLE column aside. +create table tblnr (m int); +create table tblnu (x int, m int); +create table tblnl (x int); +create table tblnm (x int); +create table tblnw (a int, c int, d int); +create table tblnv (c int); + +create view view_of_unrenamable as +select j.m +from (tblnl full join tblnm using (x)), + (json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt + join tblnr on jt.x > 0) j; + +select pg_get_viewdef('view_of_unrenamable', true); + +-- and that text is what has to reparse +select 'create view view_of_unrenamable_2 as ' + || pg_get_viewdef('view_of_unrenamable', true) \gexec +select pg_get_viewdef('view_of_unrenamable', true) + = pg_get_viewdef('view_of_unrenamable_2', true) as round_trips; + +-- The name of a merged column is the other place a rename lands, and it lands +-- on both sides at once: whatever is picked, each input has to answer to it, +-- the TABLEFUNC through its alias list like the table through its own. +create view view_of_unrenamable_using as +select j.m +from (tblnl full join tblnm using (x)), + (json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt + join tblnu using (x)) j; + +select pg_get_viewdef('view_of_unrenamable_using', true); + +select 'create view view_of_unrenamable_using_2 as ' + || pg_get_viewdef('view_of_unrenamable_using', true) \gexec +select pg_get_viewdef('view_of_unrenamable_using', true) + = pg_get_viewdef('view_of_unrenamable_using_2', true) as round_trips; + +-- The far side of an INNER or LEFT JOIN is as much a side as the near one: +-- the merged column's expression names only the near input, but the rename +-- reaches both. +create view view_of_unrenamable_right as +select j.m +from (tblnl full join tblnm using (x)), + ((tblnu join json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt + using (x)) full join tblnm t2 using (x)) j; + +select pg_get_viewdef('view_of_unrenamable_right', true); +select 'create view view_of_unrenamable_right_2 as ' + || pg_get_viewdef('view_of_unrenamable_right', true) \gexec +select pg_get_viewdef('view_of_unrenamable_right', true) + = pg_get_viewdef('view_of_unrenamable_right_2', true) as round_trips; + +create view view_of_unrenamable_left as +select j.m +from (tblnl full join tblnm using (x)), + ((tblnu left join json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt + using (x)) full join tblnm t2 using (x)) j; + +select pg_get_viewdef('view_of_unrenamable_left', true); +select 'create view view_of_unrenamable_left_2 as ' + || pg_get_viewdef('view_of_unrenamable_left', true) \gexec +select pg_get_viewdef('view_of_unrenamable_left', true) + = pg_get_viewdef('view_of_unrenamable_left_2', true) as round_trips; + +-- An aliased join hides its inputs, and a USING name above it is taken by +-- the join's own column: the join renames it on its alias list, and the +-- TABLEFUNC underneath, being held to the query-wide USING names as well, +-- renames its column on its own list. +create view view_of_unrenamable_hidden as +select j.m +from (tblnl full join tblnm using (x)), + ((json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt + join tblnr on true) j full join tblnm t2 using (x)); + +select pg_get_viewdef('view_of_unrenamable_hidden', true); +select 'create view view_of_unrenamable_hidden_2 as ' + || pg_get_viewdef('view_of_unrenamable_hidden', true) \gexec +select pg_get_viewdef('view_of_unrenamable_hidden', true) + = pg_get_viewdef('view_of_unrenamable_hidden_2', true) as round_trips; + +-- A name a parent join pushes down onto a merged column has to be unique in +-- the input it lands in, whatever the USING clause underneath spells. +create view view_of_unrenamable_pushed as +select j2.d +from (tblnv join (tblnw join json_table(jsonb '[1]', '$[*]' columns (a int path '$')) x + using (a)) using (c)) as j2(a); + +select pg_get_viewdef('view_of_unrenamable_pushed', true); +select 'create view view_of_unrenamable_pushed_2 as ' + || pg_get_viewdef('view_of_unrenamable_pushed', true) \gexec +select pg_get_viewdef('view_of_unrenamable_pushed', true) + = pg_get_viewdef('view_of_unrenamable_pushed_2', true) as round_trips; + +-- Two anonymous FULL JOINs merging the same name through a TABLEFUNC each +-- still get distinct names. +create view view_of_unrenamable_twice as +select count(*) as n +from (tblnl full join json_table(jsonb '[1]', '$[*]' columns (x int path '$')) jt1 + using (x)), + (tblnm full join json_table(jsonb '[1]', '$[*]' columns (x int path '$')) jt2 + using (x)); + +select pg_get_viewdef('view_of_unrenamable_twice', true); +select 'create view view_of_unrenamable_twice_2 as ' + || pg_get_viewdef('view_of_unrenamable_twice', true) \gexec +select pg_get_viewdef('view_of_unrenamable_twice', true) + = pg_get_viewdef('view_of_unrenamable_twice_2', true) as round_trips; + +-- and a column alias list the user wrote on a TABLEFUNC is part of the query +create view view_of_tablefunc_alias as +select t.a from json_table(jsonb '[1]', '$[*]' columns (c int path '$')) as t(a); + +select pg_get_viewdef('view_of_tablefunc_alias', true); +select 'create view view_of_tablefunc_alias_2 as ' + || pg_get_viewdef('view_of_tablefunc_alias', true) \gexec +select pg_get_viewdef('view_of_tablefunc_alias', true) + = pg_get_viewdef('view_of_tablefunc_alias_2', true) as round_trips; + +drop view view_of_tablefunc_alias_2, view_of_tablefunc_alias; +drop view view_of_unrenamable_twice_2, view_of_unrenamable_twice; +drop view view_of_unrenamable_pushed_2, view_of_unrenamable_pushed; +drop view view_of_unrenamable_hidden_2, view_of_unrenamable_hidden; +drop view view_of_unrenamable_left_2, view_of_unrenamable_left; +drop view view_of_unrenamable_right_2, view_of_unrenamable_right; +drop view view_of_unrenamable_using_2, view_of_unrenamable_using; +drop view view_of_unrenamable_2, view_of_unrenamable; +drop table tblnr, tblnu, tblnl, tblnm, tblnw, tblnv; + +-- The columns a function's result type grows after the view is made are +-- columns the RTE has now, and the alias list being positional they are +-- printed in full: an aliased join above lays its own list over its inputs' +-- lists end to end, so a grown column left off would shift the input after +-- it, and a reference into that input would silently land on another column. +create table tblfc (a int, z int); +insert into tblfc values (1, 5); +create function tblfc_f() returns setof tblfc language sql + as $$ select * from tblfc $$; +create table tblfr (z int, val int); +insert into tblfr values (1, 7); + +create view view_of_grown_input as +select j.a, j.val from (tblfc_f() f join tblfr on true) j; + +select * from view_of_grown_input; + +alter table tblfc add column val int, add column spare int; + +select pg_get_viewdef('view_of_grown_input', true); +select 'create view view_of_grown_input_2 as ' + || pg_get_viewdef('view_of_grown_input', true) \gexec +select pg_get_viewdef('view_of_grown_input', true) + = pg_get_viewdef('view_of_grown_input_2', true) as round_trips; +select * from view_of_grown_input; +select * from view_of_grown_input_2; + +drop view view_of_grown_input_2, view_of_grown_input; +drop function tblfc_f(); +drop table tblfc, tblfr; + -- Test view decompilation in the face of column addition/deletion/renaming create table tt2 (a int, b int, c int); diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index 7c3c9f54cd1..9fc8f9cd0ac 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -2588,47 +2588,1106 @@ 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; +-- A DEFINE clause can only name a column without a qualifier, so the name has +-- to resolve exactly as printed. When another relation of the query acquires +-- a column of that name, the deparser pushes the newcomer aside with a column +-- alias list, the same way it protects a column merged by USING. + +CREATE TABLE rpr_pin (id INT, val INT); +CREATE TABLE rpr_pin_other (id INT); +INSERT INTO rpr_pin VALUES (1, 10), (2, 20), (3, 15); +INSERT INTO rpr_pin_other VALUES (1), (2), (3); + +CREATE VIEW rpr_pin_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_pin, rpr_pin_other +WHERE rpr_pin.id = rpr_pin_other.id +WINDOW w AS (ORDER BY rpr_pin.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS val > 0); + +-- names reached through a navigation operation are pinned too +CREATE VIEW rpr_pin_nav_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_pin, rpr_pin_other +WHERE rpr_pin.id = rpr_pin_other.id +WINDOW w AS (ORDER BY rpr_pin.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(val) < val); + +-- no collision yet, so no column alias list +SELECT pg_get_viewdef('rpr_pin_v'::regclass, true); + +ALTER TABLE rpr_pin_other ADD COLUMN val INT; + +SELECT pg_get_viewdef('rpr_pin_v'::regclass, true); +SELECT pg_get_viewdef('rpr_pin_nav_v'::regclass, true); + +-- and the deparsed text builds an identical view +CREATE VIEW rpr_pin_v2 AS + SELECT count(*) OVER w AS cnt + FROM rpr_pin, + rpr_pin_other rpr_pin_other(id, val_1) + WHERE rpr_pin.id = rpr_pin_other.id + WINDOW w AS (ORDER BY rpr_pin.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS val > 0); + +SELECT pg_get_viewdef('rpr_pin_v'::regclass, true) + = pg_get_viewdef('rpr_pin_v2'::regclass, true) AS identical; + +-- The hazard this section guards against cannot be written in the first +-- place: a whole-row reference through a row constructor is rejected in +-- DEFINE, so no view can carry one as far as the deparser. +CREATE VIEW rpr_pin_row_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_pin, rpr_pin_other +WHERE rpr_pin.id = rpr_pin_other.id +WINDOW w AS (ORDER BY rpr_pin.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS ROW(rpr_pin.*) IS NOT NULL); + +-- a column merged by USING is pinned the same way +CREATE TABLE rpr_pin_l (x INT, y INT); +CREATE TABLE rpr_pin_r (x INT, z INT); +CREATE TABLE rpr_pin_x (id INT); + +CREATE VIEW rpr_pin_using_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_pin_l JOIN rpr_pin_r USING (x), rpr_pin_x +WINDOW w AS (ORDER BY rpr_pin_l.y + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS x > 0); + +ALTER TABLE rpr_pin_x ADD COLUMN x INT; + +SELECT pg_get_viewdef('rpr_pin_using_v'::regclass, true); + +-- a JOIN ... ON behaves the same, and the view keeps returning its rows +CREATE TABLE rpr_pin_j1 (id INT, price INT); +CREATE TABLE rpr_pin_j2 (id INT, qty INT); +INSERT INTO rpr_pin_j1 VALUES (1, 10), (2, 20); +INSERT INTO rpr_pin_j2 VALUES (1, 5), (2, 7); + +CREATE VIEW rpr_pin_on_v AS +SELECT j1.id, count(*) OVER w AS cnt +FROM rpr_pin_j1 j1 JOIN rpr_pin_j2 j2 ON j1.id = j2.id +WINDOW w AS (ORDER BY j1.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS price > 0); + +ALTER TABLE rpr_pin_j2 ADD COLUMN price INT; + +SELECT pg_get_viewdef('rpr_pin_on_v'::regclass, true); +SELECT * FROM rpr_pin_on_v ORDER BY id; + +-- An aliased join hides its inputs, so the name that gets printed is the join's +-- own, taken from varnosyn, not the child column the Var carries in varno. +-- Pinning the child instead would reserve a name that never reaches the output +-- and leave the printed one free for a later column to collide with. +CREATE TABLE rpr_pin_a (i INT, x INT); +CREATE TABLE rpr_pin_b (k INT, y INT); +CREATE TABLE rpr_pin_c (m INT); +INSERT INTO rpr_pin_a VALUES (1, 10), (2, 20); +INSERT INTO rpr_pin_b VALUES (1, 5), (2, 7); +INSERT INTO rpr_pin_c VALUES (100), (200); + +CREATE VIEW rpr_pin_alias_v AS +SELECT count(*) OVER w AS cnt +FROM (rpr_pin_a JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j(p, q, r, s), + rpr_pin_c +WINDOW w AS (ORDER BY rpr_pin_c.m + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS q > 0); + +ALTER TABLE rpr_pin_c ADD COLUMN q INT; + +SELECT pg_get_viewdef('rpr_pin_alias_v'::regclass, true); +SELECT * FROM rpr_pin_alias_v; + +-- and the deparsed text builds a view that returns the same rows +CREATE VIEW rpr_pin_alias_v2 AS +SELECT count(*) OVER w AS cnt +FROM (rpr_pin_a JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j(p, q, r, s), + rpr_pin_c rpr_pin_c(m, q_1) +WINDOW w AS (ORDER BY rpr_pin_c.m + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a) + DEFINE a AS q > 0); +SELECT * FROM rpr_pin_alias_v2; +DROP VIEW rpr_pin_alias_v2; + +-- Without a user column alias list the join still keeps the printed name, and +-- the input relation is the one that moves aside. +CREATE VIEW rpr_pin_alias_v3 AS +SELECT count(*) OVER w AS cnt +FROM (rpr_pin_a JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j, rpr_pin_c +WINDOW w AS (ORDER BY rpr_pin_c.m + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS x > 0); +SELECT pg_get_viewdef('rpr_pin_alias_v3'::regclass, true); + +DROP VIEW rpr_pin_alias_v3; +DROP VIEW rpr_pin_alias_v; +DROP TABLE rpr_pin_a, rpr_pin_b, rpr_pin_c; + +-- A relation RTE prints the column alias the user wrote, not the catalog name, +-- so the alias is the name to reserve. Pinning the catalog name would replace +-- the alias in the printed text and push aside an unrelated column that +-- collides only with a name nobody prints. +CREATE TABLE rpr_pin_d (i INT, k INT); +CREATE TABLE rpr_pin_e (m INT); +INSERT INTO rpr_pin_d VALUES (1, 10), (2, 20); +INSERT INTO rpr_pin_e VALUES (100), (200); + +CREATE VIEW rpr_pin_rel_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_pin_d d(p, q), rpr_pin_e +WINDOW w AS (ORDER BY rpr_pin_e.m + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS q > 0); + +-- a column named after the catalog name is no collision: q is what is printed +ALTER TABLE rpr_pin_e ADD COLUMN k INT; + +SELECT pg_get_viewdef('rpr_pin_rel_v'::regclass, true); + +-- one named after the alias is, and moves aside +ALTER TABLE rpr_pin_e ADD COLUMN q INT; + +SELECT pg_get_viewdef('rpr_pin_rel_v'::regclass, true); +SELECT * FROM rpr_pin_rel_v; + +DROP VIEW rpr_pin_rel_v; +DROP TABLE rpr_pin_d, rpr_pin_e; + +-- A name a DEFINE clause reads is the one name in the query that cannot be +-- spelled any other way, the qualifier slot being reserved for a pattern +-- variable. set_using_names() picks the name of every column merged by USING +-- before it, and is free to pick any name at all, renaming the merged inputs +-- to match. So the DEFINE names that are settled already are reserved first +-- and the merged names are chosen around them. Here the second USING would +-- otherwise reach for x_1, the very column the DEFINE clause reads: the +-- anonymous FULL JOIN forces USING names to be unique query-wide, which takes +-- plain x, and x_1 is what the next one counts up to. +CREATE TABLE rpr_res_t (x_1 INT, id INT); +CREATE TABLE rpr_res_l1 (x INT); +CREATE TABLE rpr_res_r1 (x INT); +CREATE TABLE rpr_res_l2 (x INT); +CREATE TABLE rpr_res_r2 (x INT); +INSERT INTO rpr_res_t VALUES (1, 1), (2, 2); +INSERT INTO rpr_res_l1 VALUES (1); +INSERT INTO rpr_res_r1 VALUES (1); +INSERT INTO rpr_res_l2 VALUES (1); +INSERT INTO rpr_res_r2 VALUES (1); + +CREATE VIEW rpr_res_using_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_t, + (rpr_res_l1 FULL JOIN rpr_res_r1 USING (x)), + (rpr_res_l2 JOIN rpr_res_r2 USING (x)) +WINDOW w AS (ORDER BY rpr_res_t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS x_1 > 0); + +SELECT pg_get_viewdef('rpr_res_using_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_using_rt AS ' + || pg_get_viewdef('rpr_res_using_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_using_v'::regclass, true) + = pg_get_viewdef('rpr_res_using_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_using_v; +SELECT * FROM rpr_res_using_rt; + +DROP VIEW rpr_res_using_rt, rpr_res_using_v; +DROP TABLE rpr_res_t, rpr_res_l1, rpr_res_r1, rpr_res_l2, rpr_res_r2; + +-- A merged column keeps its natural name and collides all the same. This one +-- is the column set_relation_column_names() cannot push aside afterwards: its +-- name was settled and handed to both inputs before that function ran, so the +-- loop there passes over it. An ordinary column in its place does move aside, +-- which is what the rpr_pin views above cover. +CREATE TABLE rpr_res_a (j INT, p INT); +CREATE TABLE rpr_res_b (j INT, q INT); +CREATE TABLE rpr_res_c (r INT, s INT); +INSERT INTO rpr_res_a VALUES (1, 10); +INSERT INTO rpr_res_b VALUES (1, 30); +INSERT INTO rpr_res_c VALUES (1, 10), (2, 20), (3, 15); + +CREATE VIEW rpr_res_merged_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_a JOIN rpr_res_b USING (j) CROSS JOIN rpr_res_c +WINDOW w AS (ORDER BY rpr_res_c.s + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL PATTERN (X Y+) + DEFINE X AS true, Y AS s > PREV(s)); + +-- the collision arrives only now: the merged column has been named j all along +ALTER TABLE rpr_res_c RENAME COLUMN s TO j; + +SELECT pg_get_viewdef('rpr_res_merged_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_merged_rt AS ' + || pg_get_viewdef('rpr_res_merged_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_merged_v'::regclass, true) + = pg_get_viewdef('rpr_res_merged_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_merged_v; +SELECT * FROM rpr_res_merged_rt; + +DROP VIEW rpr_res_merged_rt, rpr_res_merged_v; +DROP TABLE rpr_res_a, rpr_res_b, rpr_res_c; + +-- Naming a merged column renames the columns it merges, and the name can land +-- in an RTE that has a real column of that name already. The column the +-- DEFINE clause reads is the one that cannot move, so the merge counts past it +-- instead and the RTE prints two distinct aliases. +CREATE TABLE rpr_res_fa (k INT); +CREATE TABLE rpr_res_fb (k INT); +CREATE TABLE rpr_res_ga (k INT, k_1 INT); +CREATE TABLE rpr_res_gb (k INT); +CREATE TABLE rpr_res_ord (id INT); +INSERT INTO rpr_res_fa VALUES (1); +INSERT INTO rpr_res_fb VALUES (1); +INSERT INTO rpr_res_ga VALUES (1, 5); +INSERT INTO rpr_res_gb VALUES (1); +INSERT INTO rpr_res_ord VALUES (1), (2); + +CREATE VIEW rpr_res_dup_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_fa FULL JOIN rpr_res_fb USING (k), + rpr_res_ga JOIN rpr_res_gb USING (k), + rpr_res_ord +WINDOW w AS (ORDER BY rpr_res_ord.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS k_1 > 0); + +SELECT pg_get_viewdef('rpr_res_dup_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_dup_rt AS ' + || pg_get_viewdef('rpr_res_dup_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_dup_v'::regclass, true) + = pg_get_viewdef('rpr_res_dup_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_dup_v; +SELECT * FROM rpr_res_dup_rt; + +DROP VIEW rpr_res_dup_rt, rpr_res_dup_v; +DROP TABLE rpr_res_fa, rpr_res_fb, rpr_res_ga, rpr_res_gb, rpr_res_ord; + +-- A merged column a DEFINE clause reads is named by the DEFINE clause: its +-- name is settled before set_using_names() runs, and the USING clause adopts +-- it rather than inventing one. The merged name therefore stays id here, +-- and the newcomer is the one that moves aside. +CREATE TABLE rpr_res_p (id INT, v INT); +CREATE TABLE rpr_res_q (id INT, w INT); +CREATE TABLE rpr_res_s (n INT); +INSERT INTO rpr_res_p VALUES (1, 10), (2, 20); +INSERT INTO rpr_res_q VALUES (1, 30), (3, 40); +INSERT INTO rpr_res_s VALUES (7); + +CREATE VIEW rpr_res_full_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_p FULL JOIN rpr_res_q USING (id), rpr_res_s +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS id > 0); + +ALTER TABLE rpr_res_s ADD COLUMN id INT; + +SELECT pg_get_viewdef('rpr_res_full_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_full_rt AS ' + || pg_get_viewdef('rpr_res_full_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_full_v'::regclass, true) + = pg_get_viewdef('rpr_res_full_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_full_v; +SELECT * FROM rpr_res_full_rt; + +DROP VIEW rpr_res_full_rt, rpr_res_full_v; +DROP TABLE rpr_res_p, rpr_res_q, rpr_res_s; + +-- An aliased join answers for its inputs. The parser gives every column of +-- such a join the join's own varnosyn, so a DEFINE clause reading a column the +-- inputs brought in names the join and not the relation it came from. The +-- name is settled all the same, the join's USING clause not being the thing +-- that produced it, and so it is reserved: a column of a join that its own +-- USING clause does not name is no more merged than a relation's would be. +-- Left unreserved, the merged name counts up onto it and the join prints the +-- one alias twice, which reparses as an ambiguous column. +CREATE TABLE rpr_res_ja (x INT, x_1 INT); +CREATE TABLE rpr_res_jb (x INT, z INT); +CREATE TABLE rpr_res_m1 (x INT); +CREATE TABLE rpr_res_m2 (x INT); +CREATE TABLE rpr_res_ordj (id INT); +INSERT INTO rpr_res_ja VALUES (1, 7); +INSERT INTO rpr_res_jb VALUES (1, 9); +INSERT INTO rpr_res_m1 VALUES (1); +INSERT INTO rpr_res_m2 VALUES (1); +INSERT INTO rpr_res_ordj VALUES (1), (2); + +CREATE VIEW rpr_res_alias_v AS +SELECT count(*) OVER w AS cnt +FROM (rpr_res_m1 FULL JOIN rpr_res_m2 USING (x)), + (rpr_res_ja JOIN rpr_res_jb USING (x)) AS jx, + rpr_res_ordj +WINDOW w AS (ORDER BY rpr_res_ordj.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS x_1 > 0); + +SELECT pg_get_viewdef('rpr_res_alias_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_alias_rt AS ' + || pg_get_viewdef('rpr_res_alias_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_alias_v'::regclass, true) + = pg_get_viewdef('rpr_res_alias_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_alias_v; +SELECT * FROM rpr_res_alias_rt; + + +-- The same through NATURAL JOIN, which names no column in the query text. The +-- test above reads a join's USING clause to tell a merged column from one that +-- only passes through, and the analyzed tree is where it reads it: the parser +-- works out which columns NATURAL merges and files them there. +CREATE VIEW rpr_res_nat_v AS +SELECT count(*) OVER w AS cnt +FROM (rpr_res_m1 FULL JOIN rpr_res_m2 USING (x)), + (rpr_res_ja NATURAL JOIN rpr_res_jb) AS jx, + rpr_res_ordj +WINDOW w AS (ORDER BY rpr_res_ordj.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS x_1 > 0); + +SELECT pg_get_viewdef('rpr_res_nat_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_nat_rt AS ' + || pg_get_viewdef('rpr_res_nat_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_nat_v'::regclass, true) + = pg_get_viewdef('rpr_res_nat_rt'::regclass, true) AS round_trips; + +DROP VIEW rpr_res_nat_rt, rpr_res_nat_v; +DROP VIEW rpr_res_alias_rt, rpr_res_alias_v; +DROP TABLE rpr_res_ja, rpr_res_jb, rpr_res_m1, rpr_res_m2, rpr_res_ordj; + +-- A column a function's result type grows after the view is made is one the +-- deparser does not see at all: expandRTE() stops at the column count the +-- query was parsed with. A column it cannot see is one it cannot rename out +-- of the way, and the name it collides with here is the one a DEFINE clause +-- has to resolve to as printed. So the grown columns are looked up and named +-- too, and printed in full, the alias list being positional. +CREATE TABLE rpr_res_fn (id INT, val INT); +INSERT INTO rpr_res_fn VALUES (1, 1), (2, 2), (3, 3); +CREATE TABLE rpr_res_cfg (a INT); +INSERT INTO rpr_res_cfg VALUES (1); +CREATE FUNCTION rpr_res_fcfg() RETURNS SETOF rpr_res_cfg LANGUAGE sql + AS $$ SELECT * FROM rpr_res_cfg $$; + +CREATE VIEW rpr_res_fn_v AS +SELECT rpr_res_fn.id, count(*) OVER w AS cnt +FROM rpr_res_fn, rpr_res_fcfg() f +WINDOW w AS (ORDER BY rpr_res_fn.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS val > 0); + +-- nothing to keep off yet +SELECT pg_get_viewdef('rpr_res_fn_v'::regclass, true); + +ALTER TABLE rpr_res_cfg ADD COLUMN val INT; + +SELECT pg_get_viewdef('rpr_res_fn_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_fn_rt AS ' + || pg_get_viewdef('rpr_res_fn_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_fn_v'::regclass, true) + = pg_get_viewdef('rpr_res_fn_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_fn_v; +SELECT * FROM rpr_res_fn_rt; + +-- a grown column that collides with nothing is still on the list +ALTER TABLE rpr_res_cfg ADD COLUMN spare INT; +SELECT pg_get_viewdef('rpr_res_fn_v'::regclass, true); + +DROP VIEW rpr_res_fn_rt, rpr_res_fn_v; +DROP FUNCTION rpr_res_fcfg(); +DROP TABLE rpr_res_fn, rpr_res_cfg; + +-- A system column is named from the catalog, not from the deparser's own +-- choice, so there is no alias to pick for it and nothing to exempt from +-- renaming. Its name still has to be held against the rest of the query, or +-- a column that turns up later answers to it as well. +CREATE TABLE rpr_res_sys (id INT, v INT); +INSERT INTO rpr_res_sys VALUES (1, 1), (2, 2); +CREATE TYPE rpr_res_ct AS (a INT); +CREATE FUNCTION rpr_res_fct() RETURNS SETOF rpr_res_ct LANGUAGE sql + AS $$ SELECT ROW(1)::rpr_res_ct $$; + +CREATE VIEW rpr_res_sys_v AS +SELECT rpr_res_sys.id, count(*) OVER w AS cnt +FROM rpr_res_sys, rpr_res_fct() f +WINDOW w AS (ORDER BY rpr_res_sys.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS ctid IS NOT NULL); + +ALTER TYPE rpr_res_ct ADD ATTRIBUTE ctid INT; + +SELECT pg_get_viewdef('rpr_res_sys_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_sys_rt AS ' + || pg_get_viewdef('rpr_res_sys_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_sys_v'::regclass, true) + = pg_get_viewdef('rpr_res_sys_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_sys_v; +SELECT * FROM rpr_res_sys_rt; + +DROP VIEW rpr_res_sys_rt, rpr_res_sys_v; +DROP FUNCTION rpr_res_fct(); +DROP TYPE rpr_res_ct; +DROP TABLE rpr_res_sys; + +-- A TABLEFUNC RTE writes its column names into the clause that produces them, +-- but it accepts a column alias list like any other RTE, and a rename of one +-- of its columns is printed there. So a TABLEFUNC column that comes to +-- answer to the name a DEFINE clause reads is renamed like any other column +-- would be, and the DEFINE clause keeps its spelling. +CREATE TABLE rpr_res_tf (id INT, s INT); +INSERT INTO rpr_res_tf VALUES (1, 1), (2, 2); + +CREATE VIEW rpr_res_tf_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_tf, + JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jx +WINDOW w AS (ORDER BY rpr_res_tf.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS s > 0); + +-- the collision arrives only now +ALTER TABLE rpr_res_tf RENAME COLUMN s TO c1; + +SELECT pg_get_viewdef('rpr_res_tf_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_tf_rt AS ' + || pg_get_viewdef('rpr_res_tf_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_tf_v'::regclass, true) + = pg_get_viewdef('rpr_res_tf_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_tf_v; +SELECT * FROM rpr_res_tf_rt; + +DROP VIEW rpr_res_tf_rt, rpr_res_tf_v; +DROP TABLE rpr_res_tf; + +-- An aliased join answers for its inputs and hides them, so the DEFINE +-- clause here reads the join's own column x. That name is reserved across +-- the query level, which reaches the TABLEFUNC column underneath as well: +-- it is renamed on its own alias list, and the join prints x on its list to +-- keep the name the query sees. Out of reach of an unqualified reference, +-- the TABLEFUNC column would not have collided, but a reserved name is kept +-- off every RTE of the level, as a globally unique USING name is. +CREATE TABLE rpr_res_hid (id INT, v INT); +CREATE TABLE rpr_res_hu (m INT); +INSERT INTO rpr_res_hid VALUES (1, 1), (2, 2), (3, 3); +INSERT INTO rpr_res_hu VALUES (9); + +CREATE VIEW rpr_res_hid_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_hid, + (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (x int PATH '$')) AS jt + JOIN rpr_res_hu ON jt.x > 0) j +WINDOW w AS (ORDER BY rpr_res_hid.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS x > 0); + +SELECT pg_get_viewdef('rpr_res_hid_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_hid_rt AS ' + || pg_get_viewdef('rpr_res_hid_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_hid_v'::regclass, true) + = pg_get_viewdef('rpr_res_hid_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_hid_v; +SELECT * FROM rpr_res_hid_rt; + +DROP VIEW rpr_res_hid_rt, rpr_res_hid_v; +DROP TABLE rpr_res_hid, rpr_res_hu; + +-- A column can come to carry the name a DEFINE clause reads only after the +-- view is made, by being renamed, and from then on the two have to be told +-- apart in the printed text. The DEFINE column keeps its spelling, being +-- settled first, and the newcomer gets name_N -- whether it sits in the same +-- RTE or in another one where a DEFINE clause reads it too, is the one a +-- USING clause merges, or is not merged but carries the USING clause's +-- spelling, which is no business of the deparser's to guess from. A USING +-- clause elsewhere that spells the newcomer's new name moves out of the way +-- as well. +CREATE TABLE rpr_res_ren (c INT, d INT); +INSERT INTO rpr_res_ren VALUES (1, 1), (2, 2); +CREATE TABLE rpr_res_ren2 (a INT, e INT); +INSERT INTO rpr_res_ren2 VALUES (1, 0), (2, 0); +CREATE TABLE rpr_res_renl (a_1 INT); +CREATE TABLE rpr_res_renr (a_1 INT); +INSERT INTO rpr_res_renl VALUES (1); +INSERT INTO rpr_res_renr VALUES (1); +CREATE TABLE rpr_res_rs (x INT); +CREATE TABLE rpr_res_rr (x INT, y INT); +INSERT INTO rpr_res_rs VALUES (1), (2); +INSERT INTO rpr_res_rr VALUES (1, 5), (2, 6); +CREATE TABLE rpr_res_rena (id INT, a INT); +CREATE TABLE rpr_res_renb (id INT, b INT); +INSERT INTO rpr_res_rena VALUES (1, 1), (2, 2); +INSERT INTO rpr_res_renb VALUES (1, 5), (2, 0); + +-- the same RTE, its alias list shorter than the table +CREATE VIEW rpr_res_ren_v AS +SELECT x.a, x.d, count(*) OVER w AS cnt +FROM rpr_res_ren AS x(a) +WINDOW w AS (ORDER BY x.a + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (P Q*) + DEFINE P AS a > 0, Q AS d > 0); + +-- the column a USING clause merges +CREATE VIEW rpr_res_renu_v AS +SELECT a, x.d, count(*) OVER w AS cnt +FROM rpr_res_ren AS x(a) JOIN rpr_res_ren2 USING (a) +WINDOW w AS (ORDER BY a + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (P Q*) + DEFINE P AS d > 0); + +-- a USING clause elsewhere that spells the name the newcomer is going to get +CREATE VIEW rpr_res_renx_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_ren AS x(a), rpr_res_renl JOIN rpr_res_renr USING (a_1) +WINDOW w AS (ORDER BY x.a + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (P Q*) + DEFINE P AS a > 0, Q AS d > 0); + +-- a merged column that is renamed away, and a DEFINE column renamed onto +-- the USING clause's spelling +CREATE VIEW rpr_res_renm_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_rs JOIN rpr_res_rr USING (x) +WINDOW w AS (ORDER BY y + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS y > 0); + +-- another RTE, both of its columns read by the DEFINE clause +CREATE VIEW rpr_res_reno_v AS +SELECT rpr_res_rena.id, count(*) OVER w AS cnt +FROM rpr_res_rena JOIN rpr_res_renb ON rpr_res_rena.id = rpr_res_renb.id +WINDOW w AS (ORDER BY rpr_res_rena.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (P Q*) + DEFINE P AS a > 0, Q AS b > 0); + +-- the collisions arrive only now +ALTER TABLE rpr_res_ren RENAME d TO a; +ALTER TABLE rpr_res_rr RENAME x TO z; +ALTER TABLE rpr_res_rr RENAME y TO x; +ALTER TABLE rpr_res_renb RENAME b TO a; + +SELECT pg_get_viewdef('rpr_res_ren_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_ren_rt AS ' + || pg_get_viewdef('rpr_res_ren_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_ren_v'::regclass, true) + = pg_get_viewdef('rpr_res_ren_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_ren_v; +SELECT * FROM rpr_res_ren_rt; + +SELECT pg_get_viewdef('rpr_res_renu_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_renu_rt AS ' + || pg_get_viewdef('rpr_res_renu_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_renu_v'::regclass, true) + = pg_get_viewdef('rpr_res_renu_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_renu_v; +SELECT * FROM rpr_res_renu_rt; + +SELECT pg_get_viewdef('rpr_res_renx_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_renx_rt AS ' + || pg_get_viewdef('rpr_res_renx_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_renx_v'::regclass, true) + = pg_get_viewdef('rpr_res_renx_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_renx_v; +SELECT * FROM rpr_res_renx_rt; + +SELECT pg_get_viewdef('rpr_res_renm_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_renm_rt AS ' + || pg_get_viewdef('rpr_res_renm_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_renm_v'::regclass, true) + = pg_get_viewdef('rpr_res_renm_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_renm_v; +SELECT * FROM rpr_res_renm_rt; + +SELECT pg_get_viewdef('rpr_res_reno_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_reno_rt AS ' + || pg_get_viewdef('rpr_res_reno_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_reno_v'::regclass, true) + = pg_get_viewdef('rpr_res_reno_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_reno_v; +SELECT * FROM rpr_res_reno_rt; + +DROP VIEW rpr_res_reno_rt, rpr_res_reno_v; +DROP VIEW rpr_res_renm_rt, rpr_res_renm_v; +DROP VIEW rpr_res_renx_rt, rpr_res_renx_v; +DROP VIEW rpr_res_renu_rt, rpr_res_renu_v; +DROP VIEW rpr_res_ren_rt, rpr_res_ren_v; +DROP TABLE rpr_res_ren, rpr_res_ren2, rpr_res_renl, rpr_res_renr; +DROP TABLE rpr_res_rs, rpr_res_rr; +DROP TABLE rpr_res_rena, rpr_res_renb; + +-- A merged column an aliased join carries can spell the DEFINE name from the +-- start, and a TABLEFUNC among the join's inputs changes nothing about that: +-- the join's own alias list names the merged column, the inputs answer to +-- it, and the DEFINE column is left alone. Nor does the order of the FROM +-- list matter. +CREATE TABLE rpr_res_tj (id INT, c1 INT); +INSERT INTO rpr_res_tj VALUES (1, 1), (2, 2); +CREATE TABLE rpr_res_tb (c1 INT, y INT); +INSERT INTO rpr_res_tb VALUES (1, 10), (2, 20); +CREATE TABLE rpr_res_ti (id INT); +INSERT INTO rpr_res_ti VALUES (1), (2); + +CREATE VIEW rpr_res_tj_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_tj, + (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jt + JOIN rpr_res_tb USING (c1)) AS j(k, m) +WINDOW w AS (ORDER BY rpr_res_tj.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS c1 > 0); + +CREATE VIEW rpr_res_tjr_v AS +SELECT count(*) OVER w AS cnt +FROM (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jt + JOIN rpr_res_tb USING (c1)) AS j(k, m), + rpr_res_tj +WINDOW w AS (ORDER BY rpr_res_tj.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS c1 > 0); + +-- the join's alias list puts the USING spelling on a column it does not +-- merge, and that column is the one the DEFINE clause reads +CREATE VIEW rpr_res_tja_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_ti, + (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jt + JOIN rpr_res_tb USING (c1)) AS j(k, c1) +WINDOW w AS (ORDER BY rpr_res_ti.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS c1 > 0); + +-- the same with the TABLEFUNC on the right +CREATE VIEW rpr_res_tjb_v AS +SELECT count(*) OVER w AS cnt +FROM (rpr_res_tb JOIN JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jt + USING (c1)) AS j(a, c1) +WINDOW w AS (ORDER BY j.a + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS c1 > 0); + +SELECT pg_get_viewdef('rpr_res_tj_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_tj_rt AS ' + || pg_get_viewdef('rpr_res_tj_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_tj_v'::regclass, true) + = pg_get_viewdef('rpr_res_tj_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_tj_v; +SELECT * FROM rpr_res_tj_rt; + +SELECT pg_get_viewdef('rpr_res_tjr_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_tjr_rt AS ' + || pg_get_viewdef('rpr_res_tjr_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_tjr_v'::regclass, true) + = pg_get_viewdef('rpr_res_tjr_rt'::regclass, true) AS round_trips; + +SELECT pg_get_viewdef('rpr_res_tja_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_tja_rt AS ' + || pg_get_viewdef('rpr_res_tja_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_tja_v'::regclass, true) + = pg_get_viewdef('rpr_res_tja_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_tja_v; +SELECT * FROM rpr_res_tja_rt; + +SELECT pg_get_viewdef('rpr_res_tjb_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_tjb_rt AS ' + || pg_get_viewdef('rpr_res_tjb_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_tjb_v'::regclass, true) + = pg_get_viewdef('rpr_res_tjb_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_tjb_v; +SELECT * FROM rpr_res_tjb_rt; + +DROP VIEW rpr_res_tjb_rt, rpr_res_tjb_v; +DROP VIEW rpr_res_tja_rt, rpr_res_tja_v; +DROP VIEW rpr_res_tjr_rt, rpr_res_tjr_v; +DROP VIEW rpr_res_tj_rt, rpr_res_tj_v; +DROP TABLE rpr_res_tj, rpr_res_tb, rpr_res_ti; + +-- The name a USING clause is given for an aliased join has to stay clear of +-- the names the join's alias list already carries, and of what a DEFINE +-- clause reads among them. The anonymous FULL JOIN takes the plain spelling +-- first, so the second USING has to move past both. +CREATE TABLE rpr_res_fa (x INT); +CREATE TABLE rpr_res_fb (x INT); +CREATE TABLE rpr_res_fc (x INT, y INT); +INSERT INTO rpr_res_fa VALUES (1); +INSERT INTO rpr_res_fb VALUES (1); +INSERT INTO rpr_res_fc VALUES (1, 3); + +CREATE VIEW rpr_res_fa_v AS +SELECT count(*) OVER w AS cnt +FROM (rpr_res_fa FULL JOIN rpr_res_fb USING (x)), + ((rpr_res_fc JOIN rpr_res_fa t4 USING (x)) AS j(x, x_1) + JOIN rpr_res_fb t5 USING (x)) +WINDOW w AS (ORDER BY j.x + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS x_1 > 0); + +SELECT pg_get_viewdef('rpr_res_fa_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_fa_rt AS ' + || pg_get_viewdef('rpr_res_fa_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_fa_v'::regclass, true) + = pg_get_viewdef('rpr_res_fa_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_fa_v; +SELECT * FROM rpr_res_fa_rt; + +DROP VIEW rpr_res_fa_rt, rpr_res_fa_v; +DROP TABLE rpr_res_fa, rpr_res_fb, rpr_res_fc; + +-- A TABLEFUNC that merges through an anonymous join, or that carries a +-- column alias list of its own, is no different: when a relation column is +-- renamed onto the TABLEFUNC's name, it is the TABLEFUNC column that moves, +-- and a third RTE that already spells the name it would have moved to is +-- kept clear as well. +CREATE TABLE rpr_res_tk (id INT, y INT); +INSERT INTO rpr_res_tk VALUES (1, 1), (2, 2); +CREATE TABLE rpr_res_th (x INT, z INT); +INSERT INTO rpr_res_th VALUES (1, 1), (2, 2); +CREATE TABLE rpr_res_ta (id INT, c1 INT); +INSERT INTO rpr_res_ta VALUES (1, 1), (2, 2); +CREATE TABLE rpr_res_ts (id INT, s INT); +INSERT INTO rpr_res_ts VALUES (1, 1), (2, 2); +CREATE TABLE rpr_res_to (c1_1 INT); +INSERT INTO rpr_res_to VALUES (9); + +CREATE VIEW rpr_res_tk_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_tk, + (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (x int PATH '$')) AS jt + JOIN rpr_res_th USING (x)) j +WINDOW w AS (ORDER BY rpr_res_tk.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS y > 0); + +CREATE VIEW rpr_res_ta_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_ta, + JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jx(a) +WINDOW w AS (ORDER BY rpr_res_ta.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS c1 > 0); + +CREATE VIEW rpr_res_ts_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_ts, rpr_res_to, + JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jx +WINDOW w AS (ORDER BY rpr_res_ts.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS s > 0); + +ALTER TABLE rpr_res_tk RENAME y TO x; +ALTER TABLE rpr_res_ts RENAME s TO c1; + +SELECT pg_get_viewdef('rpr_res_tk_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_tk_rt AS ' + || pg_get_viewdef('rpr_res_tk_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_tk_v'::regclass, true) + = pg_get_viewdef('rpr_res_tk_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_tk_v; +SELECT * FROM rpr_res_tk_rt; + +SELECT pg_get_viewdef('rpr_res_ta_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_ta_rt AS ' + || pg_get_viewdef('rpr_res_ta_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_ta_v'::regclass, true) + = pg_get_viewdef('rpr_res_ta_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_ta_v; +SELECT * FROM rpr_res_ta_rt; + +SELECT pg_get_viewdef('rpr_res_ts_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_ts_rt AS ' + || pg_get_viewdef('rpr_res_ts_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_ts_v'::regclass, true) + = pg_get_viewdef('rpr_res_ts_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_ts_v; +SELECT * FROM rpr_res_ts_rt; + +DROP VIEW rpr_res_ts_rt, rpr_res_ts_v; +DROP VIEW rpr_res_ta_rt, rpr_res_ta_v; +DROP VIEW rpr_res_tk_rt, rpr_res_tk_v; +DROP TABLE rpr_res_tk, rpr_res_th, rpr_res_ta, rpr_res_ts, rpr_res_to; + +-- A merged column the DEFINE clause reads is named by the DEFINE clause, not +-- by the USING clause: the name settled for it is the one the merge adopts. +-- So a join whose alias list renames the merged column prints the same text +-- with the DEFINE clause as without it, and a merge two joins deep is reached +-- through the input the reference resolves to. A column that turns up later +-- under the same name elsewhere is the one that moves. +CREATE TABLE rpr_res_ma (x INT, y INT); +CREATE TABLE rpr_res_mb (x INT, z INT); +CREATE TABLE rpr_res_mc (x INT, r INT); +CREATE TABLE rpr_res_mo (xx INT); +INSERT INTO rpr_res_ma VALUES (1, 1), (2, 2); +INSERT INTO rpr_res_mb VALUES (1, 1), (2, 2); +INSERT INTO rpr_res_mc VALUES (1, 1), (2, 2); +INSERT INTO rpr_res_mo VALUES (1); + +CREATE VIEW rpr_res_ma_v AS +SELECT count(*) OVER w AS cnt +FROM (rpr_res_ma JOIN rpr_res_mb USING (x)) AS j(x1, y, z) +WINDOW w AS (ORDER BY j.y + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS x1 > 0); + +CREATE VIEW rpr_res_ma_nodef AS +SELECT count(*) OVER w AS cnt +FROM (rpr_res_ma JOIN rpr_res_mb USING (x)) AS j(x1, y, z) +WINDOW w AS (ORDER BY j.y + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING); + +CREATE VIEW rpr_res_mm_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_res_mo, (rpr_res_ma JOIN rpr_res_mb USING (x)) JOIN rpr_res_mc USING (x) +WINDOW w AS (ORDER BY rpr_res_ma.y + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS x > 0); + +SELECT pg_get_viewdef('rpr_res_ma_v'::regclass, true); +SELECT pg_get_viewdef('rpr_res_ma_nodef'::regclass, true); +SELECT 'CREATE VIEW rpr_res_ma_rt AS ' + || pg_get_viewdef('rpr_res_ma_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_ma_v'::regclass, true) + = pg_get_viewdef('rpr_res_ma_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_ma_v; +SELECT * FROM rpr_res_ma_rt; + +SELECT pg_get_viewdef('rpr_res_mm_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_mm_rt AS ' + || pg_get_viewdef('rpr_res_mm_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_mm_v'::regclass, true) + = pg_get_viewdef('rpr_res_mm_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_mm_v; +SELECT * FROM rpr_res_mm_rt; + +-- the name turns up elsewhere only now +DROP VIEW rpr_res_mm_rt; +ALTER TABLE rpr_res_mo RENAME xx TO x; + +SELECT pg_get_viewdef('rpr_res_mm_v'::regclass, true); +SELECT 'CREATE VIEW rpr_res_mm_rt AS ' + || pg_get_viewdef('rpr_res_mm_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_res_mm_v'::regclass, true) + = pg_get_viewdef('rpr_res_mm_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_res_mm_v; +SELECT * FROM rpr_res_mm_rt; + +DROP VIEW rpr_res_mm_rt, rpr_res_mm_v; +DROP VIEW rpr_res_ma_rt, rpr_res_ma_nodef, rpr_res_ma_v; +DROP TABLE rpr_res_ma, rpr_res_mb, rpr_res_mc, rpr_res_mo; + +-- Deparsing a query whose DEFINE clause reads the grouping step expands the +-- clause's GROUP Vars into the grouping expressions, and over a column merged +-- by a FULL JOIN USING that expands to the COALESCE the parser built for the +-- merge. Printed, both arms come out spelled the same -- a DEFINE clause +-- carries no qualifier -- so COALESCE(id, id) says nothing about where either +-- came from, and re-parsing nests one merged column inside another. The join +-- RTE still holds what it built, so the expansion is folded back into the +-- merged column itself. +CREATE TABLE rpr_cds_l (id INT PRIMARY KEY, val INT); +CREATE TABLE rpr_cds_r (id INT, val INT); +CREATE TABLE rpr_cds_o (k INT); +INSERT INTO rpr_cds_l VALUES (1, 1), (2, 2); +INSERT INTO rpr_cds_r VALUES (1, 1), (3, 3); +INSERT INTO rpr_cds_o VALUES (1), (2); + +CREATE VIEW rpr_cds_v AS +SELECT COALESCE(rpr_cds_l.id, rpr_cds_r.id) + 1 AS idp1, count(*) OVER w AS cnt +FROM rpr_cds_l FULL JOIN rpr_cds_r USING (id) +GROUP BY COALESCE(rpr_cds_l.id, rpr_cds_r.id) + 1 +WINDOW w AS (ORDER BY COALESCE(rpr_cds_l.id, rpr_cds_r.id) + 1 + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS id + 1 > 0); + +SELECT pg_get_viewdef('rpr_cds_v'::regclass, true); +SELECT 'CREATE VIEW rpr_cds_rt AS ' + || pg_get_viewdef('rpr_cds_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_cds_v'::regclass, true) + = pg_get_viewdef('rpr_cds_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_cds_v ORDER BY idp1; +SELECT * FROM rpr_cds_rt ORDER BY idp1; + +-- An outer join above the merge marks the copy the grouping expression +-- carries and not the copy the join RTE keeps. Neither mark reaches the +-- printed text, so the two still name one column. +CREATE VIEW rpr_cds_null_v AS +SELECT COALESCE(l.id, r.id) + 1 AS idp1, count(*) OVER w AS cnt +FROM rpr_cds_o LEFT JOIN (rpr_cds_l l FULL JOIN rpr_cds_r r USING (id)) ON true +GROUP BY COALESCE(l.id, r.id) + 1 +WINDOW w AS (ORDER BY COALESCE(l.id, r.id) + 1 + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS id + 1 > 0); + +SELECT pg_get_viewdef('rpr_cds_null_v'::regclass, true); +SELECT 'CREATE VIEW rpr_cds_null_rt AS ' + || pg_get_viewdef('rpr_cds_null_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_cds_null_v'::regclass, true) + = pg_get_viewdef('rpr_cds_null_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_cds_null_v ORDER BY idp1; +SELECT * FROM rpr_cds_null_rt ORDER BY idp1; + +-- and the same nesting where the join above nulls nothing, which the exact +-- match takes +CREATE VIEW rpr_cds_inner_v AS +SELECT COALESCE(l.id, r.id) + 1 AS idp1, count(*) OVER w AS cnt +FROM (rpr_cds_l l FULL JOIN rpr_cds_r r USING (id)) JOIN rpr_cds_o ON true +GROUP BY COALESCE(l.id, r.id) + 1 +WINDOW w AS (ORDER BY COALESCE(l.id, r.id) + 1 + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS id + 1 > 0); + +SELECT pg_get_viewdef('rpr_cds_inner_v'::regclass, true); +SELECT 'CREATE VIEW rpr_cds_inner_rt AS ' + || pg_get_viewdef('rpr_cds_inner_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_cds_inner_v'::regclass, true) + = pg_get_viewdef('rpr_cds_inner_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_cds_inner_v ORDER BY idp1; +SELECT * FROM rpr_cds_inner_rt ORDER BY idp1; + +-- A merge built over another merge -- a FULL JOIN USING above a FULL JOIN +-- USING -- expands to a COALESCE over a COALESCE. The inner one is folded +-- first, so that the outer one is seen whole and folds in turn; folded +-- inside out, it re-parses to itself rather than to twice the nesting. +CREATE VIEW rpr_cds_nest_v AS +SELECT count(*) OVER w AS cnt +FROM (rpr_cds_l FULL JOIN rpr_cds_r USING (id)) FULL JOIN rpr_cds_r t USING (id) +GROUP BY rpr_cds_l.id, rpr_cds_r.id, t.id +WINDOW w AS (ORDER BY rpr_cds_l.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS id > 0); + +SELECT pg_get_viewdef('rpr_cds_nest_v'::regclass, true); +SELECT 'CREATE VIEW rpr_cds_nest_rt AS ' + || pg_get_viewdef('rpr_cds_nest_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_cds_nest_v'::regclass, true) + = pg_get_viewdef('rpr_cds_nest_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_cds_nest_v; +SELECT * FROM rpr_cds_nest_rt; + +DROP VIEW rpr_cds_nest_rt, rpr_cds_nest_v; +DROP VIEW rpr_cds_inner_rt, rpr_cds_inner_v; +DROP VIEW rpr_cds_null_rt, rpr_cds_null_v; +DROP VIEW rpr_cds_rt, rpr_cds_v; +DROP TABLE rpr_cds_l, rpr_cds_r, rpr_cds_o; + +-- A rule deparses with varprefix on no matter what its action query looks +-- like, the range table always holding *OLD* and *NEW*, so a DEFINE clause in +-- one would be printed with qualifiers that the parser rejects outright. +-- get_rule_define() turns the prefix off for the clause, and without that a +-- rule holding a row pattern query could not be restored at all -- not even a +-- single-table one, which is what makes this its own case and not the view's. +CREATE TABLE rpr_rule_t (id INT, val INT); +CREATE TABLE rpr_rule_log (id INT, cnt BIGINT); + +CREATE RULE rpr_rule_r AS ON INSERT TO rpr_rule_t DO ALSO + INSERT INTO rpr_rule_log + SELECT id, count(*) OVER w FROM rpr_rule_t + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS val > 0); + +SELECT pg_get_ruledef(oid, true) FROM pg_rewrite WHERE rulename = 'rpr_rule_r'; + +-- and that text is what has to reparse +CREATE TABLE rpr_rule_saved AS + SELECT pg_get_ruledef(oid, true) AS def + FROM pg_rewrite WHERE rulename = 'rpr_rule_r'; +DROP RULE rpr_rule_r ON rpr_rule_t; +SELECT def FROM rpr_rule_saved \gexec +SELECT (SELECT def FROM rpr_rule_saved) = pg_get_ruledef(oid, true) AS round_trips + FROM pg_rewrite WHERE rulename = 'rpr_rule_r'; + +-- the restored rule still fires. A rule action is run against the rows the +-- statement supplies as well as the table, so a two-row INSERT gives the +-- window four rows to order by two distinct ids; sort the result on both +-- columns, the pairs within an id being interchangeable. +INSERT INTO rpr_rule_t VALUES (1, 1), (2, 2); +SELECT * FROM rpr_rule_log ORDER BY id, cnt; + +DROP TABLE rpr_rule_saved; +DROP TABLE rpr_rule_t, rpr_rule_log; + +-- A relation alias that happens to spell a pattern variable is the other way +-- the prefix goes wrong. Printed as up.price it does not come back as a +-- range variable qualifier, which is merely rejected, but as a pattern +-- variable one, which is rejected by a different rule and with a different +-- message. Two RTEs are what turns the prefix on. +CREATE TABLE rpr_pvar_a (id INT, price INT); +CREATE TABLE rpr_pvar_b (id INT); +INSERT INTO rpr_pvar_a VALUES (1, 10), (2, 20), (3, 5); +INSERT INTO rpr_pvar_b VALUES (1), (2), (3); + +CREATE VIEW rpr_pvar_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_pvar_a up, rpr_pvar_b +WHERE up.id = rpr_pvar_b.id +WINDOW w AS (ORDER BY up.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (up+) + DEFINE up AS price > 0); + +SELECT pg_get_viewdef('rpr_pvar_v'::regclass, true); +SELECT 'CREATE VIEW rpr_pvar_rt AS ' + || pg_get_viewdef('rpr_pvar_v'::regclass, true) \gexec +SELECT pg_get_viewdef('rpr_pvar_v'::regclass, true) + = pg_get_viewdef('rpr_pvar_rt'::regclass, true) AS round_trips; +SELECT * FROM rpr_pvar_v; +SELECT * FROM rpr_pvar_rt; + +DROP VIEW rpr_pvar_rt, rpr_pvar_v; +DROP TABLE rpr_pvar_a, rpr_pvar_b; + +-- The same query written fresh is rejected, since nothing pins the name for +-- it. Pinning is what lets the stored definition above still reparse. +SELECT j1.id, count(*) OVER w AS cnt +FROM rpr_pin_j1 j1 JOIN rpr_pin_j2 j2 ON j1.id = j2.id +WINDOW w AS (ORDER BY j1.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS price > 0); + -- Materialized view (if supported) diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index abe89d097c9..71b7b001e9c 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -1235,6 +1235,42 @@ DROP RULE hat_confsel ON hats; drop table hats; drop table hat_data; +-- An unnamed FULL JOIN USING makes USING names unique query-wide, but an RTE +-- outside the FROM clause has no alias list to carry a renamed column. +create table rule_uniq1 (x int, y int); +create table rule_uniq2 (x int, z int); +create table rule_uniq3 (x int, w int); +create table rule_uniq_log (x int); + +create rule rule_uniq_r as on update to rule_uniq1 do also + insert into rule_uniq_log + select g1.y from rule_uniq1 g1, rule_uniq2 full join rule_uniq3 using (x) + where g1.x <> new.x; + +create rule rule_uniq_u as on update to rule_uniq1 do also + update rule_uniq_log set x = 1 + from rule_uniq2 full join rule_uniq3 using (x) + where rule_uniq_log.x = 0; + +-- g1 is in the FROM clause and is re-aliased to x_1; new and the update +-- target are not, so they keep x +select rulename, definition from pg_rules where tablename = 'rule_uniq1' + order by rulename; + +drop table rule_uniq1, rule_uniq2, rule_uniq3, rule_uniq_log; + +-- The subquery an INSERT ... SELECT reads from is outside the FROM clause +-- too, but it is printed, so its unnamed columns still need unique names. +-- LIMIT keeps the subquery from being pulled up, and the unfilled column +-- makes the plan project through a subquery scan. +create table rule_uniq_ins (a int, b int, c text, d int); + +explain (verbose, costs off) + insert into rule_uniq_ins (a, b, c) + select a + 1, b + 1, c || c from rule_uniq_ins limit 2; + +drop table rule_uniq_ins; + -- test for pg_get_functiondef properly regurgitating SET parameters -- Note that the function is kept around to stress pg_dump. CREATE FUNCTION func_with_set_params() RETURNS integer diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index bd4975877d6..3ab96052309 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -3693,6 +3693,7 @@ child_process_kind chr cmpEntriesArg codes_t +collapse_define_context collation_cache_entry collation_cache_hash color -- 2.54.0 (Apple Git-157)