<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/deallocate.sgml,v 1.7 2004/09/30 04:23:27 neilc Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/deallocate.sgml,v 1.8 2006/01/15 22:18:46 neilc Exp $

PostgreSQL documentation

-->

<refsynopsisdiv>

<synopsis>

DEALLOCATE [ PREPARE ] <replaceable class="parameter">plan_name</replaceable>

DEALLOCATE [ PREPARE ] <replaceable class="parameter">name</replaceable>

</synopsis>

</refsynopsisdiv>

</varlistentry>

<varlistentry>

<term><replaceable class="parameter">plan_name</replaceable></term>

<term><replaceable class="parameter">name</replaceable></term>

<listitem>

<para>

The name of the prepared statement to deallocate.

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/execute.sgml,v 1.12 2004/09/30 04:23:27 neilc Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/execute.sgml,v 1.13 2006/01/15 22:18:46 neilc Exp $

PostgreSQL documentation

-->

<refsynopsisdiv>

<synopsis>

EXECUTE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable class="PARAMETER">parameter</replaceable> [, ...] ) ]

EXECUTE <replaceable class="PARAMETER">name</replaceable> [ (<replaceable class="PARAMETER">parameter</replaceable> [, ...] ) ]

</synopsis>

</refsynopsisdiv>

<variablelist>

<varlistentry>

<term><replaceable class="PARAMETER">plan_name</replaceable></term>

<term><replaceable class="PARAMETER">name</replaceable></term>

<listitem>

<para>

The name of the prepared statement to execute.

<listitem>

<para>

The actual value of a parameter to the prepared statement. This

must be an expression yielding a value of a type compatible with

the data type specified for this parameter position in the

<command>PREPARE</command> command that created the prepared

statement.

must be an expression yielding a value that is compatible with

the data type of this parameter, as was determined when the

prepared statement was created.

</para>

</listitem>

</varlistentry>

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/prepare.sgml,v 1.17 2006/01/08 07:00:25 neilc Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/prepare.sgml,v 1.18 2006/01/15 22:18:46 neilc Exp $

PostgreSQL documentation

-->

<refsynopsisdiv>

<synopsis>

PREPARE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable class="PARAMETER">datatype</replaceable> [, ...] ) ] AS <replaceable class="PARAMETER">statement</replaceable>

PREPARE <replaceable class="PARAMETER">name</replaceable> [ (<replaceable class="PARAMETER">datatype</replaceable> [, ...] ) ] AS <replaceable class="PARAMETER">statement</replaceable>

</synopsis>

</refsynopsisdiv>

<para>

Prepared statements can take parameters: values that are

substituted into the statement when it is executed. To include

parameters in a prepared statement, supply a list of data types in

the <command>PREPARE</command> statement, and, in the statement to

be prepared itself, refer to the parameters by position using

<literal>$1</literal>, <literal>$2</literal>, etc. When executing

the statement, specify the actual values for these parameters in

the <command>EXECUTE</command> statement. Refer to <xref

substituted into the statement when it is executed. When creating

the prepared statement, refer to parameters by position, using

<literal>$1</>, <literal>$2</>, etc. A corresponding list of

parameter data types can optionally be specified. When a

parameter's data type is not specified or is declared as

<literal>unknown</literal>, the type is inferred from the context

in which the parameter is used (if possible). When executing the

statement, specify the actual values for these parameters in the

<command>EXECUTE</command> statement. Refer to <xref

linkend="sql-execute" endterm="sql-execute-title"> for more

information about that.

</para>

<variablelist>

<varlistentry>

<term><replaceable class="PARAMETER">plan_name</replaceable></term>

<term><replaceable class="PARAMETER">name</replaceable></term>

<listitem>

<para>

An arbitrary name given to this particular prepared

<term><replaceable class="PARAMETER">datatype</replaceable></term>

<listitem>

<para>

The data type of a parameter to the prepared statement. To

refer to the parameters in the prepared statement itself, use

The data type of a parameter to the prepared statement. If the

data type of a particular parameter is unspecified or is

specified as <literal>unknown</literal>, it will be inferred

from the context in which the parameter is used. To refer to the

parameters in the prepared statement itself, use

<literal>$1</literal>, <literal>$2</literal>, etc.

</para>

</listitem>

<refsect1 id="sql-prepare-examples">

<title id="sql-prepare-examples-title">Examples</title>

<para>

Create a preparedquery for an <command>INSERT</command> statement,

and then execute it:

Create a preparedstatement for an <command>INSERT</command>

statement,and then execute it:

<programlisting>

PREPARE fooplan (int, text, bool, numeric) AS

INSERT INTO foo VALUES($1, $2, $3, $4);

</para>

<para>

Create a preparedquery for a <command>SELECT</command> statement,

and then execute it:

Create a preparedstatement for a <command>SELECT</command>

statement,and then execute it:

<programlisting>

PREPARE usrrptplan (int, date) AS

PREPARE usrrptplan (int) AS

SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid

AND l.date = $2;

EXECUTE usrrptplan(1, current_date);

</programlisting>

Note that the data type of the second parameter is not specified,

so it is inferred from the context in which <literal>$2</> is used.

</para>

</refsect1>

<refsect1>

transformPrepareStmt(ParseState*pstate,PrepareStmt*stmt)

{

Query*result=makeNode(Query);

List*argtype_oids=NIL;/* argtype OIDs in a list */

List*argtype_oids;/* argtype OIDs in a list */

Oid*argtoids=NULL;/* and as an array */

intnargs;

List*queries;

inti;

result->commandType=CMD_UTILITY;

result->utilityStmt= (Node*)stmt;

if (nargs)

{

ListCell*l;

inti=0;

argtoids= (Oid*)palloc(nargs*sizeof(Oid));

i=0;

foreach(l,stmt->argtypes)

{

TypeName*tn=lfirst(l);

Oidtoid=typenameTypeId(tn);

argtype_oids=lappend_oid(argtype_oids,toid);

argtoids[i++]=toid;

}

}

stmt->argtype_oids=argtype_oids;

queries=parse_analyze((Node*)stmt->query,argtoids,nargs);

queries=parse_analyze_varparams((Node*)stmt->query,

&argtoids,&nargs);

if (list_length(queries)!=1)

elog(ERROR,"unexpected extra stuff in prepared statement");

stmt->query=linitial(queries);

argtype_oids=NIL;

for (i=0;i<nargs;i++)

{

Oidargtype=argtoids[i];

if (argtype==InvalidOid||argtype==UNKNOWNOID)

ereport(ERROR,

(errcode(ERRCODE_INDETERMINATE_DATATYPE),

errmsg("could not determine data type of parameter $%d",

i+1)));

argtype_oids=lappend_oid(argtype_oids,argtype);

}

stmt->argtype_oids=argtype_oids;

stmt->query=linitial(queries);

returnresult;

}

PREPARE q2(text) AS

SELECT datname, datistemplate, datallowconn

FROM pg_database WHERE datname = $1;

SELECT name, statement, parameter_types FROM pg_prepared_statements;

name | statement | parameter_types

------+--------------------------------------------------------------------------------------------------------+-----------------

q2 | PREPARE q2(text) AS

SELECT datname, datistemplate, datallowconn

FROM pg_database WHERE datname = $1; | {25}

(1 row)

EXECUTE q2('regression');

datname | datistemplate | datallowconn

------------+---------------+--------------

PREPARE q3(text, int, float, boolean, oid, smallint) AS

SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR

ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);

SELECT name, statement, parameter_types FROM pg_prepared_statements;

name | statement | parameter_types

------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------

q2 | PREPARE q2(text) AS

SELECT datname, datistemplate, datallowconn

FROM pg_database WHERE datname = $1; | {25}

q3 | PREPARE q3(text, int, float, boolean, oid, smallint) AS

SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR

ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int); | {25,23,701,16,26,21}

(2 rows)

EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint);

unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4

---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------

5905 | 9537 | 1 | 1 | 5 | 5 | 5 | 905 | 1905 | 905 | 5905 | 10 | 11 | DTAAAA | VCOAAA | HHHHxx

(16 rows)

-- unknown or unspecified parameter types: should succeed

PREPARE q6 AS

SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2;

PREPARE q7(unknown) AS

SELECT * FROM road WHERE thepath = $1;

SELECT name, statement, parameter_types FROM pg_prepared_statements

ORDER BY name;

name | statement | parameter_types

------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------

q2 | PREPARE q2(text) AS

SELECT datname, datistemplate, datallowconn

FROM pg_database WHERE datname = $1; | {25}

q3 | PREPARE q3(text, int, float, boolean, oid, smallint) AS

SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR

ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int); | {25,23,701,16,26,21}

q5 | PREPARE q5(int, text) AS

SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2; | {23,25}

q6 | PREPARE q6 AS

SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2; | {23,19}

q7 | PREPARE q7(unknown) AS

SELECT * FROM road WHERE thepath = $1; | {602}

(5 rows)

SELECT datname, datistemplate, datallowconn

FROM pg_databaseWHERE datname= $1;

SELECT name, statement, parameter_typesFROM pg_prepared_statements;

EXECUTE q2('regression');

PREPARE q3(text,int, float,boolean,oid,smallint)AS

SELECT*FROM tenk1WHERE string4= $1AND (four= $2OR

ten= $3::bigintOR true= $4ORoid= $5OR odd= $6::int);

SELECT name, statement, parameter_typesFROM pg_prepared_statements;

EXECUTE q3('AAAAxx',5::smallint,10.5::float, false,500::oid,4::bigint);

SELECT*FROM tenk1WHERE unique1= $1OR stringu1= $2;

CREATE TEMPORARY TABLE q5_prep_resultsAS EXECUTE q5(200,'DTAAAA');

SELECT*FROM q5_prep_results;

PREPARE q6AS

SELECT*FROM tenk1WHERE unique1= $1AND stringu1= $2;

PREPARE q7(unknown)AS

SELECT*FROM roadWHERE thepath= $1;

SELECT name, statement, parameter_typesFROM pg_prepared_statements

ORDER BY name;