Hi,
As per discussion on -hackers, a patch which allows updates to use
subselects is attached with this mail.
As per discussion with Tom, I have adopted the following approach:
* Introduce ROWEXPR_SUBLINK type for subqueries that allows multiple column
outputs.
* Populate the targetList with PARAM_SUBLINK entries dependent on the
subselects.
* Modify the targets in-place into PARAM_EXEC entries in the make_subplan
phase.
The above does not require any kluges in the targetList processing code path
at all.
UPDATEs seem to work fine using subselects with this patch. I have modified
the update.sql regression test to include possible variations .
No documentation changes are present in this patch.
Feedback, comments appreciated.
Regards,
Nikhils
--
EnterpriseDB http://www.enterprisedb.com
? GNUmakefile
? config.log
? config.status
? cscope.out
? src/Makefile.global
? src/backend/postgres
? src/backend/catalog/postgres.bki
? src/backend/catalog/postgres.description
? src/backend/catalog/postgres.shdescription
? src/backend/utils/mb/conversion_procs/conversion_create.sql
? src/backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/euc_jis_2004_and_shift_jis_2004/libeuc_jis_2004_and_shift_jis_2004.so.0.0
? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjis.so.0.0
? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5.so.0.0
? src/backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250.so.0.0
? src/backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_ascii.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillic.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cn.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_jis_2004/libutf8_and_euc_jis_2004.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jp.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_kr.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_tw.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbk.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johab.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_shift_jis_2004/libutf8_and_shift_jis_2004.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjis.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhc.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_win/libutf8_and_win.so.0.0
? src/bin/initdb/initdb
? src/bin/ipcclean/ipcclean
? src/bin/pg_config/pg_config
? src/bin/pg_controldata/pg_controldata
? src/bin/pg_ctl/pg_ctl
? src/bin/pg_dump/pg_dump
? src/bin/pg_dump/pg_dumpall
? src/bin/pg_dump/pg_restore
? src/bin/pg_resetxlog/pg_resetxlog
? src/bin/psql/psql
? src/bin/scripts/clusterdb
? src/bin/scripts/createdb
? src/bin/scripts/createlang
? src/bin/scripts/createuser
? src/bin/scripts/dropdb
? src/bin/scripts/droplang
? src/bin/scripts/dropuser
? src/bin/scripts/reindexdb
? src/bin/scripts/vacuumdb
? src/include/pg_config.h
? src/include/stamp-h
? src/interfaces/ecpg/compatlib/libecpg_compat.so.2.3
? src/interfaces/ecpg/ecpglib/libecpg.so.5.3
? src/interfaces/ecpg/include/ecpg_config.h
? src/interfaces/ecpg/pgtypeslib/libpgtypes.so.2.3
? src/interfaces/ecpg/preproc/.pgc.c.swp
? src/interfaces/ecpg/preproc/ecpg
? src/interfaces/libpq/exports.list
? src/interfaces/libpq/libpq.so.5.1
? src/pl/plpgsql/src/libplpgsql.so.1.0
? src/port/pg_config_paths.h
? src/test/regress/libregress.so.0.0
? src/test/regress/pg_regress
? src/test/regress/results
? src/test/regress/testtablespace
? src/test/regress/expected/constraints.out
? src/test/regress/expected/copy.out
? src/test/regress/expected/create_function_1.out
? src/test/regress/expected/create_function_2.out
? src/test/regress/expected/largeobject.out
? src/test/regress/expected/largeobject_1.out
? src/test/regress/expected/misc.out
? src/test/regress/expected/tablespace.out
? src/test/regress/sql/constraints.sql
? src/test/regress/sql/copy.sql
? src/test/regress/sql/create_function_1.sql
? src/test/regress/sql/create_function_2.sql
? src/test/regress/sql/largeobject.sql
? src/test/regress/sql/misc.sql
? src/test/regress/sql/tablespace.sql
? src/timezone/zic
Index: src/backend/executor/nodeSubplan.c
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/backend/executor/nodeSubplan.c,v
retrieving revision 1.87
diff -c -r1.87 nodeSubplan.c
*** src/backend/executor/nodeSubplan.c 27 Feb 2007 01:11:25 -0000 1.87
--- src/backend/executor/nodeSubplan.c 11 Apr 2007 08:04:32 -0000
***************
*** 322,329 ****
continue;
}
! /* cannot allow multiple input tuples for ROWCOMPARE sublink either */
! if (subLinkType == ROWCOMPARE_SUBLINK && found)
ereport(ERROR,
(errcode(ERRCODE_CARDINALITY_VIOLATION),
errmsg("more than one row returned by a subquery used as an expression")));
--- 322,330 ----
continue;
}
! /* cannot allow multiple input tuples for ROWCOMPARE/ROWEXPR sublink either */
! if ((subLinkType == ROWCOMPARE_SUBLINK || subLinkType == ROWEXPR_SUBLINK)
! && found)
ereport(ERROR,
(errcode(ERRCODE_CARDINALITY_VIOLATION),
errmsg("more than one row returned by a subquery used as an expression")));
***************
*** 376,382 ****
}
else
{
! /* must be ROWCOMPARE_SUBLINK */
result = rowresult;
*isNull = rownull;
}
--- 377,383 ----
}
else
{
! /* must be ROWCOMPARE_SUBLINK or ROWEXPR_SUBLINK */
result = rowresult;
*isNull = rownull;
}
***************
*** 391,397 ****
*/
if (subLinkType == EXPR_SUBLINK ||
subLinkType == ARRAY_SUBLINK ||
! subLinkType == ROWCOMPARE_SUBLINK)
{
result = (Datum) 0;
*isNull = true;
--- 392,399 ----
*/
if (subLinkType == EXPR_SUBLINK ||
subLinkType == ARRAY_SUBLINK ||
! subLinkType == ROWCOMPARE_SUBLINK ||
! subLinkType == ROWEXPR_SUBLINK)
{
result = (Datum) 0;
*isNull = true;
***************
*** 937,942 ****
--- 939,945 ----
if (found &&
(subLinkType == EXPR_SUBLINK ||
+ subLinkType == ROWEXPR_SUBLINK ||
subLinkType == ROWCOMPARE_SUBLINK))
ereport(ERROR,
(errcode(ERRCODE_CARDINALITY_VIOLATION),
Index: src/backend/optimizer/plan/planner.c
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/backend/optimizer/plan/planner.c,v
retrieving revision 1.216
diff -c -r1.216 planner.c
*** src/backend/optimizer/plan/planner.c 27 Feb 2007 01:11:25 -0000 1.216
--- src/backend/optimizer/plan/planner.c 11 Apr 2007 08:04:32 -0000
***************
*** 72,77 ****
--- 72,78 ----
List *sub_tlist,
AttrNumber *groupColIdx);
static List *postprocess_setop_tlist(List *new_tlist, List *orig_tlist);
+ static void preprocess_subselects(PlannerInfo *root, Node *expr);
/*****************************************************************************
***************
*** 273,278 ****
--- 274,284 ----
expand_inherited_tables(root);
/*
+ * preprocess subselects if present in the Query
+ */
+ preprocess_subselects(root, (Node *)parse->subSelects);
+
+ /*
* Set hasHavingQual to remember if HAVING clause is present. Needed
* because preprocess_expression will reduce a constant-true condition to
* an empty qual list ... but "HAVING TRUE" is not a semantic no-op.
***************
*** 1752,1754 ****
--- 1758,1776 ----
elog(ERROR, "resjunk output columns are not implemented");
return new_tlist;
}
+
+ /*
+ * preprocess_subselects
+ */
+ static void
+ preprocess_subselects(PlannerInfo *root, Node *expr)
+ {
+ /*
+ * Fall out quickly if expression is empty.
+ */
+ if (expr == NULL)
+ return;
+ SS_process_sublinks(root, expr, false);
+
+ return;
+ }
Index: src/backend/optimizer/plan/subselect.c
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/backend/optimizer/plan/subselect.c,v
retrieving revision 1.122
diff -c -r1.122 subselect.c
*** src/backend/optimizer/plan/subselect.c 27 Feb 2007 01:11:25 -0000 1.122
--- src/backend/optimizer/plan/subselect.c 11 Apr 2007 08:04:32 -0000
***************
*** 68,73 ****
--- 68,79 ----
Bitmapset *outer_params,
Bitmapset *valid_params);
static bool finalize_primnode(Node *node, finalize_primnode_context *context);
+ static Node *convert_rowexpr(PlannerInfo *root,
+ Node *testexpr,
+ int rtindex,
+ List **righthandIds);
+ static bool convert_rowexpr_walker(Node *node,
+ convert_testexpr_context *context);
/*
***************
*** 385,390 ****
--- 391,406 ----
* plan's expression tree; it is not kept in the initplan node.
*/
}
+ else if (splan->parParam == NIL && slink->subLinkType == ROWEXPR_SUBLINK)
+ {
+ /* Adjust the Params */
+ result = convert_rowexpr(root,
+ slink->testexpr,
+ 0,
+ &splan->paramIds);
+ splan->setParam = list_copy(splan->paramIds);
+ isInitPlan = true;
+ }
else
{
List *args;
***************
*** 1376,1378 ****
--- 1392,1459 ----
return prm;
}
+
+ /*
+ * convert_rowexpr:
+ * Use a walker to do an in-situ change to the PARAM entries involved in a
+ * ROWEXPR_SUBLINK subquery
+ */
+ static Node *
+ convert_rowexpr(PlannerInfo *root,
+ Node *testexpr,
+ int rtindex,
+ List **righthandIds)
+ {
+ convert_testexpr_context context;
+
+ context.root = root;
+ context.rtindex = rtindex;
+ context.righthandIds = NIL;
+ convert_rowexpr_walker(testexpr, &context);
+ *righthandIds = context.righthandIds;
+ return testexpr;
+ }
+
+ static bool
+ convert_rowexpr_walker(Node *node,
+ convert_testexpr_context *context)
+ {
+ if (node == NULL)
+ return false;
+
+ if (IsA(node, Param))
+ {
+ Param *param = (Param *) node;
+
+ if (param->paramkind == PARAM_SUBLINK)
+ {
+ PlannerParamItem *pitem;
+
+ /*
+ * We expect to encounter the Params in column-number sequence. We
+ * could handle non-sequential order if necessary, but for now
+ * there's no need. (This is also a useful cross-check that we
+ * aren't finding any unexpected Params.)
+ */
+ if (param->paramid != list_length(context->righthandIds) + 1)
+ elog(ERROR, "unexpected PARAM_SUBLINK ID: %d", param->paramid);
+
+ /* Modify the Param node in-situ representing the subplan's result */
+ param->paramkind = PARAM_EXEC;
+ param->paramid = list_length(context->root->glob->paramlist);
+
+ pitem = makeNode(PlannerParamItem);
+ pitem->item = (Node *) param;
+ pitem->abslevel = context->root->query_level;
+
+ context->root->glob->paramlist =
+ lappend(context->root->glob->paramlist, pitem);
+
+ context->righthandIds = lappend_int(context->righthandIds,
+ param->paramid);
+ }
+ }
+ return expression_tree_walker((Node *)node,
+ convert_rowexpr_walker,
+ (void *) context);
+ }
Index: src/backend/parser/analyze.c
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/backend/parser/analyze.c,v
retrieving revision 1.362
diff -c -r1.362 analyze.c
*** src/backend/parser/analyze.c 13 Mar 2007 00:33:41 -0000 1.362
--- src/backend/parser/analyze.c 11 Apr 2007 08:04:32 -0000
***************
*** 110,116 ****
static Query *transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt);
static Query *transformInsertStmt(ParseState *pstate, InsertStmt *stmt,
List **extras_before, List **extras_after);
! static List *transformInsertRow(ParseState *pstate, List *exprlist,
List *stmtcols, List *icolumns, List *attrnos);
static List *transformReturningList(ParseState *pstate, List *returningList);
static Query *transformSelectStmt(ParseState *pstate, SelectStmt *stmt);
--- 110,116 ----
static Query *transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt);
static Query *transformInsertStmt(ParseState *pstate, InsertStmt *stmt,
List **extras_before, List **extras_after);
! static List *transformRow(ParseState *pstate, List *exprlist,
List *stmtcols, List *icolumns, List *attrnos);
static List *transformReturningList(ParseState *pstate, List *returningList);
static Query *transformSelectStmt(ParseState *pstate, SelectStmt *stmt);
***************
*** 497,503 ****
false, false, ACL_INSERT);
/* Validate stmt->cols list, or build default list if no list given */
! icolumns = checkInsertTargets(pstate, stmt->cols, &attrnos);
Assert(list_length(icolumns) == list_length(attrnos));
/*
--- 497,503 ----
false, false, ACL_INSERT);
/* Validate stmt->cols list, or build default list if no list given */
! icolumns = checkTargets(pstate, stmt->cols, &attrnos);
Assert(list_length(icolumns) == list_length(attrnos));
/*
***************
*** 603,609 ****
}
/* Prepare row for assignment to target table */
! exprList = transformInsertRow(pstate, exprList,
stmt->cols,
icolumns, attrnos);
}
--- 603,609 ----
}
/* Prepare row for assignment to target table */
! exprList = transformRow(pstate, exprList,
stmt->cols,
icolumns, attrnos);
}
***************
*** 643,649 ****
}
/* Prepare row for assignment to target table */
! sublist = transformInsertRow(pstate, sublist,
stmt->cols,
icolumns, attrnos);
--- 643,649 ----
}
/* Prepare row for assignment to target table */
! sublist = transformRow(pstate, sublist,
stmt->cols,
icolumns, attrnos);
***************
*** 710,716 ****
(List *) linitial(valuesLists));
/* Prepare row for assignment to target table */
! exprList = transformInsertRow(pstate, exprList,
stmt->cols,
icolumns, attrnos);
}
--- 710,716 ----
(List *) linitial(valuesLists));
/* Prepare row for assignment to target table */
! exprList = transformRow(pstate, exprList,
stmt->cols,
icolumns, attrnos);
}
***************
*** 771,783 ****
}
/*
! * Prepare an INSERT row for assignment to the target table.
*
* The row might be either a VALUES row, or variables referencing a
* sub-SELECT output.
*/
static List *
! transformInsertRow(ParseState *pstate, List *exprlist,
List *stmtcols, List *icolumns, List *attrnos)
{
List *result;
--- 771,783 ----
}
/*
! * Prepare an INSERT/UPDATE row for assignment to the target table.
*
* The row might be either a VALUES row, or variables referencing a
* sub-SELECT output.
*/
static List *
! transformRow(ParseState *pstate, List *exprlist,
List *stmtcols, List *icolumns, List *attrnos)
{
List *result;
***************
*** 795,806 ****
if (list_length(exprlist) > list_length(icolumns))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("INSERT has more expressions than target columns")));
if (stmtcols != NIL &&
list_length(exprlist) < list_length(icolumns))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("INSERT has more target columns than expressions")));
/*
* Prepare columns for assignment to target table.
--- 795,806 ----
if (list_length(exprlist) > list_length(icolumns))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("INSERT/UPDATE has more expressions than target columns")));
if (stmtcols != NIL &&
list_length(exprlist) < list_length(icolumns))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("INSERT/UPDATE has more target columns than expressions")));
/*
* Prepare columns for assignment to target table.
***************
*** 2784,2790 ****
--- 2784,2802 ----
Node *qual;
ListCell *origTargetList;
ListCell *tl;
+ ListCell *lc, *lcell;
+ List *exprList = NIL;
+ List *icolumns;
+ List *attrnos;
+ List *otherColsList = NIL;
+ List *selectColsList = NIL;
+ List *selectQryList;
+ List *subselectQryList = NIL;
+ ListCell *icols;
+ ListCell *attnos;
+ SubLink *subselect;
+ qry->subSelects = NIL;
qry->commandType = CMD_UPDATE;
pstate->p_is_update = true;
***************
*** 2792,2797 ****
--- 2804,2890 ----
interpretInhOption(stmt->relation->inhOpt),
true,
ACL_UPDATE);
+ /*
+ * check if the targetList contains subselects and if so process each of
+ * them
+ */
+ qry->targetList = NIL;
+ subselectQryList = NIL;
+ foreach(lcell, stmt->targetList)
+ {
+ Query *selectQuery;
+
+ if (!IsA(lfirst(lcell), SubLink))
+ {
+ otherColsList = lappend(otherColsList, lfirst(lcell));
+ continue;
+ }
+
+ subselect = (SubLink *)lfirst(lcell);
+
+ /* Validate subselect->cols list */
+ icolumns = checkTargets(pstate, subselect->cols, &attrnos);
+ Assert(list_length(icolumns) == list_length(attrnos));
+
+ /*
+ * Maintain sub select based target cols in another list
+ */
+ selectColsList = list_concat(selectColsList, subselect->cols);
+
+ /*
+ * Process the SELECT subquery.
+ */
+
+ transformExpr(pstate, (Node *)subselect);
+ selectQuery = (Query *)subselect->subselect;
+
+ Assert(IsA(selectQuery, Query));
+ Assert(selectQuery->commandType == CMD_SELECT);
+ if (selectQuery->into)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("UPDATE ... SELECT cannot specify INTO")));
+
+ /* Prepare row for assignment to target table */
+ exprList = transformRow(pstate, (List *)subselect->testexpr,
+ subselect->cols,
+ icolumns, attrnos);
+ /*
+ * Generate query's target list using the computed list of expressions.
+ */
+ selectQryList = NIL;
+ icols = list_head(icolumns);
+ attnos = list_head(attrnos);
+ foreach(lc, exprList)
+ {
+ Expr *expr = (Expr *) lfirst(lc);
+ ResTarget *col;
+ TargetEntry *tle;
+
+ col = (ResTarget *) lfirst(icols);
+ Assert(IsA(col, ResTarget));
+
+ tle = makeTargetEntry(expr,
+ (AttrNumber) lfirst_int(attnos),
+ col->name,
+ false);
+ selectQryList = lappend(selectQryList, tle);
+
+ icols = lnext(icols);
+ attnos = lnext(attnos);
+ }
+ subselect->testexpr = (Node *)selectQryList;
+ /*
+ * Need to use list_copy otherwise targets of multiple subselects get
+ * chained to one another
+ */
+ subselectQryList = list_concat(subselectQryList, list_copy(selectQryList));
+
+ /*
+ * Add this sublink to the Query for further processing by the planner
+ */
+ qry->subSelects = lappend(qry->subSelects, subselect);
+ }
/*
* the FROM clause is non-standard SQL syntax. We used to be able to do
***************
*** 2799,2806 ****
*/
transformFromClause(pstate, stmt->fromClause);
! qry->targetList = transformTargetList(pstate, stmt->targetList);
!
qual = transformWhereClause(pstate, stmt->whereClause, "WHERE");
qry->returningList = transformReturningList(pstate, stmt->returningList);
--- 2892,2901 ----
*/
transformFromClause(pstate, stmt->fromClause);
!
! if (otherColsList)
! qry->targetList = transformTargetList(pstate, otherColsList);
!
qual = transformWhereClause(pstate, stmt->whereClause, "WHERE");
qry->returningList = transformReturningList(pstate, stmt->returningList);
***************
*** 2829,2836 ****
if (pstate->p_next_resno <= pstate->p_target_relation->rd_rel->relnatts)
pstate->p_next_resno = pstate->p_target_relation->rd_rel->relnatts + 1;
! /* Prepare non-junk columns for assignment to target table */
! origTargetList = list_head(stmt->targetList);
foreach(tl, qry->targetList)
{
--- 2924,2935 ----
if (pstate->p_next_resno <= pstate->p_target_relation->rd_rel->relnatts)
pstate->p_next_resno = pstate->p_target_relation->rd_rel->relnatts + 1;
! /*
! * Prepare non-junk columns for assignment to target table.
! * The subselect based targets have already been processed, process the
! * other remaining entries here
! */
! origTargetList = list_head(otherColsList);
foreach(tl, qry->targetList)
{
***************
*** 2875,2880 ****
--- 2974,2984 ----
if (origTargetList != NULL)
elog(ERROR, "UPDATE target count mismatch --- internal error");
+ /*
+ * subselect based target entries need to be made a part of the query's
+ * targetList here too
+ */
+ qry->targetList = list_concat(qry->targetList, subselectQryList);
return qry;
}
Index: src/backend/parser/gram.y
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.587
diff -c -r2.587 gram.y
*** src/backend/parser/gram.y 8 Apr 2007 00:26:34 -0000 2.587
--- src/backend/parser/gram.y 11 Apr 2007 08:04:32 -0000
***************
*** 5774,5779 ****
--- 5774,5789 ----
$$ = $2;
}
+ | '(' set_target_list ')' '=' select_with_parens
+ {
+ SubLink *n = makeNode(SubLink);
+ n->subLinkType = ROWEXPR_SUBLINK;
+ n->testexpr = NULL;
+ n->operName = NIL;
+ n->subselect = $5;
+ n->cols = $2;
+ $$ = list_make1((Node *)n);
+ }
;
set_target:
Index: src/backend/parser/parse_expr.c
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/backend/parser/parse_expr.c,v
retrieving revision 1.216
diff -c -r1.216 parse_expr.c
*** src/backend/parser/parse_expr.c 2 Apr 2007 03:49:39 -0000 1.216
--- src/backend/parser/parse_expr.c 11 Apr 2007 08:04:32 -0000
***************
*** 1159,1164 ****
--- 1159,1204 ----
sublink->testexpr = NULL;
sublink->operName = NIL;
}
+ else if (sublink->subLinkType == ROWEXPR_SUBLINK)
+ {
+ List *left_list;
+ List *right_list;
+ ListCell *l;
+
+ left_list = sublink->cols;
+ Assert(left_list != NULL);
+ /*
+ * Build a list of PARAM_SUBLINK nodes representing the output columns
+ * of the subquery.
+ */
+ right_list = NIL;
+ foreach(l, qtree->targetList)
+ {
+ TargetEntry *tent = (TargetEntry *) lfirst(l);
+ Param *param;
+
+ if (tent->resjunk)
+ continue;
+
+ param = makeNode(Param);
+ param->paramkind = PARAM_SUBLINK;
+ param->paramid = tent->resno;
+ param->paramtype = exprType((Node *) tent->expr);
+ param->paramtypmod = exprTypmod((Node *) tent->expr);
+
+ right_list = lappend(right_list, param);
+ }
+
+ if (list_length(left_list) < list_length(right_list))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("subquery has too many columns")));
+ if (list_length(left_list) > list_length(right_list))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("subquery has too few columns")));
+ sublink->testexpr = (Node *)right_list;
+ }
else
{
/* ALL, ANY, or ROWCOMPARE: generate row-comparing expression */
Index: src/backend/parser/parse_target.c
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/backend/parser/parse_target.c,v
retrieving revision 1.154
diff -c -r1.154 parse_target.c
*** src/backend/parser/parse_target.c 3 Feb 2007 14:06:54 -0000 1.154
--- src/backend/parser/parse_target.c 11 Apr 2007 08:04:33 -0000
***************
*** 706,725 ****
/*
! * checkInsertTargets -
! * generate a list of INSERT column targets if not supplied, or
* test supplied column names to make sure they are in target table.
* Also return an integer list of the columns' attribute numbers.
*/
List *
! checkInsertTargets(ParseState *pstate, List *cols, List **attrnos)
{
*attrnos = NIL;
if (cols == NIL)
{
/*
! * Generate default column list for INSERT.
*/
Form_pg_attribute *attr = pstate->p_target_relation->rd_att->attrs;
int numcol = pstate->p_target_relation->rd_rel->relnatts;
--- 706,725 ----
/*
! * checkTargets -
! * generate a list of INSERT/UPDATE column targets if not supplied, or
* test supplied column names to make sure they are in target table.
* Also return an integer list of the columns' attribute numbers.
*/
List *
! checkTargets(ParseState *pstate, List *cols, List **attrnos)
{
*attrnos = NIL;
if (cols == NIL)
{
/*
! * Generate default column list for INSERT/UPDATE.
*/
Form_pg_attribute *attr = pstate->p_target_relation->rd_att->attrs;
int numcol = pstate->p_target_relation->rd_rel->relnatts;
***************
*** 744,750 ****
else
{
/*
! * Do initial validation of user-supplied INSERT column list.
*/
Bitmapset *wholecols = NULL;
Bitmapset *partialcols = NULL;
--- 744,750 ----
else
{
/*
! * Do initial validation of user-supplied INSERT/UPDATE column list.
*/
Bitmapset *wholecols = NULL;
Bitmapset *partialcols = NULL;
***************
*** 768,774 ****
/*
* Check for duplicates, but only of whole columns --- we allow
! * INSERT INTO foo (col.subcol1, col.subcol2)
*/
if (col->indirection == NIL)
{
--- 768,775 ----
/*
* Check for duplicates, but only of whole columns --- we allow
! * INSERT INTO foo (col.subcol1, col.subcol2).
! * Above applies for UPDATEs too
*/
if (col->indirection == NIL)
{
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.344
diff -c -r1.344 parsenodes.h
*** src/include/nodes/parsenodes.h 2 Apr 2007 03:49:41 -0000 1.344
--- src/include/nodes/parsenodes.h 11 Apr 2007 08:04:33 -0000
***************
*** 129,134 ****
--- 129,135 ----
Node *setOperations; /* set-operation tree if this is top level of
* a UNION/INTERSECT/EXCEPT query */
+ List *subSelects; /* a list of subselects used in UPDATEs */
} Query;
Index: src/include/nodes/primnodes.h
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/include/nodes/primnodes.h,v
retrieving revision 1.129
diff -c -r1.129 primnodes.h
*** src/include/nodes/primnodes.h 27 Mar 2007 23:21:12 -0000 1.129
--- src/include/nodes/primnodes.h 11 Apr 2007 08:04:33 -0000
***************
*** 377,382 ****
--- 377,383 ----
* ANY_SUBLINK (lefthand) op ANY (SELECT ...)
* ROWCOMPARE_SUBLINK (lefthand) op (SELECT ...)
* EXPR_SUBLINK (SELECT with single targetlist item ...)
+ * ROWEXPR_SUBLINK (SELECT with multiple targetlist items ...)
* ARRAY_SUBLINK ARRAY(SELECT with single targetlist item ...)
* For ALL, ANY, and ROWCOMPARE, the lefthand is a list of expressions of the
* same length as the subselect's targetlist. ROWCOMPARE will *always* have
***************
*** 384,392 ****
* then the parser will create an EXPR_SUBLINK instead (and any operator
* above the subselect will be represented separately). Note that both
* ROWCOMPARE and EXPR require the subselect to deliver only one row.
! * ALL, ANY, and ROWCOMPARE require the combining operators to deliver boolean
! * results. ALL and ANY combine the per-row results using AND and OR
! * semantics respectively.
* ARRAY requires just one target column, and creates an array of the target
* column's type using one or more rows resulting from the subselect.
*
--- 385,394 ----
* then the parser will create an EXPR_SUBLINK instead (and any operator
* above the subselect will be represented separately). Note that both
* ROWCOMPARE and EXPR require the subselect to deliver only one row.
! * For subselects with multiple targets, the parser will create a
! * ROWEXPR sublink. ALL, ANY, and ROWCOMPARE require the combining operators
! * to deliver boolean results. ALL and ANY combine the per-row results using
! * AND and OR semantics respectively.
* ARRAY requires just one target column, and creates an array of the target
* column's type using one or more rows resulting from the subselect.
*
***************
*** 412,417 ****
--- 414,420 ----
ANY_SUBLINK,
ROWCOMPARE_SUBLINK,
EXPR_SUBLINK,
+ ROWEXPR_SUBLINK,
ARRAY_SUBLINK
} SubLinkType;
***************
*** 422,427 ****
--- 425,431 ----
SubLinkType subLinkType; /* see above */
Node *testexpr; /* outer-query test for ALL/ANY/ROWCOMPARE */
List *operName; /* originally specified operator name */
+ List *cols; /* names of the target columns for ROWEXPR_SUBLINK */
Node *subselect; /* subselect as Query* or parsetree */
} SubLink;
Index: src/include/parser/parse_target.h
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/include/parser/parse_target.h,v
retrieving revision 1.42
diff -c -r1.42 parse_target.h
*** src/include/parser/parse_target.h 5 Jan 2007 22:19:57 -0000 1.42
--- src/include/parser/parse_target.h 11 Apr 2007 08:04:33 -0000
***************
*** 32,38 ****
char *colname, int attrno,
List *indirection,
int location);
! extern List *checkInsertTargets(ParseState *pstate, List *cols,
List **attrnos);
extern TupleDesc expandRecordVariable(ParseState *pstate, Var *var,
int levelsup);
--- 32,38 ----
char *colname, int attrno,
List *indirection,
int location);
! extern List *checkTargets(ParseState *pstate, List *cols,
List **attrnos);
extern TupleDesc expandRecordVariable(ParseState *pstate, Var *var,
int levelsup);
Index: src/test/regress/expected/alter_table.out
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/test/regress/expected/alter_table.out,v
retrieving revision 1.101
diff -c -r1.101 alter_table.out
*** src/test/regress/expected/alter_table.out 14 Feb 2007 01:58:58 -0000 1.101
--- src/test/regress/expected/alter_table.out 11 Apr 2007 08:04:33 -0000
***************
*** 765,773 ****
^
-- INSERTs
insert into atacc1 values (10, 11, 12, 13);
! ERROR: INSERT has more expressions than target columns
insert into atacc1 values (default, 11, 12, 13);
! ERROR: INSERT has more expressions than target columns
insert into atacc1 values (11, 12, 13);
insert into atacc1 (a) values (10);
ERROR: column "a" of relation "atacc1" does not exist
--- 765,773 ----
^
-- INSERTs
insert into atacc1 values (10, 11, 12, 13);
! ERROR: INSERT/UPDATE has more expressions than target columns
insert into atacc1 values (default, 11, 12, 13);
! ERROR: INSERT/UPDATE has more expressions than target columns
insert into atacc1 values (11, 12, 13);
insert into atacc1 (a) values (10);
ERROR: column "a" of relation "atacc1" does not exist
Index: src/test/regress/expected/insert.out
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/test/regress/expected/insert.out,v
retrieving revision 1.8
diff -c -r1.8 insert.out
*** src/test/regress/expected/insert.out 3 Aug 2006 14:54:44 -0000 1.8
--- src/test/regress/expected/insert.out 11 Apr 2007 08:04:33 -0000
***************
*** 21,33 ****
-- insert with similar expression / target_list values (all fail)
--
insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT);
! ERROR: INSERT has more target columns than expressions
insert into inserttest (col1, col2, col3) values (1, 2);
! ERROR: INSERT has more target columns than expressions
insert into inserttest (col1) values (1, 2);
! ERROR: INSERT has more expressions than target columns
insert into inserttest (col1) values (DEFAULT, DEFAULT);
! ERROR: INSERT has more expressions than target columns
select * from inserttest;
col1 | col2 | col3
------+------+---------
--- 21,33 ----
-- insert with similar expression / target_list values (all fail)
--
insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT);
! ERROR: INSERT/UPDATE has more target columns than expressions
insert into inserttest (col1, col2, col3) values (1, 2);
! ERROR: INSERT/UPDATE has more target columns than expressions
insert into inserttest (col1) values (1, 2);
! ERROR: INSERT/UPDATE has more expressions than target columns
insert into inserttest (col1) values (DEFAULT, DEFAULT);
! ERROR: INSERT/UPDATE has more expressions than target columns
select * from inserttest;
col1 | col2 | col3
------+------+---------
Index: src/test/regress/expected/update.out
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/test/regress/expected/update.out,v
retrieving revision 1.5
diff -c -r1.5 update.out
*** src/test/regress/expected/update.out 3 Sep 2006 22:37:06 -0000 1.5
--- src/test/regress/expected/update.out 11 Apr 2007 08:04:33 -0000
***************
*** 74,85 ****
-- fail, multi assignment to same column:
UPDATE update_test SET (c,b) = ('car', a+b), b = a + 1 WHERE a = 10;
ERROR: multiple assignments to same column "b"
! -- XXX this should work, but doesn't yet:
! UPDATE update_test SET (a,b) = (select a,b FROM update_test where c = 'foo')
! WHERE a = 10;
! ERROR: syntax error at or near "select"
! LINE 1: UPDATE update_test SET (a,b) = (select a,b FROM update_test ...
! ^
-- if an alias for the target table is specified, don't allow references
-- to the original table name
BEGIN;
--- 74,131 ----
-- fail, multi assignment to same column:
UPDATE update_test SET (c,b) = ('car', a+b), b = a + 1 WHERE a = 10;
ERROR: multiple assignments to same column "b"
! INSERT INTO update_test VALUES (5, 10, 'foo');
! -- update using subselect:
! UPDATE update_test SET (a,b) = (select a+5,b FROM update_test where c = 'foo')
! WHERE a = 5;
! SELECT * FROM update_test;
! a | b | c
! -----+----+-----
! 100 | 20 |
! 11 | 41 | car
! 10 | 10 | foo
! (3 rows)
!
! -- fail update when subselect returns multiple rows:
! UPDATE update_test SET (a,b) = (select a,b FROM update_test);
! ERROR: more than one row returned by a subquery used as an expression
! -- combination of multiple subselects in UPDATEs
! UPDATE update_test SET a= (select a FROM update_test where c = 'foo'),
! b = (select b+100 FROM update_test where c = 'foo') WHERE a = 10;
! SELECT * FROM update_test;
! a | b | c
! -----+-----+-----
! 100 | 20 |
! 11 | 41 | car
! 10 | 110 | foo
! (3 rows)
!
! -- combination of normal SET with subselects
! UPDATE update_test SET a= (select a+5 FROM update_test where c = 'foo'),
! b = 100 WHERE a = 10;
! SELECT * FROM update_test;
! a | b | c
! -----+-----+-----
! 100 | 20 |
! 11 | 41 | car
! 15 | 100 | foo
! (3 rows)
!
! -- fail, multi assignment to same column when subselects are involved
! UPDATE update_test SET (c,b) = ('car', a+b),
! b = (select b from update_test where c = 'foo') WHERE a = 10;
! ERROR: multiple assignments to same column "b"
! -- update using subselect, subquery should return NULLs if there are no matches
! UPDATE update_test SET (a,b) = (select a,b FROM update_test where c = 'bar')
! WHERE a = 15;
! SELECT * FROM update_test;
! a | b | c
! -----+----+-----
! 100 | 20 |
! 11 | 41 | car
! | | foo
! (3 rows)
!
-- if an alias for the target table is specified, don't allow references
-- to the original table name
BEGIN;
Index: src/test/regress/sql/update.sql
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/test/regress/sql/update.sql,v
retrieving revision 1.5
diff -c -r1.5 update.sql
*** src/test/regress/sql/update.sql 3 Sep 2006 22:37:06 -0000 1.5
--- src/test/regress/sql/update.sql 11 Apr 2007 08:04:33 -0000
***************
*** 46,54 ****
-- fail, multi assignment to same column:
UPDATE update_test SET (c,b) = ('car', a+b), b = a + 1 WHERE a = 10;
! -- XXX this should work, but doesn't yet:
! UPDATE update_test SET (a,b) = (select a,b FROM update_test where c = 'foo')
! WHERE a = 10;
-- if an alias for the target table is specified, don't allow references
-- to the original table name
--- 46,78 ----
-- fail, multi assignment to same column:
UPDATE update_test SET (c,b) = ('car', a+b), b = a + 1 WHERE a = 10;
! INSERT INTO update_test VALUES (5, 10, 'foo');
! -- update using subselect:
! UPDATE update_test SET (a,b) = (select a+5,b FROM update_test where c = 'foo')
! WHERE a = 5;
! SELECT * FROM update_test;
!
! -- fail update when subselect returns multiple rows:
! UPDATE update_test SET (a,b) = (select a,b FROM update_test);
!
! -- combination of multiple subselects in UPDATEs
! UPDATE update_test SET a= (select a FROM update_test where c = 'foo'),
! b = (select b+100 FROM update_test where c = 'foo') WHERE a = 10;
! SELECT * FROM update_test;
!
! -- combination of normal SET with subselects
! UPDATE update_test SET a= (select a+5 FROM update_test where c = 'foo'),
! b = 100 WHERE a = 10;
! SELECT * FROM update_test;
!
! -- fail, multi assignment to same column when subselects are involved
! UPDATE update_test SET (c,b) = ('car', a+b),
! b = (select b from update_test where c = 'foo') WHERE a = 10;
!
! -- update using subselect, subquery should return NULLs if there are no matches
! UPDATE update_test SET (a,b) = (select a,b FROM update_test where c = 'bar')
! WHERE a = 15;
! SELECT * FROM update_test;
-- if an alias for the target table is specified, don't allow references
-- to the original table name
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
