v1-0001-Forbid-FOR-PORTION-OF-with-WHERE-CURRENT-OF.patch

From d42ef75a39a24fd9667f0a48ef510064f5472880 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Tue, 16 Jun 2026 21:23:24 -0700
Subject: [PATCH v1] Forbid FOR PORTION OF with WHERE CURRENT OF

It is not clear how the implicit condition of FOR PORTION OF should interact
with the use of a cursor. Normally we forbid combining WHERE CURRENT OF with
other WHERE conditions. The SQL standard only includes FOR PORTION OF with
<update statement: searched> and <delete statement: searched>, not <update
statement: positioned> or <delete statement: positioned>, so it is easy for us
to exclude the functionality, at least for now.
---
 doc/src/sgml/ref/delete.sgml                 |  7 ++--
 doc/src/sgml/ref/update.sgml                 |  7 ++--
 src/backend/parser/analyze.c                 | 11 +++++
 src/test/regress/expected/for_portion_of.out | 42 ++++++++++++++++++++
 src/test/regress/sql/for_portion_of.sql      | 28 +++++++++++++
 5 files changed, 87 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index 9066d7ea83d..ffdcd7fc4fa 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -231,10 +231,9 @@ DELETE FROM [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ *
       from this cursor.  The cursor must be a non-grouping
       query on the <command>DELETE</command>'s target table.
       Note that <literal>WHERE CURRENT OF</literal> cannot be
-      specified together with a Boolean condition.  See
-      <xref linkend="sql-declare"/>
-      for more information about using cursors with
-      <literal>WHERE CURRENT OF</literal>.
+      specified together with a Boolean condition or <literal>FOR PORTION
+      OF</literal>.  See <xref linkend="sql-declare"/> for more information
+      about using cursors with <literal>WHERE CURRENT OF</literal>.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index dd57bead90c..21a8fd8b037 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -287,10 +287,9 @@ UPDATE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
       from this cursor.  The cursor must be a non-grouping
       query on the <command>UPDATE</command>'s target table.
       Note that <literal>WHERE CURRENT OF</literal> cannot be
-      specified together with a Boolean condition.  See
-      <xref linkend="sql-declare"/>
-      for more information about using cursors with
-      <literal>WHERE CURRENT OF</literal>.
+      specified together with a Boolean condition or <literal>FOR PORTION
+      OF</literal>.  See <xref linkend="sql-declare"/> for more information
+      about using cursors with <literal>WHERE CURRENT OF</literal>.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 93fa66ae57c..7caf11bfb94 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -81,6 +81,7 @@ static OnConflictExpr *transformOnConflictClause(ParseState *pstate,
 static ForPortionOfExpr *transformForPortionOfClause(ParseState *pstate,
 													 int rtindex,
 													 const ForPortionOfClause *forPortionOf,
+													 const Node *whereClause,
 													 bool isUpdate);
 static int	count_rowexpr_columns(ParseState *pstate, Node *expr);
 static Query *transformSelectStmt(ParseState *pstate, SelectStmt *stmt,
@@ -626,6 +627,7 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
 		qry->forPortionOf = transformForPortionOfClause(pstate,
 														qry->resultRelation,
 														stmt->forPortionOf,
+														stmt->whereClause,
 														false);
 
 	qual = transformWhereClause(pstate, stmt->whereClause,
@@ -1319,6 +1321,7 @@ static ForPortionOfExpr *
 transformForPortionOfClause(ParseState *pstate,
 							int rtindex,
 							const ForPortionOfClause *forPortionOf,
+							const Node *whereClause,
 							bool isUpdate)
 {
 	Relation	targetrel = pstate->p_target_relation;
@@ -1335,6 +1338,13 @@ transformForPortionOfClause(ParseState *pstate,
 	ForPortionOfExpr *result;
 	Var		   *rangeVar;
 
+	/* disallow FOR PORTION OF ... WHERE CURRENT OF */
+	if (whereClause &&
+		IsA(whereClause, CurrentOfExpr))
+		ereport(ERROR,
+				errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				errmsg("WHERE CURRENT OF with FOR PORTION OF is not implemented"));
+
 	/* We don't support FOR PORTION OF FDW queries. */
 	if (targetrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
 		ereport(ERROR,
@@ -2884,6 +2894,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
 		qry->forPortionOf = transformForPortionOfClause(pstate,
 														qry->resultRelation,
 														stmt->forPortionOf,
+														stmt->whereClause,
 														true);
 
 	nsitem = pstate->p_target_nsitem;
diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out
index 43408972117..207e370627e 100644
--- a/src/test/regress/expected/for_portion_of.out
+++ b/src/test/regress/expected/for_portion_of.out
@@ -2446,4 +2446,46 @@ NOTICE:  fpo_before_row1: BEFORE UPDATE ROW:
 NOTICE:    old: [10,100)
 NOTICE:    new: [30,70)
 DROP TABLE fpo_update_of_trigger;
+-- CURSORs
+CREATE TABLE fpo_cursed (
+  id int,
+  valid_at int4range
+);
+INSERT INTO fpo_cursed (id, valid_at) VALUES (1, '[10,100)');
+-- UPDATE FOR PORTION OF is not permitted with a CURSOR:
+BEGIN;
+DECLARE fpo_cur CURSOR FOR SELECT * FROM fpo_cursed;
+FETCH NEXT FROM fpo_cur;
+ id | valid_at 
+----+----------
+  1 | [10,100)
+(1 row)
+
+UPDATE fpo_cursed
+  FOR PORTION OF valid_at FROM 5 TO 6
+  SET id = 2
+  WHERE CURRENT OF fpo_cur;
+ERROR:  WHERE CURRENT OF with FOR PORTION OF is not implemented
+ROLLBACK;
+-- DELETE FOR PORTION OF is not permitted with a CURSOR:
+BEGIN;
+DECLARE fpo_cur CURSOR FOR SELECT * FROM fpo_cursed;
+FETCH NEXT FROM fpo_cur;
+ id | valid_at 
+----+----------
+  1 | [10,100)
+(1 row)
+
+DELETE FROM fpo_cursed
+  FOR PORTION OF valid_at FROM 8 TO 9
+  WHERE CURRENT OF fpo_cur;
+ERROR:  WHERE CURRENT OF with FOR PORTION OF is not implemented
+ROLLBACK;
+SELECT * FROM fpo_cursed;
+ id | valid_at 
+----+----------
+  1 | [10,100)
+(1 row)
+
+DROP TABLE fpo_cursed;
 RESET datestyle;
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql
index 7b08f8cf45e..a3c41abf7b7 100644
--- a/src/test/regress/sql/for_portion_of.sql
+++ b/src/test/regress/sql/for_portion_of.sql
@@ -1591,4 +1591,32 @@ UPDATE fpo_update_of_trigger
   SET id = 2;
 DROP TABLE fpo_update_of_trigger;
 
+-- CURSORs
+CREATE TABLE fpo_cursed (
+  id int,
+  valid_at int4range
+);
+INSERT INTO fpo_cursed (id, valid_at) VALUES (1, '[10,100)');
+
+-- UPDATE FOR PORTION OF is not permitted with a CURSOR:
+BEGIN;
+DECLARE fpo_cur CURSOR FOR SELECT * FROM fpo_cursed;
+FETCH NEXT FROM fpo_cur;
+UPDATE fpo_cursed
+  FOR PORTION OF valid_at FROM 5 TO 6
+  SET id = 2
+  WHERE CURRENT OF fpo_cur;
+ROLLBACK;
+
+-- DELETE FOR PORTION OF is not permitted with a CURSOR:
+BEGIN;
+DECLARE fpo_cur CURSOR FOR SELECT * FROM fpo_cursed;
+FETCH NEXT FROM fpo_cur;
+DELETE FROM fpo_cursed
+  FOR PORTION OF valid_at FROM 8 TO 9
+  WHERE CURRENT OF fpo_cur;
+ROLLBACK;
+SELECT * FROM fpo_cursed;
+DROP TABLE fpo_cursed;
+
 RESET datestyle;
-- 
2.45.0