Adding upstream version 15.5.upstream/15.5

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
author: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-05-04 12:17:33 +0000
committer: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-05-04 12:17:33 +0000
commit: 5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch)
tree: 739caf8c461053357daa9f162bef34516c7bf452 /doc/src/sgml/html/sql-prepare.html
parent: Initial commit. (diff)
download: postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz
postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip
1 files changed, 151 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-prepare.html b/doc/src/sgml/html/sql-prepare.html
new file mode 100644
index 0000000..dd6d3b1
--- /dev/null
+++ b/doc/src/sgml/html/sql-prepare.html
@@ -0,0 +1,151 @@
+<?xml version="1.0" encoding="UTF-8" standalone="no"?>
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>PREPARE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-notify.html" title="NOTIFY" /><link rel="next" href="sql-prepare-transaction.html" title="PREPARE TRANSACTION" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">PREPARE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-notify.html" title="NOTIFY">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-prepare-transaction.html" title="PREPARE TRANSACTION">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-PREPARE"><div class="titlepage"></div><a id="id-1.9.3.159.1" class="indexterm"></a><a id="id-1.9.3.159.2" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">PREPARE</span></h2><p>PREPARE — prepare a statement for execution</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
+PREPARE <em class="replaceable"><code>name</code></em> [ ( <em class="replaceable"><code>data_type</code></em> [, ...] ) ] AS <em class="replaceable"><code>statement</code></em>
+</pre></div><div class="refsect1" id="id-1.9.3.159.6"><h2>Description</h2><p>
+   <code class="command">PREPARE</code> creates a prepared statement. A prepared
+   statement is a server-side object that can be used to optimize
+   performance. When the <code class="command">PREPARE</code> statement is
+   executed, the specified statement is parsed, analyzed, and rewritten.
+   When an <code class="command">EXECUTE</code> 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.
+  </p><p>
+   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
+   <code class="literal">$1</code>, <code class="literal">$2</code>, 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
+   <code class="literal">unknown</code>, the type is inferred from the context
+   in which the parameter is first referenced (if possible). When executing the
+   statement, specify the actual values for these parameters in the
+   <code class="command">EXECUTE</code> statement.  Refer to <a class="xref" href="sql-execute.html" title="EXECUTE"><span class="refentrytitle">EXECUTE</span></a> for more
+   information about that.
+  </p><p>
+   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 <a class="link" href="sql-deallocate.html" title="DEALLOCATE"><code class="command">DEALLOCATE</code></a> command.
+  </p><p>
+   Prepared statements potentially 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, e.g.,
+   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.
+  </p></div><div class="refsect1" id="id-1.9.3.159.7"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>
+      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.
+     </p></dd><dt><span class="term"><em class="replaceable"><code>data_type</code></em></span></dt><dd><p>
+      The data type of a parameter to the prepared statement.  If the
+      data type of a particular parameter is unspecified or is
+      specified as <code class="literal">unknown</code>, it will be inferred
+      from the context in which the parameter is first referenced. To refer to the
+      parameters in the prepared statement itself, use
+      <code class="literal">$1</code>, <code class="literal">$2</code>, etc.
+     </p></dd><dt><span class="term"><em class="replaceable"><code>statement</code></em></span></dt><dd><p>
+      Any <code class="command">SELECT</code>, <code class="command">INSERT</code>, <code class="command">UPDATE</code>,
+      <code class="command">DELETE</code>, <code class="command">MERGE</code>, or <code class="command">VALUES</code>
+      statement.
+     </p></dd></dl></div></div><div class="refsect1" id="SQL-PREPARE-NOTES"><h2>Notes</h2><p>
+   A prepared statement can be executed with either a <em class="firstterm">generic
+   plan</em> or a <em class="firstterm">custom plan</em>.  A generic
+   plan is the same across all executions, while a custom plan is generated
+   for a specific execution using the parameter values given in that call.
+   Use of a generic plan avoids planning overhead, but in some situations
+   a custom plan will be much more efficient to execute because the planner
+   can make use of knowledge of the parameter values.  (Of course, if the
+   prepared statement has no parameters, then this is moot and a generic
+   plan is always used.)
+  </p><p>
+   By default (that is, when <a class="xref" href="runtime-config-query.html#GUC-PLAN-CACHE_MODE">plan_cache_mode</a> is set
+   to <code class="literal">auto</code>), the server will automatically choose
+   whether to use a generic or custom plan for a prepared statement that
+   has parameters.  The current rule for this is that the first five
+   executions are done with custom plans and the average estimated cost of
+   those plans is calculated.  Then a generic plan is created and its
+   estimated cost is compared to the average custom-plan cost.  Subsequent
+   executions use the generic plan if its cost is not so much higher than
+   the average custom-plan cost as to make repeated replanning seem
+   preferable.
+  </p><p>
+   This heuristic can be overridden, forcing the server to use either
+   generic or custom plans, by setting <code class="varname">plan_cache_mode</code>
+   to <code class="literal">force_generic_plan</code>
+   or <code class="literal">force_custom_plan</code> respectively.
+   This setting is primarily useful if the generic plan's cost estimate
+   is badly off for some reason, allowing it to be chosen even though
+   its actual cost is much more than that of a custom plan.
+  </p><p>
+   To examine the query plan <span class="productname">PostgreSQL</span> is using
+   for a prepared statement, use <a class="link" href="sql-explain.html" title="EXPLAIN"><code class="command">EXPLAIN</code></a>, for example
+</p><pre class="programlisting">
+EXPLAIN EXECUTE <em class="replaceable"><code>name</code></em>(<em class="replaceable"><code>parameter_values</code></em>);
+</pre><p>
+   If a generic plan is in use, it will contain parameter symbols
+   <code class="literal">$<em class="replaceable"><code>n</code></em></code>, while a custom plan
+   will have the supplied parameter values substituted into it.
+  </p><p>
+   For more information on query planning and the statistics collected
+   by <span class="productname">PostgreSQL</span> for that purpose, see
+   the <a class="xref" href="sql-analyze.html" title="ANALYZE"><span class="refentrytitle">ANALYZE</span></a>
+   documentation.
+  </p><p>
+   Although the main point of a prepared statement is to avoid repeated parse
+   analysis and planning of the statement, <span class="productname">PostgreSQL</span> 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 or their planner statistics have
+   been updated since the previous use of the prepared
+   statement.  Also, if the value of <a class="xref" href="runtime-config-client.html#GUC-SEARCH-PATH">search_path</a> changes
+   from one use to the next, the statement will be re-parsed using the new
+   <code class="varname">search_path</code>.  (This latter behavior is new as of
+   <span class="productname">PostgreSQL</span> 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 <code class="varname">search_path</code>, 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.
+  </p><p>
+   You can see all prepared statements available in the session by querying the
+   <a class="link" href="view-pg-prepared-statements.html" title="54.15. pg_prepared_statements"><code class="structname">pg_prepared_statements</code></a>
+   system view.
+  </p></div><div class="refsect1" id="SQL-PREPARE-EXAMPLES"><h2>Examples</h2><p>
+   Create a prepared statement for an <code class="command">INSERT</code>
+   statement, and then execute it:
+</p><pre class="programlisting">
+PREPARE fooplan (int, text, bool, numeric) AS
+    INSERT INTO foo VALUES($1, $2, $3, $4);
+EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
+</pre><p>
+  </p><p>
+   Create a prepared statement for a <code class="command">SELECT</code>
+   statement, and then execute it:
+</p><pre class="programlisting">
+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);
+</pre><p>
+
+   In this example, the data type of the second parameter is not specified,
+   so it is inferred from the context in which <code class="literal">$2</code> is used.
+  </p></div><div class="refsect1" id="id-1.9.3.159.10"><h2>Compatibility</h2><p>
+   The SQL standard includes a <code class="command">PREPARE</code> statement,
+   but it is only for use in embedded SQL. This version of the
+   <code class="command">PREPARE</code> statement also uses a somewhat different
+   syntax.
+  </p></div><div class="refsect1" id="id-1.9.3.159.11"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-deallocate.html" title="DEALLOCATE"><span class="refentrytitle">DEALLOCATE</span></a>, <a class="xref" href="sql-execute.html" title="EXECUTE"><span class="refentrytitle">EXECUTE</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-notify.html" title="NOTIFY">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-prepare-transaction.html" title="PREPARE TRANSACTION">Next</a></td></tr><tr><td width="40%" align="left" valign="top">NOTIFY </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> PREPARE TRANSACTION</td></tr></table></div></body></html>
+\ No newline at end of file
author	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-05-04 12:17:33 +0000
committer	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-05-04 12:17:33 +0000
commit	5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch)
tree	739caf8c461053357daa9f162bef34516c7bf452 /doc/src/sgml/html/sql-prepare.html
parent	Initial commit. (diff)
download	postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip