pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Subject: [PATCH] Reject MERGE in CTE
Date: Thu, 11 Aug 2022 13:17:58 +0200

---
 src/backend/parser/parse_cte.c      | 7 +++++++
 src/test/regress/expected/merge.out | 8 ++++++++
 src/test/regress/sql/merge.sql      | 5 +++++
 3 files changed, 20 insertions(+)

inline patch 1: src/backend/parser/parse_cte.c, src/test/regress/expected/merge.out, src/test/regress/sql/merge.sql (+20/-0 lines) download inline-1.patch
diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c
index efb4af706e..8fc8658608 100644
--- a/src/backend/parser/parse_cte.c
+++ b/src/backend/parser/parse_cte.c
@@ -126,6 +126,13 @@ transformWithClause(ParseState *pstate, WithClause *withClause)
 		CommonTableExpr *cte = (CommonTableExpr *) lfirst(lc);
 		ListCell   *rest;
 
+		/* MERGE is allowed by parser, but unimplemented. Reject for now */
+		if (IsA(cte->ctequery, MergeStmt))
+			ereport(ERROR,
+					errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					errmsg("MERGE not supported in WITH query"),
+					parser_errposition(pstate, cte->location));
+
 		for_each_cell(rest, withClause->ctes, lnext(withClause->ctes, lc))
 		{
 			CommonTableExpr *cte2 = (CommonTableExpr *) lfirst(rest);
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
index af670e28e7..21609eba63 100644
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -123,6 +123,14 @@ ON tid = tid
 WHEN MATCHED THEN DO NOTHING;
 ERROR:  name "target" specified more than once
 DETAIL:  The name is used both as MERGE target table and data source.
+-- used in a CTE
+WITH foo AS (
+  MERGE INTO target USING source ON (true)
+WHEN MATCHED THEN DELETE
+) SELECT * FROM foo;
+ERROR:  MERGE not supported in WITH query
+LINE 1: WITH foo AS (
+             ^
 -- unsupported relation types
 -- view
 CREATE VIEW tv AS SELECT * FROM target;
diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql
index afeb212f3c..a3e9d36fe3 100644
--- a/src/test/regress/sql/merge.sql
+++ b/src/test/regress/sql/merge.sql
@@ -88,6 +88,11 @@ MERGE INTO target
 USING target
 ON tid = tid
 WHEN MATCHED THEN DO NOTHING;
+-- used in a CTE
+WITH foo AS (
+  MERGE INTO target USING source ON (true)
+WHEN MATCHED THEN DELETE
+) SELECT * FROM foo;
 
 -- unsupported relation types
 -- view
-- 
2.30.2


--54exrjnke3mqqaau--

Message-ID: <no-message-id-29310@localhost>
Permalink:  ../no-message-id-29310@localhost/
Also on:    postgresql.org/message-id/no-message-id-29310@localhost

copy link · copy postgr.es · copy git am

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: alvherre@alvh.no-ip.org
  Subject: Re: [PATCH] Reject MERGE in CTE
  In-Reply-To: <no-message-id-29310@localhost>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox