prepare

PREPARE()			 SQL Commands			    PREPARE()



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 pars-
       ing, 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 state-
       ment, and, in the statement to be prepared itself, refer to the param-
       eters  by  position  using  $1, $2, etc. When executing the statement,
       specify the actual values for these parameters in the  EXECUTE  state-
       ment. Refer to EXECUTE [execute(7)] for more information about that.

       Prepared	 statements  are  only	stored in and for the duration of the
       current database session. When the session ends, the  prepared  state-
       ment  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
       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 by for a  prepared  state-
       ment  may  be inferior to the plan produced if the statement were sub-
       mitted 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 Post-
       greSQL 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(7)] 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	  2008-01-03			    PREPARE()