From 2ffa740be9d96a3743ecb7e42391c53d0760c65a Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 31 Dec 2012 15:13:26 -0500 Subject: [PATCH] Fix ruleutils to cope with conflicts from adding/dropping/renaming columns. In commit 11e131854f8231a21613f834c40fe9d046926387, we improved the rule/view dumping code so that it would produce valid query representations even if some of the tables involved in a query had been renamed since the query was parsed. This patch extends that idea to fix problems that occur when individual columns are renamed, or added or dropped. As before, the core of the fix is to assign unique new aliases when a name conflict has been created. This is complicated by the JOIN USING feature, which requires the same column alias to be used in both input relations, but we can handle that with a sufficiently complex approach to assigning aliases. A fortiori, this patch takes care of situations where the query didn't have unique column names to begin with, such as in a recent complaint from Bryan Nuse. (Because of expansion of "SELECT *", re-parsing a dumped query can require column name uniqueness even though the original text did not.) --- src/backend/commands/explain.c | 14 +- src/backend/commands/typecmds.c | 15 +- src/backend/utils/adt/ruleutils.c | 1452 ++++++++++++++++++--- src/test/regress/expected/create_view.out | 428 +++++- src/test/regress/sql/create_view.sql | 109 +- 5 files changed, 1764 insertions(+), 254 deletions(-) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 33252a8e20..323f3a11ae 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -653,9 +653,9 @@ elapsed_time(instr_time *starttime) * Prescan the planstate tree to identify which RTEs are referenced * * Adds the relid of each referenced RTE to *rels_used. The result controls - * which RTEs are assigned aliases by select_rtable_names_for_explain. This - * ensures that we don't confusingly assign un-suffixed aliases to RTEs that - * never appear in the EXPLAIN output (such as inheritance parents). + * which RTEs are assigned aliases by select_rtable_names_for_explain. + * This ensures that we don't confusingly assign un-suffixed aliases to RTEs + * that never appear in the EXPLAIN output (such as inheritance parents). */ static void ExplainPreScanNode(PlanState *planstate, Bitmapset **rels_used) @@ -1954,6 +1954,8 @@ ExplainTargetRel(Plan *plan, Index rti, ExplainState *es) rte = rt_fetch(rti, es->rtable); refname = (char *) list_nth(es->rtable_names, rti - 1); + if (refname == NULL) + refname = rte->eref->aliasname; switch (nodeTag(plan)) { @@ -2026,8 +2028,7 @@ ExplainTargetRel(Plan *plan, Index rti, ExplainState *es) quote_identifier(objectname)); else if (objectname != NULL) appendStringInfo(es->str, " %s", quote_identifier(objectname)); - if (refname != NULL && - (objectname == NULL || strcmp(refname, objectname) != 0)) + if (objectname == NULL || strcmp(refname, objectname) != 0) appendStringInfo(es->str, " %s", quote_identifier(refname)); } else @@ -2036,8 +2037,7 @@ ExplainTargetRel(Plan *plan, Index rti, ExplainState *es) ExplainPropertyText(objecttag, objectname, es); if (namespace != NULL) ExplainPropertyText("Schema", namespace, es); - if (refname != NULL) - ExplainPropertyText("Alias", refname, es); + ExplainPropertyText("Alias", refname, es); } } diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index aca670095b..e859379cb9 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -888,9 +888,7 @@ DefineDomain(CreateDomainStmt *stmt) */ defaultValue = deparse_expression(defaultExpr, - deparse_context_for(domainName, - InvalidOid), - false, false); + NIL, false, false); defaultValueBin = nodeToString(defaultExpr); } } @@ -2143,9 +2141,7 @@ AlterDomainDefault(List *names, Node *defaultRaw) * easier for pg_dump). */ defaultValue = deparse_expression(defaultExpr, - deparse_context_for(NameStr(typTup->typname), - InvalidOid), - false, false); + NIL, false, false); /* * Form an updated tuple with the new default and write it back. @@ -2941,14 +2937,9 @@ domainAddConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid, /* * Deparse it to produce text for consrc. - * - * Since VARNOs aren't allowed in domain constraints, relation context - * isn't required as anything other than a shell. */ ccsrc = deparse_expression(expr, - deparse_context_for(domainName, - InvalidOid), - false, false); + NIL, false, false); /* * Store the constraint in pg_constraint diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index f58418e0ce..a387030059 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -106,9 +106,16 @@ typedef struct * the current context's namespaces list. * * The rangetable is the list of actual RTEs from the query tree, and the - * cte list is the list of actual CTEs. rtable_names holds the alias name - * to be used for each RTE (either a C string, or NULL for nameless RTEs - * such as unnamed joins). + * cte list is the list of actual CTEs. + * + * rtable_names holds the alias name to be used for each RTE (either a C + * string, or NULL for nameless RTEs such as unnamed joins). + * rtable_columns holds the column alias names to be used for each RTE. + * + * 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. * * When deparsing plan trees, there is always just a single item in the * deparse_namespace list (since a plan tree never contains Vars with @@ -124,7 +131,11 @@ typedef struct { List *rtable; /* List of RangeTblEntry nodes */ List *rtable_names; /* Parallel list of names for RTEs */ + List *rtable_columns; /* Parallel list of deparse_columns structs */ List *ctes; /* List of CommonTableExpr nodes */ + /* 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 */ /* Remaining fields are used only when deparsing a Plan tree: */ PlanState *planstate; /* immediate parent of current expression */ List *ancestors; /* ancestors of planstate */ @@ -135,6 +146,111 @@ typedef struct List *index_tlist; /* referent for INDEX_VAR Vars */ } deparse_namespace; +/* + * Per-relation data about column alias names. + * + * Selecting aliases is unreasonably complicated because of the need to dump + * rules/views whose underlying tables may have had columns added, deleted, or + * renamed since the query was parsed. We must nonetheless print the rule/view + * in a form that can be reloaded and will produce the same results as before. + * + * For each RTE used in the query, we must assign column aliases that are + * unique within that RTE. SQL does not require this of the original query, + * but due to factors such as *-expansion we need to be able to uniquely + * reference every column in a decompiled query. As long as we qualify all + * column references, per-RTE uniqueness is sufficient for that. + * + * However, we can't ensure per-column name uniqueness for unnamed join RTEs, + * since they just inherit column names from their input RTEs, and we can't + * rename the columns at the join level. Most of the time this isn't an issue + * because we don't need to reference the join's output columns as such; we + * can reference the input columns instead. That approach fails for merged + * FULL JOIN USING columns, however, so when we have one of those in an + * unnamed join, we have to make that column's alias globally unique across + * the whole query to ensure it can be referenced unambiguously. + * + * Another problem is that a JOIN USING clause requires the columns to be + * merged to have the same aliases in both input RTEs. To handle that, we do + * USING-column alias assignment in a recursive traversal of the query's + * jointree. When descending through a JOIN with USING, we preassign the + * USING column names to the child columns, overriding other rules for column + * alias assignment. + * + * Another problem is that if a JOIN's input tables have had columns added or + * deleted since the query was parsed, we must generate a column alias list + * for the join that matches the current set of input columns --- otherwise, a + * change in the number of columns in the left input would throw off matching + * of aliases to columns of the right input. Thus, positions in the printable + * column alias list are not necessarily one-for-one with varattnos of the + * JOIN, so we need a separate new_colnames[] array for printing purposes. + */ +typedef struct +{ + /* + * colnames is an array containing column aliases to use for columns that + * existed when the query was parsed. Dropped columns have NULL entries. + * This array can be directly indexed by varattno to get a Var's name. + * + * Non-NULL entries are guaranteed unique within the RTE, *except* when + * this is for an unnamed JOIN RTE. In that case we merely copy up names + * from the two input RTEs. + * + * During the recursive descent in set_using_names(), forcible assignment + * of a child RTE's column name is represented by pre-setting that element + * of the child's colnames array. So at that stage, NULL entries in this + * array just mean that no name has been preassigned, not necessarily that + * the column is dropped. + */ + int num_cols; /* length of colnames[] array */ + char **colnames; /* array of C strings and NULLs */ + + /* + * new_colnames is an array containing column aliases to use for columns + * that would exist if the query was re-parsed against the current + * definitions of its base tables. This is what to print as the column + * alias list for the RTE. This array does not include dropped columns, + * but it will include columns added since original parsing. Indexes in + * it therefore have little to do with current varattno values. As above, + * entries are unique unless this is for an unnamed JOIN RTE. (In such an + * RTE, we never actually print this array, but we must compute it anyway + * for possible use in computing column names of upper joins.) The + * parallel array is_new_col marks which of these columns are new since + * original parsing. Entries with is_new_col false must match the + * non-NULL colnames entries one-for-one. + */ + int num_new_cols; /* length of new_colnames[] array */ + char **new_colnames; /* array of C strings */ + bool *is_new_col; /* array of bool flags */ + + /* This flag tells whether we should actually print a column alias list */ + bool printaliases; + + /* + * If this struct is for a JOIN RTE, we fill these fields during the + * set_using_names() pass to describe its relationship to its child RTEs. + * + * leftattnos and rightattnos are arrays with one entry per existing + * output column of the join (hence, indexable by join varattno). For a + * simple reference to a column of the left child, leftattnos[i] is the + * child RTE's attno and rightattnos[i] is zero; and conversely for a + * column of the right child. But for merged columns produced by JOIN + * USING/NATURAL JOIN, both leftattnos[i] and rightattnos[i] are nonzero. + * + * If it's a JOIN USING, usingNames holds the alias names selected for the + * merged columns (these might be different from the original USING list, + * if we had to modify names to achieve uniqueness). + */ + int leftrti; /* rangetable index of left child */ + int rightrti; /* rangetable index of right child */ + int *leftattnos; /* left-child varattnos of join cols, or 0 */ + int *rightattnos; /* right-child varattnos of join cols, or 0 */ + List *usingNames; /* names assigned to merged columns */ +} deparse_columns; + +/* This macro is analogous to rt_fetch(), but for deparse_columns structs */ +#define deparse_columns_fetch(rangetable_index, dpns) \ + ((deparse_columns *) list_nth((dpns)->rtable_columns, (rangetable_index)-1)) + /* ---------- * Global data @@ -181,6 +297,25 @@ static void set_rtable_names(deparse_namespace *dpns, List *parent_namespaces, Bitmapset *rels_used); static bool refname_is_unique(char *refname, deparse_namespace *dpns, List *parent_namespaces); +static void set_deparse_for_query(deparse_namespace *dpns, Query *query, + List *parent_namespaces); +static void set_simple_column_names(deparse_namespace *dpns); +static bool has_unnamed_full_join_using(Node *jtnode); +static void set_using_names(deparse_namespace *dpns, Node *jtnode); +static void set_relation_column_names(deparse_namespace *dpns, + RangeTblEntry *rte, + deparse_columns *colinfo); +static void set_join_column_names(deparse_namespace *dpns, RangeTblEntry *rte, + deparse_columns *colinfo); +static bool colname_is_unique(char *colname, deparse_namespace *dpns, + deparse_columns *colinfo); +static char *make_colname_unique(char *colname, deparse_namespace *dpns, + deparse_columns *colinfo); +static void expand_colnames_array_to(deparse_columns *colinfo, int n); +static void identify_join_columns(JoinExpr *j, RangeTblEntry *jrte, + deparse_columns *colinfo); +static void flatten_join_using_qual(Node *qual, + List **leftvars, List **rightvars); static char *get_rtable_name(int rtindex, deparse_context *context); static void set_deparse_planstate(deparse_namespace *dpns, PlanState *ps); static void push_child_plan(deparse_namespace *dpns, PlanState *ps, @@ -249,9 +384,9 @@ static void get_from_clause(Query *query, const char *prefix, deparse_context *context); static void get_from_clause_item(Node *jtnode, Query *query, deparse_context *context); -static void get_from_clause_alias(Alias *alias, RangeTblEntry *rte, +static void get_column_alias_list(deparse_columns *colinfo, deparse_context *context); -static void get_from_clause_coldeflist(List *names, +static void get_from_clause_coldeflist(deparse_columns *colinfo, List *types, List *typmods, List *collations, deparse_context *context); static void get_opclass_name(Oid opclass, Oid actual_datatype, @@ -704,6 +839,7 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty) dpns.rtable = list_make2(oldrte, newrte); dpns.ctes = NIL; set_rtable_names(&dpns, NIL, NULL); + set_simple_column_names(&dpns); /* Set up context with one-deep namespace stack */ context.buf = &buf; @@ -2133,13 +2269,14 @@ deparse_expression(Node *expr, List *dpcontext, * tree (ie, not the raw output of gram.y). * * dpcontext is a list of deparse_namespace nodes representing the context - * for interpreting Vars in the node tree. + * for interpreting Vars in the node tree. It can be NIL if no Vars are + * expected. * * forceprefix is TRUE to force all Vars to be prefixed with their table names. * * showimplicit is TRUE to force all implicit casts to be shown explicitly. * - * tries to pretty up the output according to prettyFlags and startIndent. + * Tries to pretty up the output according to prettyFlags and startIndent. * * The result is a palloc'd string. * ---------- @@ -2198,6 +2335,7 @@ deparse_context_for(const char *aliasname, Oid relid) dpns->rtable = list_make1(rte); dpns->ctes = NIL; set_rtable_names(dpns, NIL, NULL); + set_simple_column_names(dpns); /* Return a one-deep namespace stack */ return list_make1(dpns); @@ -2241,6 +2379,13 @@ deparse_context_for_planstate(Node *planstate, List *ancestors, dpns->rtable_names = rtable_names; dpns->ctes = NIL; + /* + * Set up column name aliases. We will get rather bogus results for join + * RTEs, but that doesn't matter because plan trees don't contain any join + * alias Vars. + */ + set_simple_column_names(dpns); + /* Set our attention on the specific plan node passed in */ set_deparse_planstate(dpns, (PlanState *) planstate); dpns->ancestors = ancestors; @@ -2252,8 +2397,8 @@ deparse_context_for_planstate(Node *planstate, List *ancestors, /* * select_rtable_names_for_explain - Select RTE aliases for EXPLAIN * - * Determine the aliases we'll use during an EXPLAIN operation. This is - * just a frontend to set_rtable_names. We have to expose the aliases + * Determine the relation aliases we'll use during an EXPLAIN operation. + * This is just a frontend to set_rtable_names. We have to expose the aliases * to EXPLAIN because EXPLAIN needs to know the right alias names to print. */ List * @@ -2265,12 +2410,13 @@ select_rtable_names_for_explain(List *rtable, Bitmapset *rels_used) dpns.rtable = rtable; dpns.ctes = NIL; set_rtable_names(&dpns, NIL, rels_used); + /* We needn't bother computing column aliases yet */ return dpns.rtable_names; } /* - * set_rtable_names: select RTE aliases to be used in printing variables + * set_rtable_names: select RTE aliases to be used in printing a query * * We fill in dpns->rtable_names with a list of names that is one-for-one with * the already-filled dpns->rtable list. Each RTE name is unique among those @@ -2278,6 +2424,9 @@ select_rtable_names_for_explain(List *rtable, Bitmapset *rels_used) * parent_namespaces. * * If rels_used isn't NULL, only RTE indexes listed in it are given aliases. + * + * Note that this function is only concerned with relation names, not column + * names. */ static void set_rtable_names(deparse_namespace *dpns, List *parent_namespaces, @@ -2371,6 +2520,1002 @@ refname_is_unique(char *refname, deparse_namespace *dpns, return true; } +/* + * set_deparse_for_query: set up deparse_namespace for deparsing a Query tree + * + * For convenience, this is defined to initialize the deparse_namespace struct + * from scratch. + */ +static void +set_deparse_for_query(deparse_namespace *dpns, Query *query, + List *parent_namespaces) +{ + ListCell *lc; + ListCell *lc2; + + /* Initialize *dpns and fill rtable/ctes links */ + memset(dpns, 0, sizeof(deparse_namespace)); + dpns->rtable = query->rtable; + dpns->ctes = query->cteList; + + /* Assign a unique relation alias to each RTE */ + set_rtable_names(dpns, parent_namespaces, NULL); + + /* Initialize dpns->rtable_columns to contain zeroed structs */ + dpns->rtable_columns = NIL; + while (list_length(dpns->rtable_columns) < list_length(dpns->rtable)) + dpns->rtable_columns = lappend(dpns->rtable_columns, + palloc0(sizeof(deparse_columns))); + + /* Detect whether global uniqueness of USING names is needed */ + dpns->unique_using = has_unnamed_full_join_using((Node *) query->jointree); + + /* + * Select names for columns merged by USING, via a recursive pass over the + * query jointree. + */ + set_using_names(dpns, (Node *) query->jointree); + + /* + * Now assign remaining column aliases for each RTE. We do this in a + * linear scan of the rtable, so as to process RTEs whether or not they + * are in the jointree (we mustn't miss NEW.*, INSERT target relations, + * etc). JOIN RTEs must be processed after their children, but this is + * okay because they appear later in the rtable list than their children + * (cf Asserts in identify_join_columns()). + */ + forboth(lc, dpns->rtable, lc2, dpns->rtable_columns) + { + RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc); + deparse_columns *colinfo = (deparse_columns *) lfirst(lc2); + + if (rte->rtekind == RTE_JOIN) + set_join_column_names(dpns, rte, colinfo); + else + set_relation_column_names(dpns, rte, colinfo); + } +} + +/* + * set_simple_column_names: fill in column aliases for non-query situations + * + * This handles EXPLAIN and cases where we only have relation RTEs. Without + * a join tree, we can't do anything smart about join RTEs, but we don't + * need to (note that EXPLAIN should never see join alias Vars anyway). + * If we do hit a join RTE we'll just process it like a non-table base RTE. + */ +static void +set_simple_column_names(deparse_namespace *dpns) +{ + ListCell *lc; + ListCell *lc2; + + /* Initialize dpns->rtable_columns to contain zeroed structs */ + dpns->rtable_columns = NIL; + while (list_length(dpns->rtable_columns) < list_length(dpns->rtable)) + dpns->rtable_columns = lappend(dpns->rtable_columns, + palloc0(sizeof(deparse_columns))); + + /* Assign unique column aliases within each RTE */ + forboth(lc, dpns->rtable, lc2, dpns->rtable_columns) + { + RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc); + deparse_columns *colinfo = (deparse_columns *) lfirst(lc2); + + set_relation_column_names(dpns, rte, colinfo); + } +} + +/* + * has_unnamed_full_join_using: search jointree for unnamed FULL JOIN USING + * + * Merged columns of a FULL JOIN USING act differently from either of the + * input columns, so they have to be referenced as columns of the JOIN not + * as columns of either input. And this is problematic if the join is + * unnamed (alias-less): we cannot qualify the column's name with an RTE + * name, since there is none. (Forcibly assigning an alias to the join is + * not a solution, since that will prevent legal references to tables below + * the join.) To ensure that every column in the query is unambiguously + * referenceable, we must assign such merged columns names that are globally + * unique across the whole query, aliasing other columns out of the way as + * necessary. + * + * Because the ensuing re-aliasing is fairly damaging to the readability of + * the query, we don't do this unless we have to. So, we must pre-scan + * the join tree to see if we have to, before starting set_using_names(). + */ +static bool +has_unnamed_full_join_using(Node *jtnode) +{ + if (IsA(jtnode, RangeTblRef)) + { + /* nothing to do here */ + } + else if (IsA(jtnode, FromExpr)) + { + FromExpr *f = (FromExpr *) jtnode; + ListCell *lc; + + foreach(lc, f->fromlist) + { + if (has_unnamed_full_join_using((Node *) lfirst(lc))) + return true; + } + } + else if (IsA(jtnode, JoinExpr)) + { + JoinExpr *j = (JoinExpr *) jtnode; + + /* Is it an unnamed FULL JOIN with USING? */ + if (j->alias == NULL && + j->jointype == JOIN_FULL && + j->usingClause) + return true; + + /* Nope, but inspect children */ + if (has_unnamed_full_join_using(j->larg)) + return true; + if (has_unnamed_full_join_using(j->rarg)) + return true; + } + else + elog(ERROR, "unrecognized node type: %d", + (int) nodeTag(jtnode)); + return false; +} + +/* + * set_using_names: select column aliases to be used for merged USING columns + * + * We do this during a recursive descent of the query jointree. + * dpns->unique_using must already be set to determine the global strategy. + * + * Column alias info is saved in the dpns->rtable_columns list, which is + * assumed to be filled with pre-zeroed deparse_columns structs. + */ +static void +set_using_names(deparse_namespace *dpns, Node *jtnode) +{ + if (IsA(jtnode, RangeTblRef)) + { + /* nothing to do now */ + } + else if (IsA(jtnode, FromExpr)) + { + FromExpr *f = (FromExpr *) jtnode; + ListCell *lc; + + foreach(lc, f->fromlist) + set_using_names(dpns, (Node *) lfirst(lc)); + } + else if (IsA(jtnode, JoinExpr)) + { + JoinExpr *j = (JoinExpr *) jtnode; + RangeTblEntry *rte = rt_fetch(j->rtindex, dpns->rtable); + deparse_columns *colinfo = deparse_columns_fetch(j->rtindex, dpns); + int *leftattnos; + int *rightattnos; + deparse_columns *leftcolinfo; + deparse_columns *rightcolinfo; + int i; + ListCell *lc; + + /* Get info about the shape of the join */ + identify_join_columns(j, rte, colinfo); + leftattnos = colinfo->leftattnos; + rightattnos = colinfo->rightattnos; + + /* Look up the not-yet-filled-in child deparse_columns structs */ + leftcolinfo = deparse_columns_fetch(colinfo->leftrti, dpns); + rightcolinfo = deparse_columns_fetch(colinfo->rightrti, dpns); + + /* + * If this join is unnamed, then we cannot substitute new aliases at + * this level, so any name requirements pushed down to here must be + * pushed down again to the children. + */ + if (rte->alias == NULL) + { + for (i = 0; i < colinfo->num_cols; i++) + { + char *colname = colinfo->colnames[i]; + + if (colname == NULL) + continue; + + /* Push down to left column, unless it's a system column */ + if (leftattnos[i] > 0) + { + expand_colnames_array_to(leftcolinfo, leftattnos[i]); + leftcolinfo->colnames[leftattnos[i] - 1] = colname; + } + + /* Same on the righthand side */ + if (rightattnos[i] > 0) + { + expand_colnames_array_to(rightcolinfo, rightattnos[i]); + rightcolinfo->colnames[rightattnos[i] - 1] = colname; + } + } + } + + /* + * If there's a USING clause, select the USING column names and push + * those names down to the children. We have two strategies: + * + * If dpns->unique_using is TRUE, we force all USING names to be + * unique across the whole query level. In principle we'd only need + * the names of USING columns in unnamed full joins to be globally + * unique, but to safely assign all USING names in a single pass, we + * have to enforce the same uniqueness rule for all of them. However, + * if a USING column's name has been pushed down from the parent, we + * should use it as-is rather than making a uniqueness adjustment. + * This is necessary when we're at an unnamed join, and it creates no + * risk of ambiguity. Also, if there's a user-written output alias + * for a merged column, we prefer to use that rather than the input + * name; this simplifies the logic and seems likely to lead to less + * aliasing overall. + * + * If dpns->unique_using is FALSE, we only need USING names to be + * unique within their own join RTE. We still need to honor + * pushed-down names, though. + * + * 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. + */ + if (j->usingClause) + { + /* USING names must correspond to the first join output columns */ + expand_colnames_array_to(colinfo, list_length(j->usingClause)); + i = 0; + foreach(lc, j->usingClause) + { + char *colname = strVal(lfirst(lc)); + + /* Assert it's a merged column */ + Assert(leftattnos[i] != 0 && rightattnos[i] != 0); + + /* Adopt passed-down name if any, else select unique name */ + if (colinfo->colnames[i] != NULL) + colname = colinfo->colnames[i]; + else + { + /* Prefer user-written output alias if any */ + if (rte->alias && i < list_length(rte->alias->colnames)) + colname = strVal(list_nth(rte->alias->colnames, i)); + /* Make it appropriately unique */ + colname = make_colname_unique(colname, dpns, colinfo); + if (dpns->unique_using) + dpns->using_names = lappend(dpns->using_names, + colname); + /* Save it as output column name, too */ + colinfo->colnames[i] = colname; + } + + /* Remember selected names for use later */ + colinfo->usingNames = lappend(colinfo->usingNames, colname); + + /* Push down to left column, unless it's a system column */ + if (leftattnos[i] > 0) + { + expand_colnames_array_to(leftcolinfo, leftattnos[i]); + leftcolinfo->colnames[leftattnos[i] - 1] = colname; + } + + /* Same on the righthand side */ + if (rightattnos[i] > 0) + { + expand_colnames_array_to(rightcolinfo, rightattnos[i]); + rightcolinfo->colnames[rightattnos[i] - 1] = colname; + } + + i++; + } + } + + /* Now recursively assign USING column names in children */ + set_using_names(dpns, j->larg); + set_using_names(dpns, j->rarg); + } + else + elog(ERROR, "unrecognized node type: %d", + (int) nodeTag(jtnode)); +} + +/* + * set_relation_column_names: select column aliases for a non-join RTE + * + * Column alias info is saved in *colinfo, which is assumed to be pre-zeroed. + * If any colnames entries are already filled in, those override local + * choices. + */ +static void +set_relation_column_names(deparse_namespace *dpns, RangeTblEntry *rte, + deparse_columns *colinfo) +{ + int ncolumns; + char **real_colnames; + bool changed_any; + int noldcolumns; + int i; + int j; + + /* + * Extract the RTE's "real" column names. This is comparable to + * get_rte_attribute_name, except that it's important to disregard dropped + * columns. We put NULL into the array for a dropped column. + */ + if (rte->rtekind == RTE_RELATION) + { + /* Relation --- look to the system catalogs for up-to-date info */ + Relation rel; + TupleDesc tupdesc; + + rel = relation_open(rte->relid, AccessShareLock); + tupdesc = RelationGetDescr(rel); + + ncolumns = tupdesc->natts; + real_colnames = (char **) palloc(ncolumns * sizeof(char *)); + + for (i = 0; i < ncolumns; i++) + { + if (tupdesc->attrs[i]->attisdropped) + real_colnames[i] = NULL; + else + real_colnames[i] = pstrdup(NameStr(tupdesc->attrs[i]->attname)); + } + relation_close(rel, AccessShareLock); + } + else + { + /* Otherwise use the column names from eref */ + ListCell *lc; + + ncolumns = list_length(rte->eref->colnames); + real_colnames = (char **) palloc(ncolumns * sizeof(char *)); + + i = 0; + foreach(lc, rte->eref->colnames) + { + real_colnames[i] = strVal(lfirst(lc)); + i++; + } + } + + /* + * Ensure colinfo->colnames has a slot for each column. (It could be long + * enough already, if we pushed down a name for the last column.) Note: + * it's possible that there are now more columns than there were when the + * query was parsed, ie colnames could be longer than rte->eref->colnames. + * We must assign unique aliases to the new columns too, else there could + * be unresolved conflicts when the view/rule is reloaded. + */ + expand_colnames_array_to(colinfo, ncolumns); + Assert(colinfo->num_cols == ncolumns); + + /* + * Make sufficiently large new_colnames and is_new_col arrays, too. + * + * Note: because we leave colinfo->num_new_cols zero until after the loop, + * colname_is_unique will not consult that array, which is fine because it + * would only be duplicate effort. + */ + colinfo->new_colnames = (char **) palloc(ncolumns * sizeof(char *)); + colinfo->is_new_col = (bool *) palloc(ncolumns * sizeof(bool)); + + /* + * Scan the columns, select a unique alias for each one, and store it in + * colinfo->colnames and colinfo->new_colnames. The former array has NULL + * entries for dropped columns, the latter omits them. Also mark + * new_colnames entries as to whether they are new since parse time; this + * is the case for entries beyond the length of rte->eref->colnames. + */ + noldcolumns = list_length(rte->eref->colnames); + changed_any = false; + j = 0; + for (i = 0; i < ncolumns; i++) + { + char *real_colname = real_colnames[i]; + char *colname = colinfo->colnames[i]; + + /* Skip dropped columns */ + if (real_colname == NULL) + { + Assert(colname == NULL); /* colnames[i] is already NULL */ + continue; + } + + /* If alias already assigned, that's what to use */ + if (colname == NULL) + { + /* If user wrote an alias, prefer that over real column name */ + if (rte->alias && i < list_length(rte->alias->colnames)) + colname = strVal(list_nth(rte->alias->colnames, i)); + else + colname = real_colname; + + /* Unique-ify and insert into colinfo */ + colname = make_colname_unique(colname, dpns, colinfo); + + colinfo->colnames[i] = colname; + } + + /* Put names of non-dropped columns in new_colnames[] too */ + colinfo->new_colnames[j] = colname; + /* And mark them as new or not */ + colinfo->is_new_col[j] = (i >= noldcolumns); + j++; + + /* Remember if any assigned aliases differ from "real" name */ + if (!changed_any && strcmp(colname, real_colname) != 0) + changed_any = true; + } + + /* + * Set correct length for new_colnames[] array. (Note: if columns have + * been added, colinfo->num_cols includes them, which is not really quite + * right but is harmless, since any new columns must be at the end where + * they won't affect varattnos of pre-existing columns.) + */ + colinfo->num_new_cols = j; + + /* + * For a relation RTE, we need only print the alias column names if any + * 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 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"). + */ + if (rte->rtekind == RTE_RELATION) + colinfo->printaliases = changed_any; + else if (rte->rtekind == RTE_FUNCTION) + colinfo->printaliases = true; + else if (rte->alias && rte->alias->colnames != NIL) + colinfo->printaliases = true; + else + colinfo->printaliases = changed_any; +} + +/* + * set_join_column_names: select column aliases for a join RTE + * + * Column alias info is saved in *colinfo, which is assumed to be pre-zeroed. + * If any colnames entries are already filled in, those override local + * choices. Also, names for USING columns were already chosen by + * set_using_names(). We further expect that column alias selection has been + * completed for both input RTEs. + */ +static void +set_join_column_names(deparse_namespace *dpns, RangeTblEntry *rte, + deparse_columns *colinfo) +{ + deparse_columns *leftcolinfo; + deparse_columns *rightcolinfo; + bool changed_any; + int noldcolumns; + int nnewcolumns; + Bitmapset *leftmerged = NULL; + Bitmapset *rightmerged = NULL; + int i; + int j; + int ic; + int jc; + + /* Look up the previously-filled-in child deparse_columns structs */ + leftcolinfo = deparse_columns_fetch(colinfo->leftrti, dpns); + rightcolinfo = deparse_columns_fetch(colinfo->rightrti, dpns); + + /* + * Ensure colinfo->colnames has a slot for each column. (It could be long + * enough already, if we pushed down a name for the last column.) Note: + * it's possible that one or both inputs now have more columns than there + * were when the query was parsed, but we'll deal with that below. We + * only need entries in colnames for pre-existing columns. + */ + noldcolumns = list_length(rte->eref->colnames); + expand_colnames_array_to(colinfo, noldcolumns); + Assert(colinfo->num_cols == noldcolumns); + + /* + * Scan the join output columns, select an alias for each one, and store + * it in colinfo->colnames. If there are USING columns, set_using_names() + * already selected their names, so we can start the loop at the first + * non-merged column. + */ + changed_any = false; + for (i = list_length(colinfo->usingNames); i < noldcolumns; i++) + { + char *colname = colinfo->colnames[i]; + char *real_colname; + + /* Get the child column name */ + if (colinfo->leftattnos[i] > 0) + real_colname = leftcolinfo->colnames[colinfo->leftattnos[i] - 1]; + else if (colinfo->rightattnos[i] > 0) + real_colname = rightcolinfo->colnames[colinfo->rightattnos[i] - 1]; + else + { + /* We're joining system columns --- use eref name */ + real_colname = (char *) list_nth(rte->eref->colnames, i); + } + + /* Ignore dropped columns (only possible for non-merged column) */ + if (real_colname == NULL) + { + Assert(colname == NULL); + continue; + } + + /* In an unnamed join, just report child column names as-is */ + if (rte->alias == NULL) + { + colinfo->colnames[i] = real_colname; + continue; + } + + /* If alias already assigned, that's what to use */ + if (colname == NULL) + { + /* If user wrote an alias, prefer that over real column name */ + if (rte->alias && i < list_length(rte->alias->colnames)) + colname = strVal(list_nth(rte->alias->colnames, i)); + else + colname = real_colname; + + /* Unique-ify and insert into colinfo */ + colname = make_colname_unique(colname, dpns, colinfo); + + colinfo->colnames[i] = colname; + } + + /* Remember if any assigned aliases differ from "real" name */ + if (!changed_any && strcmp(colname, real_colname) != 0) + changed_any = true; + } + + /* + * Calculate number of columns the join would have if it were re-parsed + * now, and create storage for the new_colnames and is_new_col arrays. + * + * Note: colname_is_unique will be consulting new_colnames[] during the + * loops below, so its not-yet-filled entries must be zeroes. + */ + nnewcolumns = leftcolinfo->num_new_cols + rightcolinfo->num_new_cols - + list_length(colinfo->usingNames); + colinfo->num_new_cols = nnewcolumns; + colinfo->new_colnames = (char **) palloc0(nnewcolumns * sizeof(char *)); + colinfo->is_new_col = (bool *) palloc0(nnewcolumns * sizeof(bool)); + + /* + * Generating the new_colnames array is a bit tricky since any new columns + * added since parse time must be inserted in the right places. This code + * must match the parser, which will order a join's columns as merged + * columns first (in USING-clause order), then non-merged columns from the + * left input (in attnum order), then non-merged columns from the right + * input (ditto). If one of the inputs is itself a join, its columns will + * be ordered according to the same rule, which means newly-added columns + * might not be at the end. We can figure out what's what by consulting + * the leftattnos and rightattnos arrays plus the input is_new_col arrays. + * + * In these loops, i indexes leftattnos/rightattnos (so it's join varattno + * less one), j indexes new_colnames/is_new_col, and ic/jc have similar + * meanings for the current child RTE. + */ + + /* Handle merged columns; they are first and can't be new */ + i = j = 0; + while (i < noldcolumns && + colinfo->leftattnos[i] != 0 && + colinfo->rightattnos[i] != 0) + { + /* column name is already determined and known unique */ + colinfo->new_colnames[j] = colinfo->colnames[i]; + colinfo->is_new_col[j] = false; + + /* build bitmapsets of child attnums of merged columns */ + if (colinfo->leftattnos[i] > 0) + leftmerged = bms_add_member(leftmerged, colinfo->leftattnos[i]); + if (colinfo->rightattnos[i] > 0) + rightmerged = bms_add_member(rightmerged, colinfo->rightattnos[i]); + + i++, j++; + } + + /* Handle non-merged left-child columns */ + ic = 0; + for (jc = 0; jc < leftcolinfo->num_new_cols; jc++) + { + char *child_colname = leftcolinfo->new_colnames[jc]; + + if (!leftcolinfo->is_new_col[jc]) + { + /* Advance ic to next non-dropped old column of left child */ + while (ic < leftcolinfo->num_cols && + leftcolinfo->colnames[ic] == NULL) + ic++; + Assert(ic < leftcolinfo->num_cols); + ic++; + /* If it is a merged column, we already processed it */ + if (bms_is_member(ic, leftmerged)) + continue; + /* Else, advance i to the corresponding existing join column */ + while (i < colinfo->num_cols && + colinfo->colnames[i] == NULL) + i++; + Assert(i < colinfo->num_cols); + Assert(ic == colinfo->leftattnos[i]); + /* Use the already-assigned name of this column */ + colinfo->new_colnames[j] = colinfo->colnames[i]; + i++; + } + else + { + /* + * Unique-ify the new child column name and assign, unless we're + * in an unnamed join, in which case just copy + */ + if (rte->alias != NULL) + { + colinfo->new_colnames[j] = + make_colname_unique(child_colname, dpns, colinfo); + if (!changed_any && + strcmp(colinfo->new_colnames[j], child_colname) != 0) + changed_any = true; + } + else + colinfo->new_colnames[j] = child_colname; + } + + colinfo->is_new_col[j] = leftcolinfo->is_new_col[jc]; + j++; + } + + /* Handle non-merged right-child columns in exactly the same way */ + ic = 0; + for (jc = 0; jc < rightcolinfo->num_new_cols; jc++) + { + char *child_colname = rightcolinfo->new_colnames[jc]; + + if (!rightcolinfo->is_new_col[jc]) + { + /* Advance ic to next non-dropped old column of right child */ + while (ic < rightcolinfo->num_cols && + rightcolinfo->colnames[ic] == NULL) + ic++; + Assert(ic < rightcolinfo->num_cols); + ic++; + /* If it is a merged column, we already processed it */ + if (bms_is_member(ic, rightmerged)) + continue; + /* Else, advance i to the corresponding existing join column */ + while (i < colinfo->num_cols && + colinfo->colnames[i] == NULL) + i++; + Assert(i < colinfo->num_cols); + Assert(ic == colinfo->rightattnos[i]); + /* Use the already-assigned name of this column */ + colinfo->new_colnames[j] = colinfo->colnames[i]; + i++; + } + else + { + /* + * Unique-ify the new child column name and assign, unless we're + * in an unnamed join, in which case just copy + */ + if (rte->alias != NULL) + { + colinfo->new_colnames[j] = + make_colname_unique(child_colname, dpns, colinfo); + if (!changed_any && + strcmp(colinfo->new_colnames[j], child_colname) != 0) + changed_any = true; + } + else + colinfo->new_colnames[j] = child_colname; + } + + colinfo->is_new_col[j] = rightcolinfo->is_new_col[jc]; + j++; + } + + /* Assert we processed the right number of columns */ +#ifdef USE_ASSERT_CHECKING + while (i < colinfo->num_cols && colinfo->colnames[i] == NULL) + i++; + Assert(i == colinfo->num_cols); + Assert(j == nnewcolumns); +#endif + + /* + * For a named join, print column aliases if we changed any from the child + * names. Unnamed joins cannot print aliases. + */ + if (rte->alias != NULL) + colinfo->printaliases = changed_any; + else + colinfo->printaliases = false; +} + +/* + * colname_is_unique: is colname distinct from already-chosen column names? + * + * dpns is query-wide info, colinfo is for the column's RTE + */ +static bool +colname_is_unique(char *colname, deparse_namespace *dpns, + deparse_columns *colinfo) +{ + int i; + ListCell *lc; + + /* Check against already-assigned column aliases within RTE */ + for (i = 0; i < colinfo->num_cols; i++) + { + char *oldname = colinfo->colnames[i]; + + if (oldname && strcmp(oldname, colname) == 0) + return false; + } + + /* + * If we're building a new_colnames array, check that too (this will be + * partially but not completely redundant with the previous checks) + */ + for (i = 0; i < colinfo->num_new_cols; i++) + { + char *oldname = colinfo->new_colnames[i]; + + if (oldname && strcmp(oldname, colname) == 0) + return false; + } + + /* Also check against USING-column names that must be globally unique */ + foreach(lc, dpns->using_names) + { + char *oldname = (char *) lfirst(lc); + + if (strcmp(oldname, colname) == 0) + return false; + } + + return true; +} + +/* + * make_colname_unique: modify colname if necessary to make it unique + * + * dpns is query-wide info, colinfo is for the column's RTE + */ +static char * +make_colname_unique(char *colname, deparse_namespace *dpns, + deparse_columns *colinfo) +{ + /* + * If the selected name isn't unique, append digits to make it so + */ + if (!colname_is_unique(colname, dpns, colinfo)) + { + char *modname = (char *) palloc(strlen(colname) + 32); + int i = 0; + + do + { + sprintf(modname, "%s_%d", colname, ++i); + } while (!colname_is_unique(modname, dpns, colinfo)); + colname = modname; + } + return colname; +} + +/* + * expand_colnames_array_to: make colinfo->colnames at least n items long + * + * Any added array entries are initialized to zero. + */ +static void +expand_colnames_array_to(deparse_columns *colinfo, int n) +{ + if (n > colinfo->num_cols) + { + if (colinfo->colnames == NULL) + colinfo->colnames = (char **) palloc0(n * sizeof(char *)); + else + { + colinfo->colnames = (char **) repalloc(colinfo->colnames, + n * sizeof(char *)); + memset(colinfo->colnames + colinfo->num_cols, 0, + (n - colinfo->num_cols) * sizeof(char *)); + } + colinfo->num_cols = n; + } +} + +/* + * identify_join_columns: figure out where columns of a join come from + * + * Fills the join-specific fields of the colinfo struct, except for + * usingNames which is filled later. + */ +static void +identify_join_columns(JoinExpr *j, RangeTblEntry *jrte, + deparse_columns *colinfo) +{ + int numjoincols; + int i; + ListCell *lc; + + /* Extract left/right child RT indexes */ + if (IsA(j->larg, RangeTblRef)) + colinfo->leftrti = ((RangeTblRef *) j->larg)->rtindex; + else if (IsA(j->larg, JoinExpr)) + colinfo->leftrti = ((JoinExpr *) j->larg)->rtindex; + else + elog(ERROR, "unrecognized node type in jointree: %d", + (int) nodeTag(j->larg)); + if (IsA(j->rarg, RangeTblRef)) + colinfo->rightrti = ((RangeTblRef *) j->rarg)->rtindex; + else if (IsA(j->rarg, JoinExpr)) + colinfo->rightrti = ((JoinExpr *) j->rarg)->rtindex; + else + elog(ERROR, "unrecognized node type in jointree: %d", + (int) nodeTag(j->rarg)); + + /* Assert children will be processed earlier than join in second pass */ + Assert(colinfo->leftrti < j->rtindex); + Assert(colinfo->rightrti < j->rtindex); + + /* Initialize result arrays with zeroes */ + numjoincols = list_length(jrte->joinaliasvars); + Assert(numjoincols == list_length(jrte->eref->colnames)); + colinfo->leftattnos = (int *) palloc0(numjoincols * sizeof(int)); + colinfo->rightattnos = (int *) palloc0(numjoincols * sizeof(int)); + + /* Scan the joinaliasvars list to identify simple column references */ + i = 0; + foreach(lc, jrte->joinaliasvars) + { + Var *aliasvar = (Var *) lfirst(lc); + + if (IsA(aliasvar, Var)) + { + Assert(aliasvar->varlevelsup == 0); + Assert(aliasvar->varattno != 0); + if (aliasvar->varno == colinfo->leftrti) + colinfo->leftattnos[i] = aliasvar->varattno; + else if (aliasvar->varno == colinfo->rightrti) + colinfo->rightattnos[i] = aliasvar->varattno; + else + elog(ERROR, "unexpected varno %d in JOIN RTE", + aliasvar->varno); + } + else if (IsA(aliasvar, CoalesceExpr)) + { + /* + * It's a merged column in FULL JOIN USING. Ignore it for now and + * let the code below identify the merged columns. + */ + } + else + { + /* + * Although NULL constants can appear in joinaliasvars lists + * during planning, we shouldn't see any here, since the Query + * tree hasn't been through AcquireRewriteLocks(). + */ + elog(ERROR, "unrecognized node type in join alias vars: %d", + (int) nodeTag(aliasvar)); + } + + i++; + } + + /* + * If there's a USING clause, deconstruct the join quals to identify the + * merged columns. This is a tad painful but if we cannot rely on the + * column names, there is no other representation of which columns were + * joined by USING. (Unless the join type is FULL, we can't tell from the + * joinaliasvars list which columns are merged.) Note: we assume that the + * merged columns are the first output column(s) of the join. + */ + if (j->usingClause) + { + List *leftvars = NIL; + List *rightvars = NIL; + ListCell *lc2; + + /* Extract left- and right-side Vars from the qual expression */ + flatten_join_using_qual(j->quals, &leftvars, &rightvars); + Assert(list_length(leftvars) == list_length(j->usingClause)); + Assert(list_length(rightvars) == list_length(j->usingClause)); + + /* Mark the output columns accordingly */ + i = 0; + forboth(lc, leftvars, lc2, rightvars) + { + Var *leftvar = (Var *) lfirst(lc); + Var *rightvar = (Var *) lfirst(lc2); + + Assert(leftvar->varlevelsup == 0); + Assert(leftvar->varattno != 0); + if (leftvar->varno != colinfo->leftrti) + elog(ERROR, "unexpected varno %d in JOIN USING qual", + leftvar->varno); + colinfo->leftattnos[i] = leftvar->varattno; + + Assert(rightvar->varlevelsup == 0); + Assert(rightvar->varattno != 0); + if (rightvar->varno != colinfo->rightrti) + elog(ERROR, "unexpected varno %d in JOIN USING qual", + rightvar->varno); + colinfo->rightattnos[i] = rightvar->varattno; + + i++; + } + } +} + +/* + * flatten_join_using_qual: extract Vars being joined from a JOIN/USING qual + * + * We assume that transformJoinUsingClause won't have produced anything except + * AND nodes, equality operator nodes, and possibly implicit coercions, and + * that the AND node inputs match left-to-right with the original USING list. + * + * Caller must initialize the result lists to NIL. + */ +static void +flatten_join_using_qual(Node *qual, List **leftvars, List **rightvars) +{ + if (IsA(qual, BoolExpr)) + { + /* Handle AND nodes by recursion */ + BoolExpr *b = (BoolExpr *) qual; + ListCell *lc; + + Assert(b->boolop == AND_EXPR); + foreach(lc, b->args) + { + flatten_join_using_qual((Node *) lfirst(lc), + leftvars, rightvars); + } + } + else if (IsA(qual, OpExpr)) + { + /* Otherwise we should have an equality operator */ + OpExpr *op = (OpExpr *) qual; + Var *var; + + if (list_length(op->args) != 2) + elog(ERROR, "unexpected unary operator in JOIN/USING qual"); + /* Arguments should be Vars with perhaps implicit coercions */ + var = (Var *) strip_implicit_coercions((Node *) linitial(op->args)); + if (!IsA(var, Var)) + elog(ERROR, "unexpected node type in JOIN/USING qual: %d", + (int) nodeTag(var)); + *leftvars = lappend(*leftvars, var); + var = (Var *) strip_implicit_coercions((Node *) lsecond(op->args)); + if (!IsA(var, Var)) + elog(ERROR, "unexpected node type in JOIN/USING qual: %d", + (int) nodeTag(var)); + *rightvars = lappend(*rightvars, var); + } + else + { + /* Perhaps we have an implicit coercion to boolean? */ + Node *q = strip_implicit_coercions(qual); + + if (q != qual) + flatten_join_using_qual(q, leftvars, rightvars); + else + elog(ERROR, "unexpected node type in JOIN/USING qual: %d", + (int) nodeTag(qual)); + } +} + /* * get_rtable_name: convenience function to get a previously assigned RTE alias * @@ -2684,10 +3829,7 @@ make_ruledef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc, context.wrapColumn = WRAP_COLUMN_DEFAULT; context.indentLevel = PRETTYINDENT_STD; - memset(&dpns, 0, sizeof(dpns)); - dpns.rtable = query->rtable; - dpns.ctes = query->cteList; - set_rtable_names(&dpns, NIL, NULL); + set_deparse_for_query(&dpns, query, NIL); get_rule_expr(qual, &context, false); } @@ -2835,10 +3977,7 @@ get_query_def(Query *query, StringInfo buf, List *parentnamespace, context.wrapColumn = wrapColumn; context.indentLevel = startIndent; - memset(&dpns, 0, sizeof(dpns)); - dpns.rtable = query->rtable; - dpns.ctes = query->cteList; - set_rtable_names(&dpns, parentnamespace, NULL); + set_deparse_for_query(&dpns, query, parentnamespace); switch (query->commandType) { @@ -4010,7 +5149,7 @@ get_utility_query_def(Query *query, deparse_context *context) * name in the query. * * Returns the attname of the Var, or NULL if the Var has no attname (because - * it is a whole-row Var). + * it is a whole-row Var or a subplan output reference). */ static char * get_variable(Var *var, int levelsup, bool istoplevel, deparse_context *context) @@ -4020,6 +5159,7 @@ get_variable(Var *var, int levelsup, bool istoplevel, deparse_context *context) AttrNumber attnum; int netlevelsup; deparse_namespace *dpns; + deparse_columns *colinfo; char *refname; char *attname; @@ -4034,12 +5174,14 @@ get_variable(Var *var, int levelsup, bool istoplevel, deparse_context *context) /* * Try to find the relevant RTE in this rtable. In a plan tree, it's * likely that varno is OUTER_VAR or INNER_VAR, in which case we must dig - * down into the subplans, or INDEX_VAR, which is resolved similarly. + * down into the subplans, or INDEX_VAR, which is resolved similarly. Also + * find the aliases previously assigned for this RTE. */ if (var->varno >= 1 && var->varno <= list_length(dpns->rtable)) { rte = rt_fetch(var->varno, dpns->rtable); refname = (char *) list_nth(dpns->rtable_names, var->varno - 1); + colinfo = deparse_columns_fetch(var->varno, dpns); attnum = var->varattno; } else if (var->varno == OUTER_VAR && dpns->outer_tlist) @@ -4162,11 +5304,11 @@ get_variable(Var *var, int levelsup, bool istoplevel, deparse_context *context) /* * If it's an unnamed join, look at the expansion of the alias variable. * If it's a simple reference to one of the input vars, then recursively - * print the name of that var instead. (This allows correct decompiling - * of cases where there are identically named columns on both sides of the - * join.) When it's not a simple reference, we have to just print the - * unqualified variable name (this can only happen with columns that were - * merged by USING or NATURAL clauses). + * print the name of that var instead. When it's not a simple reference, + * we have to just print the unqualified join column name. (This can only + * happen with columns that were merged by USING or NATURAL clauses in a + * FULL JOIN; we took pains previously to make the unqualified column name + * unique in such cases.) * * This wouldn't work in decompiling plan trees, because we don't store * joinaliasvars lists after planning; but a plan tree should never @@ -4198,8 +5340,18 @@ get_variable(Var *var, int levelsup, bool istoplevel, deparse_context *context) if (attnum == InvalidAttrNumber) attname = NULL; + else if (attnum > 0) + { + /* Get column name to use from the colinfo struct */ + Assert(attnum <= colinfo->num_cols); + attname = colinfo->colnames[attnum - 1]; + Assert(attname != NULL); + } else + { + /* System column - name is fixed, get it from the catalog */ attname = get_rte_attribute_name(rte, attnum); + } if (refname && (context->varprefix || attname == NULL)) { @@ -4429,10 +5581,8 @@ get_name_for_var_field(Var *var, int fieldno, deparse_namespace mydpns; const char *result; - memset(&mydpns, 0, sizeof(mydpns)); - mydpns.rtable = rte->subquery->rtable; - mydpns.ctes = rte->subquery->cteList; - set_rtable_names(&mydpns, context->namespaces, NULL); + set_deparse_for_query(&mydpns, rte->subquery, + context->namespaces); context->namespaces = lcons(&mydpns, context->namespaces); @@ -4547,10 +5697,8 @@ get_name_for_var_field(Var *var, int fieldno, deparse_namespace mydpns; const char *result; - memset(&mydpns, 0, sizeof(mydpns)); - mydpns.rtable = ctequery->rtable; - mydpns.ctes = ctequery->cteList; - set_rtable_names(&mydpns, context->namespaces, NULL); + set_deparse_for_query(&mydpns, ctequery, + context->namespaces); new_nslist = list_copy_tail(context->namespaces, ctelevelsup); @@ -6751,12 +7899,14 @@ static void get_from_clause_item(Node *jtnode, Query *query, deparse_context *context) { StringInfo buf = context->buf; + deparse_namespace *dpns = (deparse_namespace *) linitial(context->namespaces); if (IsA(jtnode, RangeTblRef)) { int varno = ((RangeTblRef *) jtnode)->rtindex; RangeTblEntry *rte = rt_fetch(varno, query->rtable); char *refname = get_rtable_name(varno, context); + deparse_columns *colinfo = deparse_columns_fetch(varno, dpns); bool printalias; if (rte->lateral) @@ -6803,6 +7953,11 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context) /* Always print alias if user provided one */ printalias = true; } + else if (colinfo->printaliases) + { + /* Always print alias if we need to print column aliases */ + printalias = true; + } else if (rte->rtekind == RTE_RELATION) { /* @@ -6816,9 +7971,10 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context) else if (rte->rtekind == RTE_FUNCTION) { /* - * For a function RTE, always print alias. This covers possible + * For a function RTE, always print alias. This covers possible * renaming of the function and/or instability of the * FigureColname rules for things that aren't simple functions. + * Also note we'd need to force it anyway for the RECORD case. */ printalias = true; } @@ -6836,42 +7992,25 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context) appendStringInfo(buf, " %s", quote_identifier(refname)); /* Print the column definitions or aliases, if needed */ - if (rte->rtekind == RTE_FUNCTION) + if (rte->rtekind == RTE_FUNCTION && rte->funccoltypes != NIL) { - if (rte->funccoltypes != NIL) - { - /* Function returning RECORD, reconstruct the columndefs */ - if (!printalias) - appendStringInfo(buf, " AS "); - get_from_clause_coldeflist(rte->eref->colnames, - rte->funccoltypes, - rte->funccoltypmods, - rte->funccolcollations, - context); - } - else - { - /* - * 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). - */ - get_from_clause_alias(rte->eref, rte, context); - } + /* Function returning RECORD, reconstruct the columndefs */ + get_from_clause_coldeflist(colinfo, + rte->funccoltypes, + rte->funccoltypmods, + rte->funccolcollations, + context); } else { - /* - * For non-function RTEs, just report whatever the user originally - * gave as column aliases. - */ - get_from_clause_alias(rte->alias, rte, context); + /* Else print column aliases as needed */ + get_column_alias_list(colinfo, context); } } else if (IsA(jtnode, JoinExpr)) { JoinExpr *j = (JoinExpr *) jtnode; + deparse_columns *colinfo = deparse_columns_fetch(j->rtindex, dpns); bool need_paren_on_right; need_paren_on_right = PRETTY_PAREN(context) && @@ -6883,70 +8022,36 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context) get_from_clause_item(j->larg, query, context); - if (j->isNatural) - { - if (!PRETTY_INDENT(context)) - appendStringInfoChar(buf, ' '); - switch (j->jointype) - { - case JOIN_INNER: - appendContextKeyword(context, "NATURAL JOIN ", - -PRETTYINDENT_JOIN, - PRETTYINDENT_JOIN, 0); - break; - case JOIN_LEFT: - appendContextKeyword(context, "NATURAL LEFT JOIN ", - -PRETTYINDENT_JOIN, - PRETTYINDENT_JOIN, 0); - break; - case JOIN_FULL: - appendContextKeyword(context, "NATURAL FULL JOIN ", - -PRETTYINDENT_JOIN, - PRETTYINDENT_JOIN, 0); - break; - case JOIN_RIGHT: - appendContextKeyword(context, "NATURAL RIGHT JOIN ", - -PRETTYINDENT_JOIN, - PRETTYINDENT_JOIN, 0); - break; - default: - elog(ERROR, "unrecognized join type: %d", - (int) j->jointype); - } - } - else + switch (j->jointype) { - switch (j->jointype) - { - case JOIN_INNER: - if (j->quals) - appendContextKeyword(context, " JOIN ", - -PRETTYINDENT_JOIN, - PRETTYINDENT_JOIN, 2); - else - appendContextKeyword(context, " CROSS JOIN ", - -PRETTYINDENT_JOIN, - PRETTYINDENT_JOIN, 1); - break; - case JOIN_LEFT: - appendContextKeyword(context, " LEFT JOIN ", + case JOIN_INNER: + if (j->quals) + appendContextKeyword(context, " JOIN ", -PRETTYINDENT_JOIN, PRETTYINDENT_JOIN, 2); - break; - case JOIN_FULL: - appendContextKeyword(context, " FULL JOIN ", - -PRETTYINDENT_JOIN, - PRETTYINDENT_JOIN, 2); - break; - case JOIN_RIGHT: - appendContextKeyword(context, " RIGHT JOIN ", + else + appendContextKeyword(context, " CROSS JOIN ", -PRETTYINDENT_JOIN, - PRETTYINDENT_JOIN, 2); - break; - default: - elog(ERROR, "unrecognized join type: %d", - (int) j->jointype); - } + PRETTYINDENT_JOIN, 1); + break; + case JOIN_LEFT: + appendContextKeyword(context, " LEFT JOIN ", + -PRETTYINDENT_JOIN, + PRETTYINDENT_JOIN, 2); + break; + case JOIN_FULL: + appendContextKeyword(context, " FULL JOIN ", + -PRETTYINDENT_JOIN, + PRETTYINDENT_JOIN, 2); + break; + case JOIN_RIGHT: + appendContextKeyword(context, " RIGHT JOIN ", + -PRETTYINDENT_JOIN, + PRETTYINDENT_JOIN, 2); + break; + default: + elog(ERROR, "unrecognized join type: %d", + (int) j->jointype); } if (need_paren_on_right) @@ -6957,32 +8062,35 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context) context->indentLevel -= PRETTYINDENT_JOIN_ON; - if (!j->isNatural) + if (j->usingClause) { - if (j->usingClause) - { - ListCell *col; + ListCell *lc; + bool first = true; - appendStringInfo(buf, " USING ("); - foreach(col, j->usingClause) - { - if (col != list_head(j->usingClause)) - appendStringInfo(buf, ", "); - appendStringInfoString(buf, - quote_identifier(strVal(lfirst(col)))); - } - appendStringInfoChar(buf, ')'); - } - else if (j->quals) + appendStringInfo(buf, " USING ("); + /* Use the assigned names, not what's in usingClause */ + foreach(lc, colinfo->usingNames) { - appendStringInfo(buf, " ON "); - if (!PRETTY_PAREN(context)) - appendStringInfoChar(buf, '('); - get_rule_expr(j->quals, context, false); - if (!PRETTY_PAREN(context)) - appendStringInfoChar(buf, ')'); + char *colname = (char *) lfirst(lc); + + if (first) + first = false; + else + appendStringInfo(buf, ", "); + appendStringInfoString(buf, quote_identifier(colname)); } + appendStringInfoChar(buf, ')'); + } + else if (j->quals) + { + appendStringInfo(buf, " ON "); + if (!PRETTY_PAREN(context)) + appendStringInfoChar(buf, '('); + get_rule_expr(j->quals, context, false); + if (!PRETTY_PAREN(context)) + appendStringInfoChar(buf, ')'); } + if (!PRETTY_PAREN(context) || j->alias != NULL) appendStringInfoChar(buf, ')'); @@ -6991,9 +8099,7 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context) { appendStringInfo(buf, " %s", quote_identifier(j->alias->aliasname)); - get_from_clause_alias(j->alias, - rt_fetch(j->rtindex, query->rtable), - context); + get_column_alias_list(colinfo, context); } } else @@ -7002,28 +8108,25 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context) } /* - * get_from_clause_alias - reproduce column alias list + * get_column_alias_list - print column alias list for an RTE * - * This is tricky because we must ignore dropped columns. + * Caller must already have printed the relation's alias name. */ static void -get_from_clause_alias(Alias *alias, RangeTblEntry *rte, - deparse_context *context) +get_column_alias_list(deparse_columns *colinfo, deparse_context *context) { StringInfo buf = context->buf; - ListCell *col; - AttrNumber attnum; + int i; bool first = true; - if (alias == NULL || alias->colnames == NIL) - return; /* definitely nothing to do */ + /* Don't print aliases if not needed */ + if (!colinfo->printaliases) + return; - attnum = 0; - foreach(col, alias->colnames) + for (i = 0; i < colinfo->num_new_cols; i++) { - attnum++; - if (get_rte_attribute_is_dropped(rte, attnum)) - continue; + char *colname = colinfo->new_colnames[i]; + if (first) { appendStringInfoChar(buf, '('); @@ -7031,8 +8134,7 @@ get_from_clause_alias(Alias *alias, RangeTblEntry *rte, } else appendStringInfo(buf, ", "); - appendStringInfoString(buf, - quote_identifier(strVal(lfirst(col)))); + appendStringInfoString(buf, quote_identifier(colname)); } if (!first) appendStringInfoChar(buf, ')'); @@ -7045,7 +8147,7 @@ get_from_clause_alias(Alias *alias, RangeTblEntry *rte, * responsible for ensuring that an alias or AS is present before it. */ static void -get_from_clause_coldeflist(List *names, +get_from_clause_coldeflist(deparse_columns *colinfo, List *types, List *typmods, List *collations, deparse_context *context) { @@ -7053,27 +8155,19 @@ get_from_clause_coldeflist(List *names, ListCell *l1; ListCell *l2; ListCell *l3; - ListCell *l4; - int i = 0; + int i; appendStringInfoChar(buf, '('); - l2 = list_head(types); - l3 = list_head(typmods); - l4 = list_head(collations); - foreach(l1, names) - { - char *attname = strVal(lfirst(l1)); - Oid atttypid; - int32 atttypmod; - Oid attcollation; - - atttypid = lfirst_oid(l2); - l2 = lnext(l2); - atttypmod = lfirst_int(l3); - l3 = lnext(l3); - attcollation = lfirst_oid(l4); - l4 = lnext(l4); + i = 0; + forthree(l1, types, l2, typmods, l3, collations) + { + char *attname = colinfo->colnames[i]; + Oid atttypid = lfirst_oid(l1); + int32 atttypmod = lfirst_int(l2); + Oid attcollation = lfirst_oid(l3); + + Assert(attname); /* shouldn't be any dropped columns here */ if (i > 0) appendStringInfo(buf, ", "); diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out index d37c88234a..5e235bb98f 100644 --- a/src/test/regress/expected/create_view.out +++ b/src/test/regress/expected/create_view.out @@ -288,7 +288,7 @@ SELECT relname, relkind, reloptions FROM pg_class mysecview4 | v | {security_barrier=false} (4 rows) --- Test view decompilation in the face of renaming conflicts +-- Test view decompilation in the face of relation renaming conflicts CREATE TABLE tt1 (f1 int, f2 int, f3 text); CREATE TABLE tx1 (x1 int, x2 int, x3 text); CREATE TABLE temp_view_test.tt1 (y1 int, f2 int, f3 text); @@ -648,57 +648,379 @@ View definition: FROM temp_view_test.tx1 tx1_1 WHERE tx1.y1 = tx1_1.f1)); +-- 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); +create table tt4 (ay int, b int, q int); +create view v1 as select * from tt2 natural join tt3; +create view v1a as select * from (tt2 natural join tt3) j; +create view v2 as select * from tt2 join tt3 using (b,c) join tt4 using (b); +create view v2a as select * from (tt2 join tt3 using (b,c) join tt4 using (b)) j; +create view v3 as select * from tt2 join tt3 using (b,c) full join tt4 using (b); +select pg_get_viewdef('v1', true); + pg_get_viewdef +------------------------------------- + SELECT tt2.b, tt3.c, tt2.a, tt3.ax+ + FROM tt2 + + JOIN tt3 USING (b, c); +(1 row) + +select pg_get_viewdef('v1a', true); + pg_get_viewdef +------------------------------ + SELECT j.b, j.c, j.a, j.ax + + FROM (tt2 + + JOIN tt3 USING (b, c)) j; +(1 row) + +select pg_get_viewdef('v2', true); + pg_get_viewdef +---------------------------------------------------- + SELECT tt2.b, tt3.c, tt2.a, tt3.ax, tt4.ay, tt4.q+ + FROM tt2 + + JOIN tt3 USING (b, c) + + JOIN tt4 USING (b); +(1 row) + +select pg_get_viewdef('v2a', true); + pg_get_viewdef +---------------------------------------- + SELECT j.b, j.c, j.a, j.ax, j.ay, j.q+ + FROM (tt2 + + JOIN tt3 USING (b, c) + + JOIN tt4 USING (b)) j; +(1 row) + +select pg_get_viewdef('v3', true); + pg_get_viewdef +------------------------------------------------ + SELECT b, tt3.c, tt2.a, tt3.ax, tt4.ay, tt4.q+ + FROM tt2 + + JOIN tt3 USING (b, c) + + FULL JOIN tt4 USING (b); +(1 row) + +alter table tt2 add column d int; +alter table tt2 add column e int; +select pg_get_viewdef('v1', true); + pg_get_viewdef +------------------------------------- + SELECT tt2.b, tt3.c, tt2.a, tt3.ax+ + FROM tt2 + + JOIN tt3 USING (b, c); +(1 row) + +select pg_get_viewdef('v1a', true); + pg_get_viewdef +------------------------------ + SELECT j.b, j.c, j.a, j.ax + + FROM (tt2 + + JOIN tt3 USING (b, c)) j; +(1 row) + +select pg_get_viewdef('v2', true); + pg_get_viewdef +---------------------------------------------------- + SELECT tt2.b, tt3.c, tt2.a, tt3.ax, tt4.ay, tt4.q+ + FROM tt2 + + JOIN tt3 USING (b, c) + + JOIN tt4 USING (b); +(1 row) + +select pg_get_viewdef('v2a', true); + pg_get_viewdef +---------------------------------------- + SELECT j.b, j.c, j.a, j.ax, j.ay, j.q+ + FROM (tt2 + + JOIN tt3 USING (b, c) + + JOIN tt4 USING (b)) j; +(1 row) + +select pg_get_viewdef('v3', true); + pg_get_viewdef +------------------------------------------------ + SELECT b, tt3.c, tt2.a, tt3.ax, tt4.ay, tt4.q+ + FROM tt2 + + JOIN tt3 USING (b, c) + + FULL JOIN tt4 USING (b); +(1 row) + +alter table tt3 rename c to d; +select pg_get_viewdef('v1', true); + pg_get_viewdef +----------------------------------------- + SELECT tt2.b, tt3.c, tt2.a, tt3.ax + + FROM tt2 + + JOIN tt3 tt3(ax, b, c) USING (b, c); +(1 row) + +select pg_get_viewdef('v1a', true); + pg_get_viewdef +-------------------------------------------- + SELECT j.b, j.c, j.a, j.ax + + FROM (tt2 + + JOIN tt3 tt3(ax, b, c) USING (b, c)) j; +(1 row) + +select pg_get_viewdef('v2', true); + pg_get_viewdef +---------------------------------------------------- + SELECT tt2.b, tt3.c, tt2.a, tt3.ax, tt4.ay, tt4.q+ + FROM tt2 + + JOIN tt3 tt3(ax, b, c) USING (b, c) + + JOIN tt4 USING (b); +(1 row) + +select pg_get_viewdef('v2a', true); + pg_get_viewdef +---------------------------------------- + SELECT j.b, j.c, j.a, j.ax, j.ay, j.q+ + FROM (tt2 + + JOIN tt3 tt3(ax, b, c) USING (b, c)+ + JOIN tt4 USING (b)) j; +(1 row) + +select pg_get_viewdef('v3', true); + pg_get_viewdef +------------------------------------------------ + SELECT b, tt3.c, tt2.a, tt3.ax, tt4.ay, tt4.q+ + FROM tt2 + + JOIN tt3 tt3(ax, b, c) USING (b, c) + + FULL JOIN tt4 USING (b); +(1 row) + +alter table tt3 add column c int; +alter table tt3 add column e int; +select pg_get_viewdef('v1', true); + pg_get_viewdef +------------------------------------------------- + SELECT tt2.b, tt3.c, tt2.a, tt3.ax + + FROM tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c); +(1 row) + +select pg_get_viewdef('v1a', true); + pg_get_viewdef +--------------------------------------------------------------------------------- + SELECT j.b, j.c, j.a, j.ax + + FROM (tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, d, e, ax, c_1, e_1); +(1 row) + +select pg_get_viewdef('v2', true); + pg_get_viewdef +---------------------------------------------------- + SELECT tt2.b, tt3.c, tt2.a, tt3.ax, tt4.ay, tt4.q+ + FROM tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c) + + JOIN tt4 USING (b); +(1 row) + +select pg_get_viewdef('v2a', true); + pg_get_viewdef +--------------------------------------------------------------- + SELECT j.b, j.c, j.a, j.ax, j.ay, j.q + + FROM (tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c) + + JOIN tt4 USING (b)) j(b, c, a, d, e, ax, c_1, e_1, ay, q); +(1 row) + +select pg_get_viewdef('v3', true); + pg_get_viewdef +------------------------------------------------ + SELECT b, tt3.c, tt2.a, tt3.ax, tt4.ay, tt4.q+ + FROM tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+ + FULL JOIN tt4 USING (b); +(1 row) + +alter table tt2 drop column d; +select pg_get_viewdef('v1', true); + pg_get_viewdef +------------------------------------------------- + SELECT tt2.b, tt3.c, tt2.a, tt3.ax + + FROM tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c); +(1 row) + +select pg_get_viewdef('v1a', true); + pg_get_viewdef +------------------------------------------------------------------------------ + SELECT j.b, j.c, j.a, j.ax + + FROM (tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, e, ax, c_1, e_1); +(1 row) + +select pg_get_viewdef('v2', true); + pg_get_viewdef +---------------------------------------------------- + SELECT tt2.b, tt3.c, tt2.a, tt3.ax, tt4.ay, tt4.q+ + FROM tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c) + + JOIN tt4 USING (b); +(1 row) + +select pg_get_viewdef('v2a', true); + pg_get_viewdef +------------------------------------------------------------ + SELECT j.b, j.c, j.a, j.ax, j.ay, j.q + + FROM (tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c) + + JOIN tt4 USING (b)) j(b, c, a, e, ax, c_1, e_1, ay, q); +(1 row) + +select pg_get_viewdef('v3', true); + pg_get_viewdef +------------------------------------------------ + SELECT b, tt3.c, tt2.a, tt3.ax, tt4.ay, tt4.q+ + FROM tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+ + FULL JOIN tt4 USING (b); +(1 row) + +create table tt5 (a int, b int); +create table tt6 (c int, d int); +create view vv1 as select * from (tt5 cross join tt6) j(aa,bb,cc,dd); +select pg_get_viewdef('vv1', true); + pg_get_viewdef +-------------------------------------- + SELECT j.aa, j.bb, j.cc, j.dd + + FROM (tt5 + + CROSS JOIN tt6) j(aa, bb, cc, dd); +(1 row) + +alter table tt5 add column c int; +select pg_get_viewdef('vv1', true); + pg_get_viewdef +----------------------------------------- + SELECT j.aa, j.bb, j.cc, j.dd + + FROM (tt5 + + CROSS JOIN tt6) j(aa, bb, c, cc, dd); +(1 row) + +alter table tt5 add column cc int; +select pg_get_viewdef('vv1', true); + pg_get_viewdef +----------------------------------------------- + SELECT j.aa, j.bb, j.cc, j.dd + + FROM (tt5 + + CROSS JOIN tt6) j(aa, bb, c, cc_1, cc, dd); +(1 row) + +alter table tt5 drop column c; +select pg_get_viewdef('vv1', true); + pg_get_viewdef +-------------------------------------------- + SELECT j.aa, j.bb, j.cc, j.dd + + FROM (tt5 + + CROSS JOIN tt6) j(aa, bb, cc_1, cc, dd); +(1 row) + +-- Unnamed FULL JOIN USING is lots of fun too +create table tt7 (x int, xx int, y int); +alter table tt7 drop column xx; +create table tt8 (x int, z int); +create view vv2 as +select * from (values(1,2,3,4,5)) v(a,b,c,d,e) +union all +select * from tt7 full join tt8 using (x), tt8 tt8x; +select pg_get_viewdef('vv2', true); + pg_get_viewdef +---------------------------------------------------------------------------- + SELECT v.a, v.b, v.c, v.d, v.e + + FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e) + + UNION ALL + + SELECT x AS a, tt7.y AS b, tt8.z AS c, tt8x.x_1 AS d, tt8x.z AS e+ + FROM tt7 + + FULL JOIN tt8 USING (x), tt8 tt8x(x_1, z); +(1 row) + +create view vv3 as +select * from (values(1,2,3,4,5,6)) v(a,b,c,x,e,f) +union all +select * from + tt7 full join tt8 using (x), + tt7 tt7x full join tt8 tt8x using (x); +select pg_get_viewdef('vv3', true); + pg_get_viewdef +------------------------------------------------------------------------- + SELECT v.a, v.b, v.c, v.x, v.e, v.f + + FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f) + + UNION ALL + + SELECT x AS a, tt7.y AS b, tt8.z AS c, x_1 AS x, tt7x.y AS e, + + tt8x.z AS f + + FROM tt7 + + FULL JOIN tt8 USING (x), + + tt7 tt7x(x_1, y) + + FULL JOIN tt8 tt8x(x_1, z) USING (x_1); +(1 row) + +create view vv4 as +select * from (values(1,2,3,4,5,6,7)) v(a,b,c,x,e,f,g) +union all +select * from + tt7 full join tt8 using (x), + tt7 tt7x full join tt8 tt8x using (x) full join tt8 tt8y using (x); +select pg_get_viewdef('vv4', true); + pg_get_viewdef +------------------------------------------------------------------------- + SELECT v.a, v.b, v.c, v.x, v.e, v.f, v.g + + FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g) + + UNION ALL + + SELECT x AS a, tt7.y AS b, tt8.z AS c, x_1 AS x, tt7x.y AS e, + + tt8x.z AS f, tt8y.z AS g + + FROM tt7 + + FULL JOIN tt8 USING (x), + + tt7 tt7x(x_1, y) + + FULL JOIN tt8 tt8x(x_1, z) USING (x_1) + + FULL JOIN tt8 tt8y(x_1, z) USING (x_1); +(1 row) + +alter table tt7 add column zz int; +alter table tt7 add column z int; +alter table tt7 drop column zz; +alter table tt8 add column z2 int; +select pg_get_viewdef('vv2', true); + pg_get_viewdef +---------------------------------------------------------------------------- + SELECT v.a, v.b, v.c, v.d, v.e + + FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e) + + UNION ALL + + SELECT x AS a, tt7.y AS b, tt8.z AS c, tt8x.x_1 AS d, tt8x.z AS e+ + FROM tt7 + + FULL JOIN tt8 USING (x), tt8 tt8x(x_1, z, z2); +(1 row) + +select pg_get_viewdef('vv3', true); + pg_get_viewdef +------------------------------------------------------------------------- + SELECT v.a, v.b, v.c, v.x, v.e, v.f + + FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f) + + UNION ALL + + SELECT x AS a, tt7.y AS b, tt8.z AS c, x_1 AS x, tt7x.y AS e, + + tt8x.z AS f + + FROM tt7 + + FULL JOIN tt8 USING (x), + + tt7 tt7x(x_1, y, z) + + FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1); +(1 row) + +select pg_get_viewdef('vv4', true); + pg_get_viewdef +------------------------------------------------------------------------- + SELECT v.a, v.b, v.c, v.x, v.e, v.f, v.g + + FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g) + + UNION ALL + + SELECT x AS a, tt7.y AS b, tt8.z AS c, x_1 AS x, tt7x.y AS e, + + tt8x.z AS f, tt8y.z AS g + + FROM tt7 + + FULL JOIN tt8 USING (x), + + tt7 tt7x(x_1, y, z) + + FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1) + + FULL JOIN tt8 tt8y(x_1, z, z2) USING (x_1); +(1 row) + +-- clean up all the random objects we made above +set client_min_messages = warning; DROP SCHEMA temp_view_test CASCADE; -NOTICE: drop cascades to 27 other objects -DETAIL: drop cascades to table temp_view_test.base_table -drop cascades to view v7_temp -drop cascades to view v10_temp -drop cascades to view v11_temp -drop cascades to view v12_temp -drop cascades to view v2_temp -drop cascades to view v4_temp -drop cascades to view v6_temp -drop cascades to view v8_temp -drop cascades to view v9_temp -drop cascades to table temp_view_test.base_table2 -drop cascades to view v5_temp -drop cascades to view temp_view_test.v1 -drop cascades to view temp_view_test.v2 -drop cascades to view temp_view_test.v3 -drop cascades to view temp_view_test.v4 -drop cascades to view temp_view_test.v5 -drop cascades to view temp_view_test.v6 -drop cascades to view temp_view_test.v7 -drop cascades to view temp_view_test.v8 -drop cascades to sequence temp_view_test.seq1 -drop cascades to view temp_view_test.v9 -drop cascades to table temp_view_test.tx1 -drop cascades to view aliased_view_1 -drop cascades to view aliased_view_2 -drop cascades to view aliased_view_3 -drop cascades to view aliased_view_4 DROP SCHEMA testviewschm2 CASCADE; -NOTICE: drop cascades to 22 other objects -DETAIL: drop cascades to table t1 -drop cascades to view temporal1 -drop cascades to view temporal2 -drop cascades to view temporal3 -drop cascades to view temporal4 -drop cascades to table t2 -drop cascades to view nontemp1 -drop cascades to view nontemp2 -drop cascades to view nontemp3 -drop cascades to view nontemp4 -drop cascades to table tbl1 -drop cascades to table tbl2 -drop cascades to table tbl3 -drop cascades to table tbl4 -drop cascades to view mytempview -drop cascades to view pubview -drop cascades to view mysecview1 -drop cascades to view mysecview2 -drop cascades to view mysecview3 -drop cascades to view mysecview4 -drop cascades to table tt1 -drop cascades to table tx1 -SET search_path to public; diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql index 07145911e6..3d85d9cfdc 100644 --- a/src/test/regress/sql/create_view.sql +++ b/src/test/regress/sql/create_view.sql @@ -224,7 +224,7 @@ SELECT relname, relkind, reloptions FROM pg_class 'mysecview3'::regclass, 'mysecview4'::regclass) ORDER BY relname; --- Test view decompilation in the face of renaming conflicts +-- Test view decompilation in the face of relation renaming conflicts CREATE TABLE tt1 (f1 int, f2 int, f3 text); CREATE TABLE tx1 (x1 int, x2 int, x3 text); @@ -286,7 +286,110 @@ ALTER TABLE tmp1 RENAME TO tx1; \d+ aliased_view_3 \d+ aliased_view_4 +-- 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); +create table tt4 (ay int, b int, q int); + +create view v1 as select * from tt2 natural join tt3; +create view v1a as select * from (tt2 natural join tt3) j; +create view v2 as select * from tt2 join tt3 using (b,c) join tt4 using (b); +create view v2a as select * from (tt2 join tt3 using (b,c) join tt4 using (b)) j; +create view v3 as select * from tt2 join tt3 using (b,c) full join tt4 using (b); + +select pg_get_viewdef('v1', true); +select pg_get_viewdef('v1a', true); +select pg_get_viewdef('v2', true); +select pg_get_viewdef('v2a', true); +select pg_get_viewdef('v3', true); + +alter table tt2 add column d int; +alter table tt2 add column e int; + +select pg_get_viewdef('v1', true); +select pg_get_viewdef('v1a', true); +select pg_get_viewdef('v2', true); +select pg_get_viewdef('v2a', true); +select pg_get_viewdef('v3', true); + +alter table tt3 rename c to d; + +select pg_get_viewdef('v1', true); +select pg_get_viewdef('v1a', true); +select pg_get_viewdef('v2', true); +select pg_get_viewdef('v2a', true); +select pg_get_viewdef('v3', true); + +alter table tt3 add column c int; +alter table tt3 add column e int; + +select pg_get_viewdef('v1', true); +select pg_get_viewdef('v1a', true); +select pg_get_viewdef('v2', true); +select pg_get_viewdef('v2a', true); +select pg_get_viewdef('v3', true); + +alter table tt2 drop column d; + +select pg_get_viewdef('v1', true); +select pg_get_viewdef('v1a', true); +select pg_get_viewdef('v2', true); +select pg_get_viewdef('v2a', true); +select pg_get_viewdef('v3', true); + +create table tt5 (a int, b int); +create table tt6 (c int, d int); +create view vv1 as select * from (tt5 cross join tt6) j(aa,bb,cc,dd); +select pg_get_viewdef('vv1', true); +alter table tt5 add column c int; +select pg_get_viewdef('vv1', true); +alter table tt5 add column cc int; +select pg_get_viewdef('vv1', true); +alter table tt5 drop column c; +select pg_get_viewdef('vv1', true); + +-- Unnamed FULL JOIN USING is lots of fun too + +create table tt7 (x int, xx int, y int); +alter table tt7 drop column xx; +create table tt8 (x int, z int); + +create view vv2 as +select * from (values(1,2,3,4,5)) v(a,b,c,d,e) +union all +select * from tt7 full join tt8 using (x), tt8 tt8x; + +select pg_get_viewdef('vv2', true); + +create view vv3 as +select * from (values(1,2,3,4,5,6)) v(a,b,c,x,e,f) +union all +select * from + tt7 full join tt8 using (x), + tt7 tt7x full join tt8 tt8x using (x); + +select pg_get_viewdef('vv3', true); + +create view vv4 as +select * from (values(1,2,3,4,5,6,7)) v(a,b,c,x,e,f,g) +union all +select * from + tt7 full join tt8 using (x), + tt7 tt7x full join tt8 tt8x using (x) full join tt8 tt8y using (x); + +select pg_get_viewdef('vv4', true); + +alter table tt7 add column zz int; +alter table tt7 add column z int; +alter table tt7 drop column zz; +alter table tt8 add column z2 int; + +select pg_get_viewdef('vv2', true); +select pg_get_viewdef('vv3', true); +select pg_get_viewdef('vv4', true); + +-- clean up all the random objects we made above +set client_min_messages = warning; DROP SCHEMA temp_view_test CASCADE; DROP SCHEMA testviewschm2 CASCADE; - -SET search_path to public; -- 2.40.0