From 398f70ec070fe60151584eaa448f04708aa77892 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 14 Feb 2012 17:34:19 -0500 Subject: [PATCH] Preserve column names in the execution-time tupledesc for a RowExpr. The hstore and json datatypes both have record-conversion functions that pay attention to column names in the composite values they're handed. We used to not worry about inserting correct field names into tuple descriptors generated at runtime, but given these examples it seems useful to do so. Observe the nicer-looking results in the regression tests whose results changed. catversion bump because there is a subtle change in requirements for stored rule parsetrees: RowExprs from ROW() constructs now have to include field names. Andrew Dunstan and Tom Lane --- contrib/hstore/expected/hstore.out | 6 +- src/backend/executor/execQual.c | 3 +- src/backend/executor/execTuples.c | 19 ++--- src/backend/executor/nodeValuesscan.c | 6 +- src/backend/optimizer/path/allpaths.c | 9 ++- src/backend/optimizer/path/equivclass.c | 3 +- src/backend/optimizer/plan/planner.c | 3 +- src/backend/optimizer/prep/prepunion.c | 98 +++++++++++++++---------- src/backend/optimizer/util/var.c | 13 +++- src/backend/parser/gram.y | 1 + src/backend/parser/parse_expr.c | 14 +++- src/include/catalog/catversion.h | 2 +- src/include/executor/executor.h | 2 +- src/include/nodes/primnodes.h | 14 ++-- src/include/optimizer/prep.h | 3 +- src/test/regress/expected/json.out | 62 ++++++++-------- 16 files changed, 158 insertions(+), 100 deletions(-) diff --git a/contrib/hstore/expected/hstore.out b/contrib/hstore/expected/hstore.out index 388893a479..813b9c0478 100644 --- a/contrib/hstore/expected/hstore.out +++ b/contrib/hstore/expected/hstore.out @@ -907,9 +907,9 @@ select pg_column_size(hstore(ARRAY['a','b','asd'], ARRAY['g','h','i'])) -- records select hstore(v) from (values (1, 'foo', 1.2, 3::float8)) v(a,b,c,d); - hstore ------------------------------------------------- - "f1"=>"1", "f2"=>"foo", "f3"=>"1.2", "f4"=>"3" + hstore +-------------------------------------------- + "a"=>"1", "b"=>"foo", "c"=>"1.2", "d"=>"3" (1 row) create domain hstestdom1 as integer not null default 0; diff --git a/src/backend/executor/execQual.c b/src/backend/executor/execQual.c index 4a6baeb17e..a1193a8dc3 100644 --- a/src/backend/executor/execQual.c +++ b/src/backend/executor/execQual.c @@ -4627,7 +4627,8 @@ ExecInitExpr(Expr *node, PlanState *parent) if (rowexpr->row_typeid == RECORDOID) { /* generic record, use runtime type assignment */ - rstate->tupdesc = ExecTypeFromExprList(rowexpr->args); + rstate->tupdesc = ExecTypeFromExprList(rowexpr->args, + rowexpr->colnames); BlessTupleDesc(rstate->tupdesc); /* we won't need to redo this at runtime */ } diff --git a/src/backend/executor/execTuples.c b/src/backend/executor/execTuples.c index 3a9471e462..e755e7c4f0 100644 --- a/src/backend/executor/execTuples.c +++ b/src/backend/executor/execTuples.c @@ -954,27 +954,28 @@ ExecTypeFromTLInternal(List *targetList, bool hasoid, bool skipjunk) /* * ExecTypeFromExprList - build a tuple descriptor from a list of Exprs * - * Here we must make up an arbitrary set of field names. + * Caller must also supply a list of field names (String nodes). */ TupleDesc -ExecTypeFromExprList(List *exprList) +ExecTypeFromExprList(List *exprList, List *namesList) { TupleDesc typeInfo; - ListCell *l; + ListCell *le; + ListCell *ln; int cur_resno = 1; - char fldname[NAMEDATALEN]; + + Assert(list_length(exprList) == list_length(namesList)); typeInfo = CreateTemplateTupleDesc(list_length(exprList), false); - foreach(l, exprList) + forboth(le, exprList, ln, namesList) { - Node *e = lfirst(l); - - sprintf(fldname, "f%d", cur_resno); + Node *e = lfirst(le); + char *n = strVal(lfirst(ln)); TupleDescInitEntry(typeInfo, cur_resno, - fldname, + n, exprType(e), exprTypmod(e), 0); diff --git a/src/backend/executor/nodeValuesscan.c b/src/backend/executor/nodeValuesscan.c index fc17677d0a..a6c1b70cca 100644 --- a/src/backend/executor/nodeValuesscan.c +++ b/src/backend/executor/nodeValuesscan.c @@ -25,6 +25,7 @@ #include "executor/executor.h" #include "executor/nodeValuesscan.h" +#include "parser/parsetree.h" static TupleTableSlot *ValuesNext(ValuesScanState *node); @@ -188,6 +189,8 @@ ValuesScanState * ExecInitValuesScan(ValuesScan *node, EState *estate, int eflags) { ValuesScanState *scanstate; + RangeTblEntry *rte = rt_fetch(node->scan.scanrelid, + estate->es_range_table); TupleDesc tupdesc; ListCell *vtl; int i; @@ -239,7 +242,8 @@ ExecInitValuesScan(ValuesScan *node, EState *estate, int eflags) /* * get info about values list */ - tupdesc = ExecTypeFromExprList((List *) linitial(node->values_lists)); + tupdesc = ExecTypeFromExprList((List *) linitial(node->values_lists), + rte->eref->colnames); ExecAssignScanType(&scanstate->ss, tupdesc); diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index e99e4cc176..8f034176e7 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -492,7 +492,8 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel, * reconstitute the RestrictInfo layer. */ childquals = get_all_actual_clauses(rel->baserestrictinfo); - childquals = (List *) adjust_appendrel_attrs((Node *) childquals, + childquals = (List *) adjust_appendrel_attrs(root, + (Node *) childquals, appinfo); childqual = eval_const_expressions(root, (Node *) make_ands_explicit(childquals)); @@ -532,10 +533,12 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel, * while constructing attr_widths estimates below, though. */ childrel->joininfo = (List *) - adjust_appendrel_attrs((Node *) rel->joininfo, + adjust_appendrel_attrs(root, + (Node *) rel->joininfo, appinfo); childrel->reltargetlist = (List *) - adjust_appendrel_attrs((Node *) rel->reltargetlist, + adjust_appendrel_attrs(root, + (Node *) rel->reltargetlist, appinfo); /* diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c index 2f22efabb5..9228f82920 100644 --- a/src/backend/optimizer/path/equivclass.c +++ b/src/backend/optimizer/path/equivclass.c @@ -1810,7 +1810,8 @@ add_child_rel_equivalences(PlannerInfo *root, Expr *child_expr; child_expr = (Expr *) - adjust_appendrel_attrs((Node *) cur_em->em_expr, + adjust_appendrel_attrs(root, + (Node *) cur_em->em_expr, appinfo); (void) add_eq_member(cur_ec, child_expr, child_rel->relids, true, cur_em->em_datatype); diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 2e8ea5afad..8bbe97713b 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -772,7 +772,8 @@ inheritance_planner(PlannerInfo *root) * then fool around with subquery RTEs. */ subroot.parse = (Query *) - adjust_appendrel_attrs((Node *) parse, + adjust_appendrel_attrs(root, + (Node *) parse, appinfo); /* diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c index cff5a86531..e361fb8ce9 100644 --- a/src/backend/optimizer/prep/prepunion.c +++ b/src/backend/optimizer/prep/prepunion.c @@ -49,6 +49,12 @@ #include "utils/selfuncs.h" +typedef struct +{ + PlannerInfo *root; + AppendRelInfo *appinfo; +} adjust_appendrel_attrs_context; + static Plan *recurse_set_operations(Node *setOp, PlannerInfo *root, double tuple_fraction, List *colTypes, List *colCollations, @@ -99,7 +105,7 @@ static void make_inh_translation_list(Relation oldrelation, static Bitmapset *translate_col_privs(const Bitmapset *parent_privs, List *translated_vars); static Node *adjust_appendrel_attrs_mutator(Node *node, - AppendRelInfo *context); + adjust_appendrel_attrs_context *context); static Relids adjust_relid_set(Relids relids, Index oldrelid, Index newrelid); static List *adjust_inherited_tlist(List *tlist, AppendRelInfo *context); @@ -1569,9 +1575,13 @@ translate_col_privs(const Bitmapset *parent_privs, * maybe we should try to fold the two routines together. */ Node * -adjust_appendrel_attrs(Node *node, AppendRelInfo *appinfo) +adjust_appendrel_attrs(PlannerInfo *root, Node *node, AppendRelInfo *appinfo) { Node *result; + adjust_appendrel_attrs_context context; + + context.root = root; + context.appinfo = appinfo; /* * Must be prepared to start with a Query or a bare expression tree. @@ -1582,7 +1592,7 @@ adjust_appendrel_attrs(Node *node, AppendRelInfo *appinfo) newnode = query_tree_mutator((Query *) node, adjust_appendrel_attrs_mutator, - (void *) appinfo, + (void *) &context, QTW_IGNORE_RC_SUBQUERIES); if (newnode->resultRelation == appinfo->parent_relid) { @@ -1596,14 +1606,17 @@ adjust_appendrel_attrs(Node *node, AppendRelInfo *appinfo) result = (Node *) newnode; } else - result = adjust_appendrel_attrs_mutator(node, appinfo); + result = adjust_appendrel_attrs_mutator(node, &context); return result; } static Node * -adjust_appendrel_attrs_mutator(Node *node, AppendRelInfo *context) +adjust_appendrel_attrs_mutator(Node *node, + adjust_appendrel_attrs_context *context) { + AppendRelInfo *appinfo = context->appinfo; + if (node == NULL) return NULL; if (IsA(node, Var)) @@ -1611,22 +1624,22 @@ adjust_appendrel_attrs_mutator(Node *node, AppendRelInfo *context) Var *var = (Var *) copyObject(node); if (var->varlevelsup == 0 && - var->varno == context->parent_relid) + var->varno == appinfo->parent_relid) { - var->varno = context->child_relid; - var->varnoold = context->child_relid; + var->varno = appinfo->child_relid; + var->varnoold = appinfo->child_relid; if (var->varattno > 0) { Node *newnode; - if (var->varattno > list_length(context->translated_vars)) + if (var->varattno > list_length(appinfo->translated_vars)) elog(ERROR, "attribute %d of relation \"%s\" does not exist", - var->varattno, get_rel_name(context->parent_reloid)); - newnode = copyObject(list_nth(context->translated_vars, + var->varattno, get_rel_name(appinfo->parent_reloid)); + newnode = copyObject(list_nth(appinfo->translated_vars, var->varattno - 1)); if (newnode == NULL) elog(ERROR, "attribute %d of relation \"%s\" does not exist", - var->varattno, get_rel_name(context->parent_reloid)); + var->varattno, get_rel_name(appinfo->parent_reloid)); return newnode; } else if (var->varattno == 0) @@ -1637,19 +1650,19 @@ adjust_appendrel_attrs_mutator(Node *node, AppendRelInfo *context) * step to convert the tuple layout to the parent's rowtype. * Otherwise we have to generate a RowExpr. */ - if (OidIsValid(context->child_reltype)) + if (OidIsValid(appinfo->child_reltype)) { - Assert(var->vartype == context->parent_reltype); - if (context->parent_reltype != context->child_reltype) + Assert(var->vartype == appinfo->parent_reltype); + if (appinfo->parent_reltype != appinfo->child_reltype) { ConvertRowtypeExpr *r = makeNode(ConvertRowtypeExpr); r->arg = (Expr *) var; - r->resulttype = context->parent_reltype; + r->resulttype = appinfo->parent_reltype; r->convertformat = COERCE_IMPLICIT_CAST; r->location = -1; /* Make sure the Var node has the right type ID, too */ - var->vartype = context->child_reltype; + var->vartype = appinfo->child_reltype; return (Node *) r; } } @@ -1657,16 +1670,27 @@ adjust_appendrel_attrs_mutator(Node *node, AppendRelInfo *context) { /* * Build a RowExpr containing the translated variables. + * + * In practice var->vartype will always be RECORDOID here, + * so we need to come up with some suitable column names. + * We use the parent RTE's column names. + * + * Note: we can't get here for inheritance cases, so there + * is no need to worry that translated_vars might contain + * some dummy NULLs. */ RowExpr *rowexpr; List *fields; + RangeTblEntry *rte; - fields = (List *) copyObject(context->translated_vars); + rte = rt_fetch(appinfo->parent_relid, + context->root->parse->rtable); + fields = (List *) copyObject(appinfo->translated_vars); rowexpr = makeNode(RowExpr); rowexpr->args = fields; rowexpr->row_typeid = var->vartype; rowexpr->row_format = COERCE_IMPLICIT_CAST; - rowexpr->colnames = NIL; + rowexpr->colnames = copyObject(rte->eref->colnames); rowexpr->location = -1; return (Node *) rowexpr; @@ -1680,16 +1704,16 @@ adjust_appendrel_attrs_mutator(Node *node, AppendRelInfo *context) { CurrentOfExpr *cexpr = (CurrentOfExpr *) copyObject(node); - if (cexpr->cvarno == context->parent_relid) - cexpr->cvarno = context->child_relid; + if (cexpr->cvarno == appinfo->parent_relid) + cexpr->cvarno = appinfo->child_relid; return (Node *) cexpr; } if (IsA(node, RangeTblRef)) { RangeTblRef *rtr = (RangeTblRef *) copyObject(node); - if (rtr->rtindex == context->parent_relid) - rtr->rtindex = context->child_relid; + if (rtr->rtindex == appinfo->parent_relid) + rtr->rtindex = appinfo->child_relid; return (Node *) rtr; } if (IsA(node, JoinExpr)) @@ -1701,8 +1725,8 @@ adjust_appendrel_attrs_mutator(Node *node, AppendRelInfo *context) adjust_appendrel_attrs_mutator, (void *) context); /* now fix JoinExpr's rtindex (probably never happens) */ - if (j->rtindex == context->parent_relid) - j->rtindex = context->child_relid; + if (j->rtindex == appinfo->parent_relid) + j->rtindex = appinfo->child_relid; return (Node *) j; } if (IsA(node, PlaceHolderVar)) @@ -1716,8 +1740,8 @@ adjust_appendrel_attrs_mutator(Node *node, AppendRelInfo *context) /* now fix PlaceHolderVar's relid sets */ if (phv->phlevelsup == 0) phv->phrels = adjust_relid_set(phv->phrels, - context->parent_relid, - context->child_relid); + appinfo->parent_relid, + appinfo->child_relid); return (Node *) phv; } /* Shouldn't need to handle planner auxiliary nodes here */ @@ -1749,20 +1773,20 @@ adjust_appendrel_attrs_mutator(Node *node, AppendRelInfo *context) /* adjust relid sets too */ newinfo->clause_relids = adjust_relid_set(oldinfo->clause_relids, - context->parent_relid, - context->child_relid); + appinfo->parent_relid, + appinfo->child_relid); newinfo->required_relids = adjust_relid_set(oldinfo->required_relids, - context->parent_relid, - context->child_relid); + appinfo->parent_relid, + appinfo->child_relid); newinfo->nullable_relids = adjust_relid_set(oldinfo->nullable_relids, - context->parent_relid, - context->child_relid); + appinfo->parent_relid, + appinfo->child_relid); newinfo->left_relids = adjust_relid_set(oldinfo->left_relids, - context->parent_relid, - context->child_relid); + appinfo->parent_relid, + appinfo->child_relid); newinfo->right_relids = adjust_relid_set(oldinfo->right_relids, - context->parent_relid, - context->child_relid); + appinfo->parent_relid, + appinfo->child_relid); /* * Reset cached derivative fields, since these might need to have diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c index 475299dadd..2bffb0a651 100644 --- a/src/backend/optimizer/util/var.c +++ b/src/backend/optimizer/util/var.c @@ -783,13 +783,16 @@ flatten_join_alias_vars_mutator(Node *node, /* Must expand whole-row reference */ RowExpr *rowexpr; List *fields = NIL; + List *colnames = NIL; AttrNumber attnum; - ListCell *l; + ListCell *lv; + ListCell *ln; attnum = 0; - foreach(l, rte->joinaliasvars) + Assert(list_length(rte->joinaliasvars) == list_length(rte->eref->colnames)); + forboth(lv, rte->joinaliasvars, ln, rte->eref->colnames) { - newvar = (Node *) lfirst(l); + newvar = (Node *) lfirst(lv); attnum++; /* Ignore dropped columns */ if (IsA(newvar, Const)) @@ -809,12 +812,14 @@ flatten_join_alias_vars_mutator(Node *node, /* (also takes care of setting inserted_sublink if needed) */ newvar = flatten_join_alias_vars_mutator(newvar, context); fields = lappend(fields, newvar); + /* We need the names of non-dropped columns, too */ + colnames = lappend(colnames, copyObject((Node *) lfirst(ln))); } rowexpr = makeNode(RowExpr); rowexpr->args = fields; rowexpr->row_typeid = var->vartype; rowexpr->row_format = COERCE_IMPLICIT_CAST; - rowexpr->colnames = NIL; + rowexpr->colnames = colnames; rowexpr->location = var->location; return (Node *) rowexpr; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index d79576bcaa..db63ff2371 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -10555,6 +10555,7 @@ c_expr: columnref { $$ = $1; } RowExpr *r = makeNode(RowExpr); r->args = $1; r->row_typeid = InvalidOid; /* not analyzed yet */ + r->colnames = NIL; /* to be filled in during analysis */ r->location = @1; $$ = (Node *)r; } diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 698f206f16..d22d8a12ba 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -1692,6 +1692,9 @@ static Node * transformRowExpr(ParseState *pstate, RowExpr *r) { RowExpr *newr = makeNode(RowExpr); + char fname[16]; + int fnum; + ListCell *lc; /* Transform the field expressions */ newr->args = transformExpressionList(pstate, r->args); @@ -1699,7 +1702,16 @@ transformRowExpr(ParseState *pstate, RowExpr *r) /* Barring later casting, we consider the type RECORD */ newr->row_typeid = RECORDOID; newr->row_format = COERCE_IMPLICIT_CAST; - newr->colnames = NIL; /* ROW() has anonymous columns */ + + /* ROW() has anonymous columns, so invent some field names */ + newr->colnames = NIL; + fnum = 1; + foreach(lc, newr->args) + { + snprintf(fname, sizeof(fname), "f%d", fnum++); + newr->colnames = lappend(newr->colnames, makeString(pstrdup(fname))); + } + newr->location = r->location; return (Node *) newr; diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index d7aabb9e4e..7a54a74757 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201202131 +#define CATALOG_VERSION_NO 201202141 #endif diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h index 9a74541d14..7f27669571 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -256,7 +256,7 @@ extern TupleTableSlot *ExecInitNullTupleSlot(EState *estate, TupleDesc tupType); extern TupleDesc ExecTypeFromTL(List *targetList, bool hasoid); extern TupleDesc ExecCleanTypeFromTL(List *targetList, bool hasoid); -extern TupleDesc ExecTypeFromExprList(List *exprList); +extern TupleDesc ExecTypeFromExprList(List *exprList, List *namesList); extern void UpdateChangedParamSet(PlanState *node, Bitmapset *newchg); typedef struct TupOutputState diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 261e7a08dd..50831eebf8 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -846,11 +846,15 @@ typedef struct ArrayExpr * than vice versa.) It is important not to assume that length(args) is * the same as the number of columns logically present in the rowtype. * - * colnames is NIL in a RowExpr built from an ordinary ROW() expression. - * It is provided in cases where we expand a whole-row Var into a RowExpr, - * to retain the column alias names of the RTE that the Var referenced - * (which would otherwise be very difficult to extract from the parsetree). - * Like the args list, it is one-for-one with physical fields of the rowtype. + * colnames provides field names in cases where the names can't easily be + * obtained otherwise. Names *must* be provided if row_typeid is RECORDOID. + * If row_typeid identifies a known composite type, colnames can be NIL to + * indicate the type's cataloged field names apply. Note that colnames can + * be non-NIL even for a composite type, and typically is when the RowExpr + * was created by expanding a whole-row Var. This is so that we can retain + * the column alias names of the RTE that the Var referenced (which would + * otherwise be very difficult to extract from the parsetree). Like the + * args list, colnames is one-for-one with physical fields of the rowtype. */ typedef struct RowExpr { diff --git a/src/include/optimizer/prep.h b/src/include/optimizer/prep.h index 2ea3ed1e11..fb03acc2b4 100644 --- a/src/include/optimizer/prep.h +++ b/src/include/optimizer/prep.h @@ -52,6 +52,7 @@ extern Plan *plan_set_operations(PlannerInfo *root, double tuple_fraction, extern void expand_inherited_tables(PlannerInfo *root); -extern Node *adjust_appendrel_attrs(Node *node, AppendRelInfo *appinfo); +extern Node *adjust_appendrel_attrs(PlannerInfo *root, Node *node, + AppendRelInfo *appinfo); #endif /* PREP_H */ diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out index c4ebdae7e4..2b57351113 100644 --- a/src/test/regress/expected/json.out +++ b/src/test/regress/expected/json.out @@ -265,17 +265,17 @@ SELECT array_to_json(array(select 1 as a)); (1 row) SELECT array_to_json(array_agg(q),false) from (select x as b, x * 2 as c from generate_series(1,3) x) q; - array_to_json ---------------------------------------------------- - [{"f1":1,"f2":2},{"f1":2,"f2":4},{"f1":3,"f2":6}] + array_to_json +--------------------------------------------- + [{"b":1,"c":2},{"b":2,"c":4},{"b":3,"c":6}] (1 row) SELECT array_to_json(array_agg(q),true) from (select x as b, x * 2 as c from generate_series(1,3) x) q; - array_to_json -------------------- - [{"f1":1,"f2":2},+ - {"f1":2,"f2":4},+ - {"f1":3,"f2":6}] + array_to_json +----------------- + [{"b":1,"c":2},+ + {"b":2,"c":4},+ + {"b":3,"c":6}] (1 row) SELECT array_to_json(array_agg(q),false) @@ -284,9 +284,9 @@ SELECT array_to_json(array_agg(q),false) ROW(y.*,ARRAY[4,5,6])] AS z FROM generate_series(1,2) x, generate_series(4,5) y) q; - array_to_json -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - [{"f1":"a1","f2":4,"f3":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]},{"f1":"a1","f2":5,"f3":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]},{"f1":"a2","f2":4,"f3":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]},{"f1":"a2","f2":5,"f3":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}] + array_to_json +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + [{"b":"a1","c":4,"z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]},{"b":"a1","c":5,"z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]},{"b":"a2","c":4,"z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]},{"b":"a2","c":5,"z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}] (1 row) SELECT array_to_json(array_agg(x),false) from generate_series(5,10) x; @@ -315,12 +315,12 @@ FROM (SELECT $$a$$ || x AS b, ROW(y.*,ARRAY[4,5,6])] AS z FROM generate_series(1,2) x, generate_series(4,5) y) q; - row_to_json ------------------------------------------------------------------------ - {"f1":"a1","f2":4,"f3":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]} - {"f1":"a1","f2":5,"f3":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]} - {"f1":"a2","f2":4,"f3":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]} - {"f1":"a2","f2":5,"f3":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]} + row_to_json +-------------------------------------------------------------------- + {"b":"a1","c":4,"z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]} + {"b":"a1","c":5,"z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]} + {"b":"a2","c":4,"z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]} + {"b":"a2","c":5,"z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]} (4 rows) SELECT row_to_json(q,true) @@ -330,20 +330,20 @@ FROM (SELECT $$a$$ || x AS b, ROW(y.*,ARRAY[4,5,6])] AS z FROM generate_series(1,2) x, generate_series(4,5) y) q; - row_to_json ------------------------------------------------------- - {"f1":"a1", + - "f2":4, + - "f3":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]} - {"f1":"a1", + - "f2":5, + - "f3":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]} - {"f1":"a2", + - "f2":4, + - "f3":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]} - {"f1":"a2", + - "f2":5, + - "f3":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]} + row_to_json +----------------------------------------------------- + {"b":"a1", + + "c":4, + + "z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]} + {"b":"a1", + + "c":5, + + "z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]} + {"b":"a2", + + "c":4, + + "z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]} + {"b":"a2", + + "c":5, + + "z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]} (4 rows) CREATE TEMP TABLE rows AS -- 2.40.0