Continuing the discussion in [0], here is a patch that allows parameter
references in the arguments of the EXECUTE command. The main purpose is
submitting protocol-level parameters, but the added regression test case
shows another way to exercise it.
What's confusing is that the code already contains a reference that
indicates that this should be possible:
/* Evaluate parameters, if any */
if (entry->plansource->num_params > 0)
{
/*
* Need an EState to evaluate parameters; must not delete it
till end
* of query, in case parameters are pass-by-reference. Note
that the
* passed-in "params" could possibly be referenced in the parameter
* expressions.
*/
estate = CreateExecutorState();
estate->es_param_list_info = params;
paramLI = EvaluateParams(pstate, entry, stmt->params, estate);
}
I'm not sure what this is supposed to do without my patch on top of it.
If I remove the estate->es_param_list_info assignment, no tests fail
(except the one I added). Either this is a leftover from previous
variants of this code (as discussed in [0]), or there is something I
haven't understood.
[0]:
https://www.postgresql.org/message-id/flat/6e7aa4a1-be6a-1a75-b1f9-83a678e5184a%402ndquadrant.com
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 9c1ba747f5c8b424ca8a1e4c0feb4c859312203e Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 11 Feb 2020 19:55:25 +0100
Subject: [PATCH v1] Support external parameters in EXECUTE command
This allows the arguments of the EXECUTE command to be parameters
themselves, for example passed as separate parameters on the protocol
level.
---
src/backend/commands/prepare.c | 40 +++++++++++++++++++++++++++
src/test/regress/expected/prepare.out | 23 +++++++++++++++
src/test/regress/sql/prepare.sql | 21 ++++++++++++++
3 files changed, 84 insertions(+)
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index c4e4b6eaec..543de3493d 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -33,6 +33,7 @@
#include "tcop/pquery.h"
#include "tcop/utility.h"
#include "utils/builtins.h"
+#include "utils/lsyscache.h"
#include "utils/snapmgr.h"
#include "utils/timestamp.h"
@@ -175,6 +176,42 @@ PrepareQuery(ParseState *pstate, PrepareStmt *stmt,
true);
}
+/*
+ * Parser callback for resolving parameter references from an existing
+ * ParamListInfo structure.
+ */
+static Node *
+pli_paramref_hook(ParseState *pstate, ParamRef *pref)
+{
+ ParamListInfo paramInfo = (ParamListInfo) pstate->p_ref_hook_state;
+ int paramno = pref->number;
+ ParamExternData *ped;
+ ParamExternData pedws;
+ Param *param;
+
+ /* Check parameter number is valid */
+ if (paramno <= 0 || paramno > paramInfo->numParams)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_PARAMETER),
+ errmsg("there is no parameter $%d", paramno),
+ parser_errposition(pstate, pref->location)));
+
+ if (paramInfo->paramFetch != NULL)
+ ped = paramInfo->paramFetch(paramInfo, paramno, false, &pedws);
+ else
+ ped = ¶mInfo->params[paramno - 1];
+
+ param = makeNode(Param);
+ param->paramkind = PARAM_EXTERN;
+ param->paramid = paramno;
+ param->paramtype = ped->ptype;
+ param->paramtypmod = -1;
+ param->paramcollid = get_typcollation(param->paramtype);
+ param->location = pref->location;
+
+ return (Node *) param;
+}
+
/*
* ExecuteQuery --- implement the 'EXECUTE' utility statement.
*
@@ -199,6 +236,9 @@ ExecuteQuery(ParseState *pstate,
int eflags;
long count;
+ pstate->p_ref_hook_state = (void *) params;
+ pstate->p_paramref_hook = pli_paramref_hook;
+
/* Look it up in the hash table */
entry = FetchPreparedStatement(stmt->name, true);
diff --git a/src/test/regress/expected/prepare.out
b/src/test/regress/expected/prepare.out
index 3306c696b1..a0fec13c6b 100644
--- a/src/test/regress/expected/prepare.out
+++ b/src/test/regress/expected/prepare.out
@@ -187,3 +187,26 @@ SELECT name, statement, parameter_types FROM
pg_prepared_statements
------+-----------+-----------------
(0 rows)
+-- check parameter handling
+CREATE TABLE t1 (a int);
+PREPARE p1 AS INSERT INTO t1 (a) VALUES ($1);
+CREATE FUNCTION f1(x int) RETURNS int
+LANGUAGE SQL
+AS $$
+EXECUTE p1($1);
+SELECT null::int;
+$$;
+SELECT f1(2);
+ f1
+----
+
+(1 row)
+
+SELECT * FROM t1;
+ a
+---
+ 2
+(1 row)
+
+DROP FUNCTION f1(int);
+DROP TABLE t1;
diff --git a/src/test/regress/sql/prepare.sql b/src/test/regress/sql/prepare.sql
index 985d0f05c9..6a16858482 100644
--- a/src/test/regress/sql/prepare.sql
+++ b/src/test/regress/sql/prepare.sql
@@ -78,3 +78,24 @@ CREATE TEMPORARY TABLE q5_prep_nodata AS EXECUTE q5(200,
'DTAAAA')
DEALLOCATE ALL;
SELECT name, statement, parameter_types FROM pg_prepared_statements
ORDER BY name;
+
+
+-- check parameter handling
+
+CREATE TABLE t1 (a int);
+
+PREPARE p1 AS INSERT INTO t1 (a) VALUES ($1);
+
+CREATE FUNCTION f1(x int) RETURNS int
+LANGUAGE SQL
+AS $$
+EXECUTE p1($1);
+SELECT null::int;
+$$;
+
+SELECT f1(2);
+
+SELECT * FROM t1;
+
+DROP FUNCTION f1(int);
+DROP TABLE t1;
--
2.25.0
