postgres.git / summary / log / commit / refs
commit f1940321687644a3ef38dccca8bbeb7c50d4cbdc
Author: Peter Eisentraut <peter@eisentraut.org>
Date: Tue Sep 08 07:35:44 2026 +0000
Resolve untyped parameters in FOR PORTION OF FROM/TO bounds
FOR PORTION OF valid_at FROM $1 TO $2 fails with "could not determine data
type of parameter $1", so using PREPARE requires users to say $1::date
etc. But transformForPortionOfClause() already coerces the bounds, since
it builds a constructor for the targeted range. We just need to store the
coerced expressions in targetFrom and targetTo.
This also improves pg_get_ruledef(), which now renders an untyped NULL
bound as NULL::date instead of NULL::unknown.
Reported-by: Noah Misch <noah@leadboat.com>
Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
Discussion: https://www.postgresql.org/message-id/flat/CA%2BrenyW1uJp1dw%3DiK7MsWfbbZf-dKP_4JLoQ65wj%3Dfry35FjCw%40mail.gmail.com
src/backend/parser/analyze.c | 12 ++-
src/test/regress/expected/for_portion_of.out | 106 ++++++++++++++++++++++++++-
src/test/regress/sql/for_portion_of.sql | 71 ++++++++++++++++++
3 files changed, 185 insertions(+), 4 deletions(-)
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index be8605f4194..be2a7510034 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1465,8 +1465,7 @@ transformForPortionOfClause(ParseState *pstate,
EXPR_KIND_FOR_PORTION);
actual_arg_types[0] = exprType(result->targetFrom);
actual_arg_types[1] = exprType(result->targetTo);
- args = list_make2(copyObject(result->targetFrom),
- copyObject(result->targetTo));
+ args = list_make2(result->targetFrom, result->targetTo);
/*
* Check the bound types separately, for better error message and
@@ -1490,6 +1489,15 @@ transformForPortionOfClause(ParseState *pstate,
parser_errposition(pstate, exprLocation(forPortionOf->target_end))));
make_fn_arguments(pstate, args, actual_arg_types, declared_arg_types);
+
+ /*
+ * Keep the *coerced* bounds. This lets prepared statements use
+ * parameters without explicit casts, and it improves deparsing when
+ * FOR PORTION OF appears in a function or RULE.
+ */
+ result->targetFrom = copyObject((Node *) linitial(args));
+ result->targetTo = copyObject((Node *) lsecond(args));
+
result->targetRange = (Node *) makeFuncExpr(get_range_constructor2(attbasetype),
attbasetype,
args,
diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out
index 64789d1777b..e4090a145c2 100644
--- a/src/test/regress/expected/for_portion_of.out
+++ b/src/test/regress/expected/for_portion_of.out
@@ -652,7 +652,7 @@ END;
RETURNS text
LANGUAGE sql
1 BEGIN ATOMIC
-2 UPDATE for_portion_of_test FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' SET name = 'one^1'::text
+2 UPDATE for_portion_of_test FOR PORTION OF valid_at FROM '2018-01-15'::date TO '2019-01-01'::date SET name = 'one^1'::text
3 RETURNING for_portion_of_test.name;
4 END
CREATE OR REPLACE function fpo_update()
@@ -1029,7 +1029,7 @@ END;
RETURNS text
LANGUAGE sql
1 BEGIN ATOMIC
-2 DELETE FROM for_portion_of_test FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01'
+2 DELETE FROM for_portion_of_test FOR PORTION OF valid_at FROM '2018-01-15'::date TO '2019-01-01'::date
3 RETURNING for_portion_of_test.name;
4 END
CREATE OR REPLACE function fpo_delete()
@@ -2793,4 +2793,106 @@ SELECT * FROM fpo_rls ORDER BY valid_at;
DROP TABLE fpo_rls;
DROP ROLE regress_fpo_rls;
+--
+-- Parameters in the FOR PORTION OF bounds
+--
+CREATE TABLE fpo_param (
+ id int4range,
+ valid_at daterange,
+ name text
+);
+INSERT INTO fpo_param (id, valid_at, name) VALUES
+ ('[1,2)', daterange('2000-01-01', '2010-01-01'), 'one');
+-- A parameter of unspecified type in an ordinary expression gets its type
+-- resolved from context. This is the control case for the FROM/TO bounds
+-- below: it builds exactly the same daterange the FROM/TO form does.
+PREPARE fpo_param_control AS
+ UPDATE fpo_param SET name = 'ctl' WHERE valid_at && daterange($1, $2);
+SELECT parameter_types FROM pg_prepared_statements
+ WHERE name = 'fpo_param_control';
+ parameter_types
+-----------------
+ {date,date}
+(1 row)
+
+-- The (portion) form resolves the parameter type from the range column.
+PREPARE fpo_param_portion AS
+ UPDATE fpo_param FOR PORTION OF valid_at ($1) SET name = 'portion';
+SELECT parameter_types FROM pg_prepared_statements
+ WHERE name = 'fpo_param_portion';
+ parameter_types
+-----------------
+ {daterange}
+(1 row)
+
+-- The FROM/TO form should likewise resolve its bounds to the range's
+-- subtype, so that clients need not spell out the parameter types.
+PREPARE fpo_param_update AS
+ UPDATE fpo_param FOR PORTION OF valid_at FROM $1 TO $2 SET name = 'upd';
+SELECT parameter_types FROM pg_prepared_statements
+ WHERE name = 'fpo_param_update';
+ parameter_types
+-----------------
+ {date,date}
+(1 row)
+
+PREPARE fpo_param_delete AS
+ DELETE FROM fpo_param FOR PORTION OF valid_at FROM $1 TO $2;
+SELECT parameter_types FROM pg_prepared_statements
+ WHERE name = 'fpo_param_delete';
+ parameter_types
+-----------------
+ {date,date}
+(1 row)
+
+-- Only one bound parameterized.
+PREPARE fpo_param_one AS
+ UPDATE fpo_param FOR PORTION OF valid_at FROM $1 TO '2003-01-01'
+ SET name = 'one-bound';
+SELECT parameter_types FROM pg_prepared_statements
+ WHERE name = 'fpo_param_one';
+ parameter_types
+-----------------
+ {date}
+(1 row)
+
+-- A parameter used as both bounds must still resolve to one type.
+PREPARE fpo_param_same AS
+ UPDATE fpo_param FOR PORTION OF valid_at FROM $1 TO $1 SET name = 'same';
+SELECT parameter_types FROM pg_prepared_statements
+ WHERE name = 'fpo_param_same';
+ parameter_types
+-----------------
+ {date}
+(1 row)
+
+EXECUTE fpo_param_update('2002-01-01', '2003-01-01');
+SELECT * FROM fpo_param ORDER BY valid_at;
+ id | valid_at | name
+-------+-------------------------+------
+ [1,2) | [2000-01-01,2002-01-01) | one
+ [1,2) | [2002-01-01,2003-01-01) | upd
+ [1,2) | [2003-01-01,2010-01-01) | one
+(3 rows)
+
+DEALLOCATE fpo_param_control;
+DEALLOCATE fpo_param_portion;
+DEALLOCATE fpo_param_update;
+DEALLOCATE fpo_param_delete;
+DEALLOCATE fpo_param_one;
+DEALLOCATE fpo_param_same;
+-- The bounds we keep for deparsing are the coerced ones, so an untyped NULL
+-- bound renders with the range's subtype instead of "unknown".
+CREATE TABLE fpo_param_src (id int);
+CREATE RULE fpo_param_r AS ON DELETE TO fpo_param_src DO INSTEAD
+ DELETE FROM fpo_param FOR PORTION OF valid_at FROM NULL TO '2001-01-01';
+SELECT definition FROM pg_rules WHERE rulename = 'fpo_param_r';
+ definition
+----------------------------------------------------------------------------------------------------------------------------------------
+ CREATE RULE fpo_param_r AS +
+ ON DELETE TO public.fpo_param_src DO INSTEAD DELETE FROM fpo_param FOR PORTION OF valid_at FROM NULL::date TO '2001-01-01'::date;
+(1 row)
+
+DROP TABLE fpo_param_src;
+DROP TABLE fpo_param;
RESET datestyle;
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql
index b61fe10478e..ede1f28d0ed 100644
--- a/src/test/regress/sql/for_portion_of.sql
+++ b/src/test/regress/sql/for_portion_of.sql
@@ -1849,4 +1849,75 @@ SELECT * FROM fpo_rls ORDER BY valid_at;
DROP TABLE fpo_rls;
DROP ROLE regress_fpo_rls;
+--
+-- Parameters in the FOR PORTION OF bounds
+--
+
+CREATE TABLE fpo_param (
+ id int4range,
+ valid_at daterange,
+ name text
+);
+INSERT INTO fpo_param (id, valid_at, name) VALUES
+ ('[1,2)', daterange('2000-01-01', '2010-01-01'), 'one');
+
+-- A parameter of unspecified type in an ordinary expression gets its type
+-- resolved from context. This is the control case for the FROM/TO bounds
+-- below: it builds exactly the same daterange the FROM/TO form does.
+PREPARE fpo_param_control AS
+ UPDATE fpo_param SET name = 'ctl' WHERE valid_at && daterange($1, $2);
+SELECT parameter_types FROM pg_prepared_statements
+ WHERE name = 'fpo_param_control';
+
+-- The (portion) form resolves the parameter type from the range column.
+PREPARE fpo_param_portion AS
+ UPDATE fpo_param FOR PORTION OF valid_at ($1) SET name = 'portion';
+SELECT parameter_types FROM pg_prepared_statements
+ WHERE name = 'fpo_param_portion';
+
+-- The FROM/TO form should likewise resolve its bounds to the range's
+-- subtype, so that clients need not spell out the parameter types.
+PREPARE fpo_param_update AS
+ UPDATE fpo_param FOR PORTION OF valid_at FROM $1 TO $2 SET name = 'upd';
+SELECT parameter_types FROM pg_prepared_statements
+ WHERE name = 'fpo_param_update';
+
+PREPARE fpo_param_delete AS
+ DELETE FROM fpo_param FOR PORTION OF valid_at FROM $1 TO $2;
+SELECT parameter_types FROM pg_prepared_statements
+ WHERE name = 'fpo_param_delete';
+
+-- Only one bound parameterized.
+PREPARE fpo_param_one AS
+ UPDATE fpo_param FOR PORTION OF valid_at FROM $1 TO '2003-01-01'
+ SET name = 'one-bound';
+SELECT parameter_types FROM pg_prepared_statements
+ WHERE name = 'fpo_param_one';
+
+-- A parameter used as both bounds must still resolve to one type.
+PREPARE fpo_param_same AS
+ UPDATE fpo_param FOR PORTION OF valid_at FROM $1 TO $1 SET name = 'same';
+SELECT parameter_types FROM pg_prepared_statements
+ WHERE name = 'fpo_param_same';
+
+EXECUTE fpo_param_update('2002-01-01', '2003-01-01');
+SELECT * FROM fpo_param ORDER BY valid_at;
+
+DEALLOCATE fpo_param_control;
+DEALLOCATE fpo_param_portion;
+DEALLOCATE fpo_param_update;
+DEALLOCATE fpo_param_delete;
+DEALLOCATE fpo_param_one;
+DEALLOCATE fpo_param_same;
+
+-- The bounds we keep for deparsing are the coerced ones, so an untyped NULL
+-- bound renders with the range's subtype instead of "unknown".
+CREATE TABLE fpo_param_src (id int);
+CREATE RULE fpo_param_r AS ON DELETE TO fpo_param_src DO INSTEAD
+ DELETE FROM fpo_param FOR PORTION OF valid_at FROM NULL TO '2001-01-01';
+SELECT definition FROM pg_rules WHERE rulename = 'fpo_param_r';
+
+DROP TABLE fpo_param_src;
+DROP TABLE fpo_param;
+
RESET datestyle;
[parent: c13e961c603c]