/usr/man/cat.l/prepare.l.Z(/usr/man/cat.l/prepare.l.Z)

NAME

       PREPARE - prepare a statement for execution

SYNOPSIS

       PREPARE plan_name [ (datatype [, ...] ) ] AS statement

DESCRIPTION

       PREPARE creates a prepared statement. A prepared statement is a server-
       side object that can be used to optimize performance. When the  PREPARE
       statement  is  executed,  the specified statement is parsed, rewritten,
       and planned. When an EXECUTE command is subsequently issued,  the  pre-
       pared  statement  need  only be executed. Thus, the parsing, rewriting,
       and planning stages are only performed once, instead of every time  the
       statement is executed.

       Prepared  statements  can  take parameters: values that are substituted
       into the statement when it is executed. To include parameters in a pre-
       pared  statement, supply a list of data types in the PREPARE statement,
       and, in the statement to be prepared itself, refer to the parameters by
       position  using  $1, $2, etc. When executing the statement, specify the
       actual values for these parameters in the EXECUTE statement.  Refer  to
       EXECUTE [execute(l)] for more information about that.

       Prepared statements are only stored in and for the duration of the cur-
       rent database session. When the session ends, the prepared statement is
       forgotten,  and  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.

       Prepared statements have the largest performance advantage when a  sin-
       gle  session  is being used to execute a large number of similar state-
       ments. 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 re-
       write but relatively expensive to execute, the performance advantage of
       prepared statements will be less noticeable.

PARAMETERS

       plan_name
              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.

       datatype
              The data type of a parameter to the prepared statement. To refer
              to the parameters in the prepared statement itself, use $1,  $2,
              etc.

       statement
              Any SELECT, INSERT, UPDATE, or DELETE statement.

NOTES

       In some situations, the query plan produced by for a prepared statement
       may be inferior to the plan produced if the  statement  were  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 unavail-
       able. PostgreSQL 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 cho-
       sen plan may be suboptimal. To examine the query  plan  PostgreSQL  has
       chosen for a prepared statement, use EXPLAIN EXECUTE.

       For  more information on query planning and the statistics collected by
       PostgreSQL for that purpose, see the  ANALYZE  [analyze(l)]  documenta-
       tion.

COMPATIBILITY

       The  SQL  standard includes a PREPARE statement, but it is only for use
       in embedded SQL. This version of the  PREPARE  statement  also  uses  a
       somewhat different syntax.

SQL - Language Statements         2003-11-02                        PREPARE(l)