Man Pages

prepare(7) - phpMan prepare(7) - phpMan

Command: man perldoc info search(apropos)  


PREPARE(7)                       SQL Commands                       PREPARE(7)



NAME
       PREPARE - prepare a statement for execution


SYNOPSIS
       PREPARE 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.
       When  creating the prepared statement, refer to parameters by position, using $1, $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  unknown, 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 EXECUTE statement. Refer to EXE-
       CUTE [execute(7)] for more information about that.

       Prepared statements only last for the duration of the current database session. When the session ends, the pre-
       pared 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
       [deallocate(7)] 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  state-
       ments  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  expen-
       sive to execute, the performance advantage of prepared statements will be less noticeable.

PARAMETERS
       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. If the data type of a  particular  parameter  is
              unspecified  or  is specified as unknown, it will be inferred from the context in which the parameter is
              used. To refer to the parameters in the prepared statement itself, use $1, $2, etc.

       statement
              Any SELECT, INSERT, UPDATE, DELETE, or VALUES 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  exe-
       cuting  the  statement.  Since  this data is unavailable when planning prepared statements with parameters, the
       chosen plan might 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.

       You can see all available prepared statements of a session by querying the pg_prepared_statements system  view.

EXAMPLES
       Create a prepared statement 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 statement for a SELECT statement, and then execute it:

       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);

       Note  that  the data type of the second parameter is not specified, so it is inferred from the context in which
       $2 is used.

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

SEE ALSO
       DEALLOCATE [deallocate(7)], EXECUTE [execute(7)]



SQL - Language Statements         2014-02-17                        PREPARE(7)