Skip to content

Commit 106a369

Browse files
author
Neil Conway
committed
Allow the types of parameters to PREPARE to be inferred. If a parameter's
data type is unspecified or is declared to be "unknown", the type will be inferred from the context in which the parameter is used. This was already possible for protocol-level prepared statements.
1 parent f7ea931 commit 106a369

File tree

6 files changed

+95
-60
lines changed

6 files changed

+95
-60
lines changed

‎doc/src/sgml/ref/deallocate.sgml

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/deallocate.sgml,v 1.7 2004/09/30 04:23:27 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/deallocate.sgml,v 1.8 2006/01/15 22:18:46 neilc Exp $
33
PostgreSQL documentation
44
-->
55

@@ -25,7 +25,7 @@ PostgreSQL documentation
2525

2626
<refsynopsisdiv>
2727
<synopsis>
28-
DEALLOCATE [ PREPARE ] <replaceable class="parameter">plan_name</replaceable>
28+
DEALLOCATE [ PREPARE ] <replaceable class="parameter">name</replaceable>
2929
</synopsis>
3030
</refsynopsisdiv>
3131

@@ -58,7 +58,7 @@ DEALLOCATE [ PREPARE ] <replaceable class="parameter">plan_name</replaceable>
5858
</varlistentry>
5959

6060
<varlistentry>
61-
<term><replaceable class="parameter">plan_name</replaceable></term>
61+
<term><replaceable class="parameter">name</replaceable></term>
6262
<listitem>
6363
<para>
6464
The name of the prepared statement to deallocate.

‎doc/src/sgml/ref/execute.sgml

Lines changed: 6 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/execute.sgml,v 1.12 2004/09/30 04:23:27 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/execute.sgml,v 1.13 2006/01/15 22:18:46 neilc Exp $
33
PostgreSQL documentation
44
-->
55

@@ -25,7 +25,7 @@ PostgreSQL documentation
2525

2626
<refsynopsisdiv>
2727
<synopsis>
28-
EXECUTE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable class="PARAMETER">parameter</replaceable> [, ...] ) ]
28+
EXECUTE <replaceable class="PARAMETER">name</replaceable> [ (<replaceable class="PARAMETER">parameter</replaceable> [, ...] ) ]
2929
</synopsis>
3030
</refsynopsisdiv>
3131

@@ -60,7 +60,7 @@ EXECUTE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable c
6060

6161
<variablelist>
6262
<varlistentry>
63-
<term><replaceable class="PARAMETER">plan_name</replaceable></term>
63+
<term><replaceable class="PARAMETER">name</replaceable></term>
6464
<listitem>
6565
<para>
6666
The name of the prepared statement to execute.
@@ -73,10 +73,9 @@ EXECUTE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable c
7373
<listitem>
7474
<para>
7575
The actual value of a parameter to the prepared statement. This
76-
must be an expression yielding a value of a type compatible with
77-
the data type specified for this parameter position in the
78-
<command>PREPARE</command> command that created the prepared
79-
statement.
76+
must be an expression yielding a value that is compatible with
77+
the data type of this parameter, as was determined when the
78+
prepared statement was created.
8079
</para>
8180
</listitem>
8281
</varlistentry>

‎doc/src/sgml/ref/prepare.sgml

Lines changed: 25 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/prepare.sgml,v 1.17 2006/01/08 07:00:25 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/prepare.sgml,v 1.18 2006/01/15 22:18:46 neilc Exp $
33
PostgreSQL documentation
44
-->
55

@@ -25,7 +25,7 @@ PostgreSQL documentation
2525

2626
<refsynopsisdiv>
2727
<synopsis>
28-
PREPARE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable class="PARAMETER">datatype</replaceable> [, ...] ) ] AS <replaceable class="PARAMETER">statement</replaceable>
28+
PREPARE <replaceable class="PARAMETER">name</replaceable> [ (<replaceable class="PARAMETER">datatype</replaceable> [, ...] ) ] AS <replaceable class="PARAMETER">statement</replaceable>
2929
</synopsis>
3030
</refsynopsisdiv>
3131

@@ -45,13 +45,15 @@ PREPARE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable c
4545

4646
<para>
4747
Prepared statements can take parameters: values that are
48-
substituted into the statement when it is executed. To include
49-
parameters in a prepared statement, supply a list of data types in
50-
the <command>PREPARE</command> statement, and, in the statement to
51-
be prepared itself, refer to the parameters by position using
52-
<literal>$1</literal>, <literal>$2</literal>, etc. When executing
53-
the statement, specify the actual values for these parameters in
54-
the <command>EXECUTE</command> statement. Refer to <xref
48+
substituted into the statement when it is executed. When creating
49+
the prepared statement, refer to parameters by position, using
50+
<literal>$1</>, <literal>$2</>, etc. A corresponding list of
51+
parameter data types can optionally be specified. When a
52+
parameter's data type is not specified or is declared as
53+
<literal>unknown</literal>, the type is inferred from the context
54+
in which the parameter is used (if possible). When executing the
55+
statement, specify the actual values for these parameters in the
56+
<command>EXECUTE</command> statement. Refer to <xref
5557
linkend="sql-execute" endterm="sql-execute-title"> for more
5658
information about that.
5759
</para>
@@ -84,7 +86,7 @@ PREPARE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable c
8486

8587
<variablelist>
8688
<varlistentry>
87-
<term><replaceable class="PARAMETER">plan_name</replaceable></term>
89+
<term><replaceable class="PARAMETER">name</replaceable></term>
8890
<listitem>
8991
<para>
9092
An arbitrary name given to this particular prepared
@@ -99,8 +101,11 @@ PREPARE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable c
99101
<term><replaceable class="PARAMETER">datatype</replaceable></term>
100102
<listitem>
101103
<para>
102-
The data type of a parameter to the prepared statement. To
103-
refer to the parameters in the prepared statement itself, use
104+
The data type of a parameter to the prepared statement. If the
105+
data type of a particular parameter is unspecified or is
106+
specified as <literal>unknown</literal>, it will be inferred
107+
from the context in which the parameter is used. To refer to the
108+
parameters in the prepared statement itself, use
104109
<literal>$1</literal>, <literal>$2</literal>, etc.
105110
</para>
106111
</listitem>
@@ -155,8 +160,8 @@ PREPARE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable c
155160
<refsect1 id="sql-prepare-examples">
156161
<title id="sql-prepare-examples-title">Examples</title>
157162
<para>
158-
Create a prepared query for an <command>INSERT</command> statement,
159-
and then execute it:
163+
Create a prepared statement for an <command>INSERT</command>
164+
statement, and then execute it:
160165
<programlisting>
161166
PREPARE fooplan (int, text, bool, numeric) AS
162167
INSERT INTO foo VALUES($1, $2, $3, $4);
@@ -165,14 +170,17 @@ EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
165170
</para>
166171

167172
<para>
168-
Create a prepared query for a <command>SELECT</command> statement,
169-
and then execute it:
173+
Create a prepared statement for a <command>SELECT</command>
174+
statement, and then execute it:
170175
<programlisting>
171-
PREPARE usrrptplan (int, date) AS
176+
PREPARE usrrptplan (int) AS
172177
SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
173178
AND l.date = $2;
174179
EXECUTE usrrptplan(1, current_date);
175180
</programlisting>
181+
182+
Note that the data type of the second parameter is not specified,
183+
so it is inferred from the context in which <literal>$2</> is used.
176184
</para>
177185
</refsect1>
178186
<refsect1>

‎src/backend/parser/analyze.c

Lines changed: 29 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
9-
* $PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.327 2005/11/22 18:17:15 momjian Exp $
9+
* $PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.328 2006/01/15 22:18:46 neilc Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -2584,10 +2584,11 @@ static Query *
25842584
transformPrepareStmt(ParseState *pstate, PrepareStmt *stmt)
25852585
{
25862586
Query *result = makeNode(Query);
2587-
List *argtype_oids = NIL; /* argtype OIDs in a list */
2587+
List *argtype_oids; /* argtype OIDs in a list */
25882588
Oid *argtoids = NULL; /* and as an array */
25892589
int nargs;
25902590
List *queries;
2591+
int i;
25912592

25922593
result->commandType = CMD_UTILITY;
25932594
result->utilityStmt = (Node *) stmt;
@@ -2598,27 +2599,27 @@ transformPrepareStmt(ParseState *pstate, PrepareStmt *stmt)
25982599
if (nargs)
25992600
{
26002601
ListCell *l;
2601-
int i = 0;
26022602

26032603
argtoids = (Oid *) palloc(nargs * sizeof(Oid));
2604+
i = 0;
26042605

26052606
foreach(l, stmt->argtypes)
26062607
{
26072608
TypeName *tn = lfirst(l);
26082609
Oid toid = typenameTypeId(tn);
26092610

2610-
argtype_oids = lappend_oid(argtype_oids, toid);
26112611
argtoids[i++] = toid;
26122612
}
26132613
}
26142614

2615-
stmt->argtype_oids = argtype_oids;
2616-
26172615
/*
2618-
* Analyze the statement using these parameter types (any parameters
2619-
* passed in from above us will not be visible to it).
2616+
* Analyze the statement using these parameter types (any
2617+
* parameters passed in from above us will not be visible to it),
2618+
* allowing information about unknown parameters to be deduced
2619+
* from context.
26202620
*/
2621-
queries = parse_analyze((Node *) stmt->query, argtoids, nargs);
2621+
queries = parse_analyze_varparams((Node *) stmt->query,
2622+
&argtoids, &nargs);
26222623

26232624
/*
26242625
* Shouldn't get any extra statements, since grammar only allows
@@ -2627,8 +2628,26 @@ transformPrepareStmt(ParseState *pstate, PrepareStmt *stmt)
26272628
if (list_length(queries) != 1)
26282629
elog(ERROR, "unexpected extra stuff in prepared statement");
26292630

2630-
stmt->query = linitial(queries);
2631+
/*
2632+
* Check that all parameter types were determined, and convert the
2633+
* array of OIDs into a list for storage.
2634+
*/
2635+
argtype_oids = NIL;
2636+
for (i = 0; i < nargs; i++)
2637+
{
2638+
Oid argtype = argtoids[i];
26312639

2640+
if (argtype == InvalidOid || argtype == UNKNOWNOID)
2641+
ereport(ERROR,
2642+
(errcode(ERRCODE_INDETERMINATE_DATATYPE),
2643+
errmsg("could not determine data type of parameter $%d",
2644+
i + 1)));
2645+
2646+
argtype_oids = lappend_oid(argtype_oids, argtype);
2647+
}
2648+
2649+
stmt->argtype_oids = argtype_oids;
2650+
stmt->query = linitial(queries);
26322651
return result;
26332652
}
26342653

‎src/test/regress/expected/prepare.out

Lines changed: 23 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -58,14 +58,6 @@ SELECT name, statement, parameter_types FROM pg_prepared_statements;
5858
PREPARE q2(text) AS
5959
SELECT datname, datistemplate, datallowconn
6060
FROM pg_database WHERE datname = $1;
61-
SELECT name, statement, parameter_types FROM pg_prepared_statements;
62-
name | statement | parameter_types
63-
------+--------------------------------------------------------------------------------------------------------+-----------------
64-
q2 | PREPARE q2(text) AS
65-
SELECT datname, datistemplate, datallowconn
66-
FROM pg_database WHERE datname = $1; | {25}
67-
(1 row)
68-
6961
EXECUTE q2('regression');
7062
datname | datistemplate | datallowconn
7163
------------+---------------+--------------
@@ -75,17 +67,6 @@ EXECUTE q2('regression');
7567
PREPARE q3(text, int, float, boolean, oid, smallint) AS
7668
SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
7769
ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);
78-
SELECT name, statement, parameter_types FROM pg_prepared_statements;
79-
name | statement | parameter_types
80-
------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------
81-
q2 | PREPARE q2(text) AS
82-
SELECT datname, datistemplate, datallowconn
83-
FROM pg_database WHERE datname = $1; | {25}
84-
q3 | PREPARE q3(text, int, float, boolean, oid, smallint) AS
85-
SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
86-
ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int); | {25,23,701,16,26,21}
87-
(2 rows)
88-
8970
EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint);
9071
unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
9172
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
@@ -160,3 +141,26 @@ SELECT * FROM q5_prep_results;
160141
5905 | 9537 | 1 | 1 | 5 | 5 | 5 | 905 | 1905 | 905 | 5905 | 10 | 11 | DTAAAA | VCOAAA | HHHHxx
161142
(16 rows)
162143

144+
-- unknown or unspecified parameter types: should succeed
145+
PREPARE q6 AS
146+
SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2;
147+
PREPARE q7(unknown) AS
148+
SELECT * FROM road WHERE thepath = $1;
149+
SELECT name, statement, parameter_types FROM pg_prepared_statements
150+
ORDER BY name;
151+
name | statement | parameter_types
152+
------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------
153+
q2 | PREPARE q2(text) AS
154+
SELECT datname, datistemplate, datallowconn
155+
FROM pg_database WHERE datname = $1; | {25}
156+
q3 | PREPARE q3(text, int, float, boolean, oid, smallint) AS
157+
SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
158+
ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int); | {25,23,701,16,26,21}
159+
q5 | PREPARE q5(int, text) AS
160+
SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2; | {23,25}
161+
q6 | PREPARE q6 AS
162+
SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2; | {23,19}
163+
q7 | PREPARE q7(unknown) AS
164+
SELECT * FROM road WHERE thepath = $1; | {602}
165+
(5 rows)
166+

‎src/test/regress/sql/prepare.sql

Lines changed: 9 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -34,16 +34,12 @@ PREPARE q2(text) AS
3434
SELECT datname, datistemplate, datallowconn
3535
FROM pg_database WHERE datname = $1;
3636

37-
SELECT name, statement, parameter_types FROM pg_prepared_statements;
38-
3937
EXECUTE q2('regression');
4038

4139
PREPARE q3(text, int, float, boolean, oid, smallint) AS
4240
SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
4341
ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);
4442

45-
SELECT name, statement, parameter_types FROM pg_prepared_statements;
46-
4743
EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint);
4844

4945
-- too few params
@@ -63,3 +59,12 @@ PREPARE q5(int, text) AS
6359
SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2;
6460
CREATE TEMPORARY TABLE q5_prep_results AS EXECUTE q5(200, 'DTAAAA');
6561
SELECT * FROM q5_prep_results;
62+
63+
-- unknown or unspecified parameter types: should succeed
64+
PREPARE q6 AS
65+
SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2;
66+
PREPARE q7(unknown) AS
67+
SELECT * FROM road WHERE thepath = $1;
68+
69+
SELECT name, statement, parameter_types FROM pg_prepared_statements
70+
ORDER BY name;

0 commit comments

Comments
 (0)