| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/prepare.sgml,v 1.21 2006/09/18 19:54:01 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>prepare a statement for execution</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-prepare"> |
| <primary>PREPARE</primary> |
| </indexterm> |
| |
| <indexterm zone="sql-prepare"> |
| <primary>prepared statements</primary> |
| <secondary>creating</secondary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| PREPARE <replaceable class="PARAMETER">name</replaceable> [ (<replaceable class="PARAMETER">datatype</replaceable> [, ...] ) ] AS <replaceable class="PARAMETER">statement</replaceable> |
| </synopsis> |
| </refsynopsisdiv> |
| |
| <refsect1> |
| <title>Description</title> |
| |
| <para> |
| <command>PREPARE</command> creates a prepared statement, |
| possibly with unbound parameters. A prepared statement is a |
| server-side object that can be used to optimize performance. |
| A prepared statement may be subsequently executed with a |
| binding for its parameters. Greenplum Database may choose to |
| replan the query for different executions of the same |
| prepared statement. |
| </para> |
| |
| <para> |
| Prepared statements can take parameters: values that are |
| 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> |
| |
| <para> |
| Prepared statements only last for the duration of the current |
| database session. When the session ends, the prepared statement is |
| forgotten, so it must be recreated before being used again. This |
| also means that a single prepared statement cannot be used by |
| multiple simultaneous database clients; however, each client can create |
| their own prepared statement to use. The prepared statement can be |
| manually cleaned up using the <xref linkend="sql-deallocate" |
| endterm="sql-deallocate-title"> command. |
| </para> |
| |
| <para> |
| Prepared statements have the largest performance advantage when a |
| single session is being used to execute a large number of similar |
| statements. The performance difference will be particularly |
| significant if the statements 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 statement is relatively simple |
| to plan and rewrite but relatively expensive to execute, the |
| performance advantage of prepared statements will be less noticeable. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Parameters</title> |
| |
| <variablelist> |
| <varlistentry> |
| <term><replaceable class="PARAMETER">name</replaceable></term> |
| <listitem> |
| <para> |
| An arbitrary name given to this particular prepared |
| statement. It must be unique within a single session and is |
| subsequently used to execute or deallocate a previously prepared |
| statement. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">datatype</replaceable></term> |
| <listitem> |
| <para> |
| 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> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">statement</replaceable></term> |
| <listitem> |
| <para> |
| Any <command>SELECT</>, <command>INSERT</>, <command>UPDATE</>, |
| <command>DELETE</>, or <command>VALUES</> statement. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| </refsect1> |
| |
| <refsect1> |
| <title>Notes</title> |
| |
| <para> |
| In some situations, the query plan produced for a prepared |
| statement will be inferior to the query plan that would have been |
| chosen if the statement had been submitted and executed |
| normally. This is because when the statement is planned and the |
| planner attempts to determine the optimal query plan, the actual |
| values of any parameters specified in the statement are |
| unavailable. <productname>PostgreSQL</productname> collects |
| statistics on the distribution of data in the table, and can use |
| constant values in a statement to make guesses about the likely |
| result of executing the statement. Since this data is unavailable |
| when planning prepared statements with parameters, the chosen plan |
| may be suboptimal. To examine the query plan |
| <productname>PostgreSQL</productname> has chosen for a prepared |
| statement, use <xref linkend="sql-explain" |
| endterm="sql-explain-title">. |
| </para> |
| |
| <para> |
| For more information on query planning and the statistics collected |
| by <productname>PostgreSQL</productname> for that purpose, see |
| the <xref linkend="sql-analyze" endterm="sql-analyze-title"> |
| documentation. |
| </para> |
| |
| <para> |
| You can see all available prepared statements of a session by querying the |
| <link linkend="view-pg-prepared-statements"><structname>pg_prepared_statements</structname></link> |
| system view. |
| </para> |
| </refsect1> |
| |
| <refsect1 id="sql-prepare-examples"> |
| <title id="sql-prepare-examples-title">Examples</title> |
| <para> |
| Create a prepared statement 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); |
| EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00); |
| </programlisting> |
| </para> |
| |
| <para> |
| Create a prepared statement for a <command>SELECT</command> |
| statement, and then execute it: |
| <programlisting> |
| 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> |
| <title>Compatibility</title> |
| |
| <para> |
| The SQL standard includes a <command>PREPARE</command> statement, |
| but it is only for use in embedded SQL. This version of the |
| <command>PREPARE</command> statement also uses a somewhat different |
| syntax. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>See Also</title> |
| |
| <simplelist type="inline"> |
| <member><xref linkend="sql-deallocate" endterm="sql-deallocate-title"></member> |
| <member><xref linkend="sql-execute" endterm="sql-execute-title"></member> |
| </simplelist> |
| </refsect1> |
| </refentry> |