Free Knowledge Resources


Linux Manual Pages


Free Software * Books

Source Code

Free Media

Linux

PREPARE(7)


NAME

   PREPARE - prepare a statement for execution

SYNOPSIS

   PREPARE name [ ( data_type [, ...] ) ] 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,
   analyzed, and rewritten. When an EXECUTE command is subsequently
   issued, the prepared statement is planned and executed. This division
   of labor avoids repetitive parse analysis work, while allowing the
   execution plan to depend on the specific parameter values supplied.

   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
   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. Prepared statements can be manually cleaned up using
   the 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 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

   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.

   data_type
       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

   If a prepared statement is executed enough times, the server may
   eventually decide to save and re-use a generic plan rather than
   re-planning each time. This will occur immediately if the prepared
   statement has no parameters; otherwise it occurs only if the generic
   plan appears to be not much more expensive than a plan that depends on
   specific parameter values. Typically, a generic plan will be selected
   only if the query's performance is estimated to be fairly insensitive
   to the specific parameter values supplied.

   To examine the query plan PostgreSQL is using for a prepared statement,
   use EXPLAIN(7). If a generic plan is in use, it will contain parameter
   symbols $n, while a custom plan will have the current actual parameter
   values substituted into it.

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

   Although the main point of a prepared statement is to avoid repeated
   parse analysis and planning of the statement, PostgreSQL will force
   re-analysis and re-planning of the statement before using it whenever
   database objects used in the statement have undergone definitional
   (DDL) changes since the previous use of the prepared statement. Also,
   if the value of search_path changes from one use to the next, the
   statement will be re-parsed using the new search_path. (This latter
   behavior is new as of PostgreSQL 9.3.) These rules make use of a
   prepared statement semantically almost equivalent to re-submitting the
   same query text over and over, but with a performance benefit if no
   object definitions are changed, especially if the best plan remains the
   same across uses. An example of a case where the semantic equivalence
   is not perfect is that if the statement refers to a table by an
   unqualified name, and then a new table of the same name is created in a
   schema appearing earlier in the search_path, no automatic re-parse will
   occur since no object used in the statement changed. However, if some
   other change forces a re-parse, the new table will be referenced in
   subsequent uses.

   You can see all prepared statements available in the 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 PREPARE statement also uses a
   somewhat different syntax.

SEE ALSO

   DEALLOCATE(7), EXECUTE(7)



Opportunity


Personal Opportunity - Free software gives you access to billions of dollars of software at no cost. Use this software for your business, personal use or to develop a profitable skill. Access to source code provides access to a level of capabilities/information that companies protect though copyrights. Open source is a core component of the Internet and it is available to you. Leverage the billions of dollars in resources and capabilities to build a career, establish a business or change the world. The potential is endless for those who understand the opportunity.

Business Opportunity - Goldman Sachs, IBM and countless large corporations are leveraging open source to reduce costs, develop products and increase their bottom lines. Learn what these companies know about open source and how open source can give you the advantage.



Free Software


Free Software provides computer programs and capabilities at no cost but more importantly, it provides the freedom to run, edit, contribute to, and share the software. The importance of free software is a matter of access, not price. Software at no cost is a benefit but ownership rights to the software and source code is far more significant.

Free Office Software - The Libre Office suite provides top desktop productivity tools for free. This includes, a word processor, spreadsheet, presentation engine, drawing and flowcharting, database and math applications. Libre Office is available for Linux or Windows.



Free Books


The Free Books Library is a collection of thousands of the most popular public domain books in an online readable format. The collection includes great classical literature and more recent works where the U.S. copyright has expired. These books are yours to read and use without restrictions.

Source Code - Want to change a program or know how it works? Open Source provides the source code for its programs so that anyone can use, modify or learn how to write those programs themselves. Visit the GNU source code repositories to download the source.



Education


Study at Harvard, Stanford or MIT - Open edX provides free online courses from Harvard, MIT, Columbia, UC Berkeley and other top Universities. Hundreds of courses for almost all major subjects and course levels. Open edx also offers some paid courses and selected certifications.

Linux Manual Pages - A man or manual page is a form of software documentation found on Linux/Unix operating systems. Topics covered include computer programs (including library and system calls), formal standards and conventions, and even abstract concepts.


birds banner