From 7103ebb7aae8ab8076b7e85f335ceb8fe799097c Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Mon, 28 Mar 2022 16:45:58 +0200 Subject: Add support for MERGE SQL command MERGE performs actions that modify rows in the target table using a source table or query. MERGE provides a single SQL statement that can conditionally INSERT/UPDATE/DELETE rows -- a task that would otherwise require multiple PL statements. For example, MERGE INTO target AS t USING source AS s ON t.tid = s.sid WHEN MATCHED AND t.balance > s.delta THEN UPDATE SET balance = t.balance - s.delta WHEN MATCHED THEN DELETE WHEN NOT MATCHED AND s.delta > 0 THEN INSERT VALUES (s.sid, s.delta) WHEN NOT MATCHED THEN DO NOTHING; MERGE works with regular tables, partitioned tables and inheritance hierarchies, including column and row security enforcement, as well as support for row and statement triggers and transition tables therein. MERGE is optimized for OLTP and is parameterizable, though also useful for large scale ETL/ELT. MERGE is not intended to be used in preference to existing single SQL commands for INSERT, UPDATE or DELETE since there is some overhead. MERGE can be used from PL/pgSQL. MERGE does not support targetting updatable views or foreign tables, and RETURNING clauses are not allowed either. These limitations are likely fixable with sufficient effort. Rewrite rules are also not supported, but it's not clear that we'd want to support them. Author: Pavan Deolasee Author: Álvaro Herrera Author: Amit Langote Author: Simon Riggs Reviewed-by: Peter Eisentraut Reviewed-by: Andres Freund (earlier versions) Reviewed-by: Peter Geoghegan (earlier versions) Reviewed-by: Robert Haas (earlier versions) Reviewed-by: Japin Li Reviewed-by: Justin Pryzby Reviewed-by: Tomas Vondra Reviewed-by: Zhihong Yu Discussion: https://postgr.es/m/CANP8+jKitBSrB7oTgT9CY2i1ObfOt36z0XMraQc+Xrz8QB0nXA@mail.gmail.com Discussion: https://postgr.es/m/CAH2-WzkJdBuxj9PO=2QaO9-3h3xGbQPZ34kJH=HukRekwM-GZg@mail.gmail.com Discussion: https://postgr.es/m/20201231134736.GA25392@alvherre.pgsql --- src/include/parser/analyze.h | 5 +++++ src/include/parser/kwlist.h | 2 ++ src/include/parser/parse_merge.h | 21 +++++++++++++++++++++ src/include/parser/parse_node.h | 5 +++-- src/include/parser/parse_relation.h | 3 ++- 5 files changed, 33 insertions(+), 3 deletions(-) create mode 100644 src/include/parser/parse_merge.h (limited to 'src/include/parser') diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h index b30cbd26bf8..c69920d1087 100644 --- a/src/include/parser/analyze.h +++ b/src/include/parser/analyze.h @@ -39,6 +39,11 @@ extern Query *parse_sub_analyze(Node *parseTree, ParseState *parentParseState, bool locked_from_parent, bool resolve_unknowns); +extern List *transformInsertRow(ParseState *pstate, List *exprlist, + List *stmtcols, List *icolumns, List *attrnos, + bool strip_indirection); +extern List *transformUpdateTargetList(ParseState *pstate, + List *targetList); extern Query *transformTopLevelStmt(ParseState *pstate, RawStmt *parseTree); extern Query *transformStmt(ParseState *pstate, Node *parseTree); diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index f44440d4a94..10829941e0d 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -259,8 +259,10 @@ PG_KEYWORD("locked", LOCKED, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("logged", LOGGED, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("mapping", MAPPING, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("match", MATCH, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("matched", MATCHED, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("materialized", MATERIALIZED, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("maxvalue", MAXVALUE, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("merge", MERGE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("method", METHOD, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("minute", MINUTE_P, UNRESERVED_KEYWORD, AS_LABEL) PG_KEYWORD("minvalue", MINVALUE, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/include/parser/parse_merge.h b/src/include/parser/parse_merge.h new file mode 100644 index 00000000000..1a1baa1f485 --- /dev/null +++ b/src/include/parser/parse_merge.h @@ -0,0 +1,21 @@ +/*------------------------------------------------------------------------- + * + * parse_merge.h + * handle MERGE statement in parser + * + * + * Portions Copyright (c) 1996-2022, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * src/include/parser/parse_merge.h + * + *------------------------------------------------------------------------- + */ +#ifndef PARSE_MERGE_H +#define PARSE_MERGE_H + +#include "parser/parse_node.h" + +extern Query *transformMergeStmt(ParseState *pstate, MergeStmt *stmt); + +#endif /* PARSE_MERGE_H */ diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index 8c859d0d0e4..cf9c7590258 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -55,6 +55,7 @@ typedef enum ParseExprKind EXPR_KIND_INSERT_TARGET, /* INSERT target list item */ EXPR_KIND_UPDATE_SOURCE, /* UPDATE assignment source item */ EXPR_KIND_UPDATE_TARGET, /* UPDATE assignment target item */ + EXPR_KIND_MERGE_WHEN, /* MERGE WHEN [NOT] MATCHED condition */ EXPR_KIND_GROUP_BY, /* GROUP BY */ EXPR_KIND_ORDER_BY, /* ORDER BY */ EXPR_KIND_DISTINCT_ON, /* DISTINCT ON */ @@ -135,7 +136,7 @@ typedef Node *(*CoerceParamHook) (ParseState *pstate, Param *param, * p_parent_cte: CommonTableExpr that immediately contains the current query, * if any. * - * p_target_relation: target relation, if query is INSERT, UPDATE, or DELETE. + * p_target_relation: target relation, if query is INSERT/UPDATE/DELETE/MERGE * * p_target_nsitem: target relation's ParseNamespaceItem. * @@ -189,7 +190,7 @@ struct ParseState List *p_ctenamespace; /* current namespace for common table exprs */ List *p_future_ctes; /* common table exprs not yet in namespace */ CommonTableExpr *p_parent_cte; /* this query's containing CTE */ - Relation p_target_relation; /* INSERT/UPDATE/DELETE target rel */ + Relation p_target_relation; /* INSERT/UPDATE/DELETE/MERGE target rel */ ParseNamespaceItem *p_target_nsitem; /* target rel's NSItem, or NULL */ bool p_is_insert; /* process assignment like INSERT not UPDATE */ List *p_windowdefs; /* raw representations of window clauses */ diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h index 06dc27995ba..de21c3c6497 100644 --- a/src/include/parser/parse_relation.h +++ b/src/include/parser/parse_relation.h @@ -113,7 +113,8 @@ extern List *expandNSItemVars(ParseNamespaceItem *nsitem, int sublevels_up, int location, List **colnames); extern List *expandNSItemAttrs(ParseState *pstate, ParseNamespaceItem *nsitem, - int sublevels_up, int location); + int sublevels_up, bool require_col_privs, + int location); extern int attnameAttNum(Relation rd, const char *attname, bool sysColOK); extern const NameData *attnumAttName(Relation rd, int attid); extern Oid attnumTypeId(Relation rd, int attid); -- cgit v1.2.3