<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/prepare.sgml,v 1.3 2003/03/10 03:53:49 tgl Exp $
PostgreSQL documentation
-->

<refentry id="SQL-PREPARE">
 <refmeta>
  <refentrytitle id="sql-prepare-title">PREPARE</refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
 <refnamediv>
  <refname>
   PREPARE
  </refname>
  <refpurpose>
   create a prepared query
  </refpurpose>
 </refnamediv>
 <refsynopsisdiv>
  <refsynopsisdivinfo>
   <date>2002-08-12</date>
  </refsynopsisdivinfo>
  <synopsis>
   PREPARE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable class="PARAMETER">datatype</replaceable> [, ...] ) ] AS <replaceable class="PARAMETER">query</replaceable>
  </synopsis>

  <refsect2 id="R2-SQL-PREPARE-1">
   <refsect2info>
    <date>2002-08-12</date>
   </refsect2info>
   <title>
    Inputs
   </title>

   <para>
    <variablelist>
     <varlistentry>
      <term><replaceable class="PARAMETER">plan_name</replaceable></term>
      <listitem>
       <para>
		An arbitrary name given to this particular prepared query. It
		must be unique within a single session, and is used to execute
		or remove a previously prepared query.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><replaceable class="PARAMETER">datatype</replaceable></term>
      <listitem>
       <para>
		The data-type of a parameter to the prepared query.
		To refer to the parameters in the prepared query itself,
		use <literal>$1</literal>, <literal>$2</literal>, etc.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><replaceable class="PARAMETER">query</replaceable></term>
      <listitem>
       <para>
	Any <command>SELECT</>, <command>INSERT</>, <command>UPDATE</>,
	or <command>DELETE</> query.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>

  <refsect2 id="R2-SQL-PREPARE-2">
   <refsect2info>
    <date>2002-08-12</date>
   </refsect2info>
   <title>
    Outputs
   </title>
   <para>

    <variablelist>
     <varlistentry>
      <term><computeroutput>
		<returnvalue>PREPARE</returnvalue>
       </computeroutput></term>
      <listitem>
       <para>
		The query has been prepared successfully.
       </para>
      </listitem>
     </varlistentry>

    </variablelist>
   </para>
  </refsect2>
 </refsynopsisdiv>

 <refsect1 id="R1-SQL-PREPARE-1">
  <refsect1info>
   <date>2002-08-12</date>
  </refsect1info>
  <title>
   Description
  </title>
  <para>
   <command>PREPARE</command> creates a prepared query. A prepared
   query is a server-side object that can be used to optimize
   performance. When the <command>PREPARE</command> statement is
   executed, the specified query is parsed, rewritten, and
   planned. When a subsequent <command>EXECUTE</command> statement is
   issued, the prepared query need only be executed. Thus, the
   parsing, rewriting, and planning stages are only performed once,
   instead of every time the query is executed.
  </para>

  <para>
   Prepared queries can take parameters: values that are
   substituted into the query when it is executed. To specify the
   parameters to a prepared query, include a list of data-types with
   the <command>PREPARE</command> statement. In the query itself, you
   can refer to the parameters by position using
   <literal>$1</literal>, <literal>$2</literal>, etc. When executing
   the query, 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.
  </para>

  <para>
   Prepared queries are stored locally (in the current backend), and
   only exist for the duration of the current database session. When
   the client exits, the prepared query is forgotten, and so it must be
   re-created before being used again. This also means that a single
   prepared query cannot be used by multiple simultaneous database
   clients; however, each client can create their own prepared query
   to use.
  </para>

  <para>
   Prepared queries have the largest performance advantage when a
   single backend is being used to execute a large number of similar
   queries. The performance difference will be particularly
   significant if the queries are complex to plan or rewrite. For
   example, if the query involves a join of many tables or requires
   the application of several rules. If the query is relatively simple
   to plan and rewrite but relatively expensive to execute, the
   performance advantage of prepared queries will be less noticeable.
  </para>

  <refsect2 id="R2-SQL-PREPARE-3">
   <refsect2info>
    <date>2002-08-12</date>
   </refsect2info>
   <title>
    Notes
   </title>

   <para>
	In some situations, the query plan produced by
	<productname>PostgreSQL</productname> for a prepared query may be
	inferior to the plan produced if the query were submitted and
	executed normally. This is because when the query is planned (and
	the optimizer attempts to determine the optimal query plan), the
	actual values of any parameters specified in the query are
	unavailable. <productname>PostgreSQL</productname> collects
	statistics on the distribution of data in the table, and can use
	constant values in a query to make guesses about the likely
	result of executing the query. Since this data is unavailable when
	planning prepared queries with parameters, the chosen plan may be
	sub-optimal. To examine the query plan
	<productname>PostgreSQL</productname> has chosen for a prepared
	query, use <command>EXPLAIN EXECUTE</command>.
   </para>

   <para>
	For more information on query planning and the statistics
	collected by <productname>PostgreSQL</productname> for query
	optimization purposes, see the <xref linkend="sql-analyze"
	endterm="sql-analyze-title"> documentation.
   </para>
  </refsect2>
 </refsect1>

 <refsect1 id="R1-SQL-PREPARE-3">
  <title>
   Compatibility
  </title>

  <refsect2 id="R2-SQL-PREPARE-4">
   <refsect2info>
    <date>2002-08-12</date>
   </refsect2info>
   <title>
    SQL92
   </title>
   <para>
	SQL92 includes a <command>PREPARE</command> statement, but it is
    only for use in embedded SQL clients. The
    <command>PREPARE</command> statement implemented by
    <productname>PostgreSQL</productname> also uses a somewhat
    different syntax.
   </para>
  </refsect2>
 </refsect1>
</refentry>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->
