Provided by: postgresql-client-8.0_8.0.7-2build1_i386 bug

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
       prepared 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
       prepared  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(7)] for more information about
       that.

       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 DEALLOCATE command.

       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.

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 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. 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  chosen  plan  may  be  suboptimal.  To
       examine  the query plan PostgreSQL has chosen for a prepared statement,
       use EXPLAIN [explain(7)].

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

EXAMPLES

       Create a prepared query for an INSERT statement, and then execute it:

       PREPARE fooplan (int, text, bool, numeric) AS
           INSERT INTO foo VALUES($1, $2, $3, $4);
       EXECUTE fooplan(1, ’Hunter Valley’, ’t’, 200.00);

       Create a prepared query for a SELECT statement, and then execute it:

       PREPARE usrrptplan (int, date) 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);

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.

SEE ALSO

       DEALLOCATE [deallocate(7)], EXECUTE [execute(l)]