diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/ref/allfiles.sgml | 5 | ||||
| -rw-r--r-- | doc/src/sgml/ref/deallocate.sgml | 137 | ||||
| -rw-r--r-- | doc/src/sgml/ref/execute.sgml | 132 | ||||
| -rw-r--r-- | doc/src/sgml/ref/prepare.sgml | 209 | ||||
| -rw-r--r-- | doc/src/sgml/reference.sgml | 7 | ||||
| -rw-r--r-- | doc/src/sgml/release.sgml | 3 |
6 files changed, 489 insertions, 4 deletions
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index 79221044c21..a923e260edb 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.47 2002/08/27 03:38:27 momjian Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.48 2002/08/27 04:55:07 tgl Exp $ PostgreSQL documentation Complete list of usable sgml source files in this directory. --> @@ -71,6 +71,7 @@ Complete list of usable sgml source files in this directory. <!entity createType system "create_type.sgml"> <!entity createUser system "create_user.sgml"> <!entity createView system "create_view.sgml"> +<!entity deallocate system "deallocate.sgml"> <!entity declare system "declare.sgml"> <!entity delete system "delete.sgml"> <!entity dropAggregate system "drop_aggregate.sgml"> @@ -93,6 +94,7 @@ Complete list of usable sgml source files in this directory. <!entity dropUser system "drop_user.sgml"> <!entity dropView system "drop_view.sgml"> <!entity end system "end.sgml"> +<!entity execute system "execute.sgml"> <!entity explain system "explain.sgml"> <!entity fetch system "fetch.sgml"> <!entity grant system "grant.sgml"> @@ -102,6 +104,7 @@ Complete list of usable sgml source files in this directory. <!entity lock system "lock.sgml"> <!entity move system "move.sgml"> <!entity notify system "notify.sgml"> +<!entity prepare system "prepare.sgml"> <!entity reindex system "reindex.sgml"> <!entity reset system "reset.sgml"> <!entity revoke system "revoke.sgml"> diff --git a/doc/src/sgml/ref/deallocate.sgml b/doc/src/sgml/ref/deallocate.sgml new file mode 100644 index 00000000000..29925adc4aa --- /dev/null +++ b/doc/src/sgml/ref/deallocate.sgml @@ -0,0 +1,137 @@ +<!-- +$Header: /cvsroot/pgsql/doc/src/sgml/ref/deallocate.sgml,v 1.1 2002/08/27 04:55:07 tgl Exp $ +PostgreSQL documentation +--> + +<refentry id="SQL-DEALLOCATE"> + <refmeta> + <refentrytitle id="sql-deallocate-title">DEALLOCATE</refentrytitle> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + <refnamediv> + <refname> + DEALLOCATE + </refname> + <refpurpose> + remove a prepared query + </refpurpose> + </refnamediv> + <refsynopsisdiv> + <refsynopsisdivinfo> + <date>2002-08-12</date> + </refsynopsisdivinfo> + <synopsis> + DEALLOCATE [ PREPARE ] <replaceable class="PARAMETER">plan_name</replaceable> + </synopsis> + + <refsect2 id="R2-SQL-DEALLOCATE-1"> + <refsect2info> + <date>2002-08-12</date> + </refsect2info> + <title> + Inputs + </title> + + <para> + <variablelist> + <varlistentry> + <term>PREPARE</term> + <listitem> + <para> + This keyword is ignored. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term><replaceable class="PARAMETER">plan_name</replaceable></term> + <listitem> + <para> + The name of the prepared query to remove. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </refsect2> + <refsect2 id="R2-SQL-DEALLOCATE-2"> + <refsect2info> + <date>2002-08-12</date> + </refsect2info> + <title> + Outputs + </title> + <para> + + <variablelist> + <varlistentry> + <term><computeroutput> + <returnvalue>DEALLOCATE</returnvalue> + </computeroutput></term> + <listitem> + <para> + The prepared query was removed successfully. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </refsect2> + </refsynopsisdiv> + + <refsect1 id="R1-SQL-DEALLOCATE-1"> + <refsect1info> + <date>2002-08-12</date> + </refsect1info> + <title> + Description + </title> + + <para> + <command>DEALLOCATE</command> is used to remove a previously + prepared query. If you do not explicitly + <command>DEALLOCATE</command> a prepared query, it is removed when + the session ends. + </para> + + <para> + For more information on prepared queries, see <xref + linkend="sql-prepare" endterm="sql-prepare-title">. + </para> + </refsect1> + + <refsect1 id="R1-SQL-DEALLOCATE-2"> + <title> + Compatibility + </title> + + <refsect2 id="R2-SQL-DEALLOCATE-3"> + <refsect2info> + <date>2002-08-12</date> + </refsect2info> + <title> + SQL92 + </title> + <para> + SQL92 includes a <command>DEALLOCATE</command> statement, but it is + only for use in embedded SQL clients. + </para> + </refsect2> + </refsect1> +</refentry> + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:nil +sgml-shorttag:t +sgml-minimize-attributes:nil +sgml-always-quote-attributes:t +sgml-indent-step:1 +sgml-indent-data:t +sgml-parent-document:nil +sgml-default-dtd-file:"../reference.ced" +sgml-exposed-tags:nil +sgml-local-catalogs:"/usr/lib/sgml/catalog" +sgml-local-ecat-files:nil +End: +--> diff --git a/doc/src/sgml/ref/execute.sgml b/doc/src/sgml/ref/execute.sgml new file mode 100644 index 00000000000..67035572e74 --- /dev/null +++ b/doc/src/sgml/ref/execute.sgml @@ -0,0 +1,132 @@ +<!-- +$Header: /cvsroot/pgsql/doc/src/sgml/ref/execute.sgml,v 1.1 2002/08/27 04:55:07 tgl Exp $ +PostgreSQL documentation +--> + +<refentry id="SQL-EXECUTE"> + <refmeta> + <refentrytitle id="sql-execute-title">EXECUTE</refentrytitle> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + <refnamediv> + <refname> + EXECUTE + </refname> + <refpurpose> + execute a prepared query + </refpurpose> + </refnamediv> + <refsynopsisdiv> + <refsynopsisdivinfo> + <date>2002-08-12</date> + </refsynopsisdivinfo> + <synopsis> + EXECUTE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable class="PARAMETER">parameter</replaceable> [, ...] ) ] + </synopsis> + + <refsect2 id="R2-SQL-EXECUTE-1"> + <refsect2info> + <date>2002-08-12</date> + </refsect2info> + <title> + Inputs + </title> + + <para> + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">plan_name</replaceable></term> + <listitem> + <para> + The name of the prepared query to execute. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term><replaceable class="PARAMETER">parameter</replaceable></term> + <listitem> + <para> + The actual value of a parameter to the prepared query. + This must be an expression yielding a value of a type + compatible with + the data-type specified for this parameter position in the + <command>PREPARE</command> statement that created the prepared + query. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </refsect2> + </refsynopsisdiv> + + <refsect1 id="R1-SQL-EXECUTE-1"> + <refsect1info> + <date>2002-08-12</date> + </refsect1info> + <title> + Description + </title> + + <para> + <command>EXECUTE</command> is used to execute a previously prepared + query. Since prepared queries only exist for the duration of a + session, the prepared query must have been created by a + <command>PREPARE</command> statement executed earlier in the + current session. + </para> + + <para> + If the <command>PREPARE</command> statement that created the query + specified some parameters, a compatible set of parameters must be + passed to the <command>EXECUTE</command> statement, or else an + error is raised. Note that (unlike functions) prepared queries are + not overloaded based on the type or number of their parameters: the + name of a prepared query must be unique within a database session. + </para> + + <para> + For more information on the creation and usage of prepared queries, + see <xref linkend="sql-prepare" endterm="sql-prepare-title">. + </para> + </refsect1> + + <refsect1 id="R1-SQL-EXECUTE-2"> + <title> + Compatibility + </title> + + <refsect2 id="R2-SQL-EXECUTE-2"> + <refsect2info> + <date>2002-08-12</date> + </refsect2info> + <title> + SQL92 + </title> + <para> + SQL92 includes an <command>EXECUTE</command> statement, but it is + only for use in embedded SQL clients. The + <command>EXECUTE</command> statement implemented by + <productname>PostgreSQL</productname> also uses a somewhat + different syntax. + </para> + </refsect2> + </refsect1> +</refentry> + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:nil +sgml-shorttag:t +sgml-minimize-attributes:nil +sgml-always-quote-attributes:t +sgml-indent-step:1 +sgml-indent-data:t +sgml-parent-document:nil +sgml-default-dtd-file:"../reference.ced" +sgml-exposed-tags:nil +sgml-local-catalogs:"/usr/lib/sgml/catalog" +sgml-local-ecat-files:nil +End: +--> diff --git a/doc/src/sgml/ref/prepare.sgml b/doc/src/sgml/ref/prepare.sgml new file mode 100644 index 00000000000..d9fa86414bf --- /dev/null +++ b/doc/src/sgml/ref/prepare.sgml @@ -0,0 +1,209 @@ +<!-- +$Header: /cvsroot/pgsql/doc/src/sgml/ref/prepare.sgml,v 1.1 2002/08/27 04:55:07 tgl Exp $ +PostgreSQL documentation +--> + +<refentry id="SQL-PREPARE"> + <refmeta> + <refentrytitle id="sql-prepare-title">PREPARE</refentrytitle> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + <refnamediv> + <refname> + PREPARE + </refname> + <refpurpose> + create a prepared query + </refpurpose> + </refnamediv> + <refsynopsisdiv> + <refsynopsisdivinfo> + <date>2002-08-12</date> + </refsynopsisdivinfo> + <synopsis> + PREPARE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable class="PARAMETER">datatype</replaceable> [, ...] ) ] AS <replaceable class="PARAMETER">query</replaceable> + </synopsis> + + <refsect2 id="R2-SQL-PREPARE-1"> + <refsect2info> + <date>2002-08-12</date> + </refsect2info> + <title> + Inputs + </title> + + <para> + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">plan_name</replaceable></term> + <listitem> + <para> + An arbitrary name given to this particular prepared query. It + must be unique within a single session, and is used to execute + or remove a previously prepared query. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term><replaceable class="PARAMETER">datatype</replaceable></term> + <listitem> + <para> + The data-type of a parameter to the prepared query. + To refer to the parameters in the prepared query itself, + use <literal>$1</literal>, <literal>$2</literal>, etc. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </refsect2> + + <refsect2 id="R2-SQL-PREPARE-2"> + <refsect2info> + <date>2002-08-12</date> + </refsect2info> + <title> + Outputs + </title> + <para> + + <variablelist> + <varlistentry> + <term><computeroutput> + <returnvalue>PREPARE</returnvalue> + </computeroutput></term> + <listitem> + <para> + The query has been prepared successfully. + </para> + </listitem> + </varlistentry> + + </variablelist> + </para> + </refsect2> + </refsynopsisdiv> + + <refsect1 id="R1-SQL-PREPARE-1"> + <refsect1info> + <date>2002-08-12</date> + </refsect1info> + <title> + Description + </title> + <para> + <command>PREPARE</command> creates a prepared query. A prepared + query is a server-side object that can be used to optimize + performance. When the <command>PREPARE</command> statement is + executed, the specified query is parsed, rewritten, and + planned. When a subsequent <command>EXECUTE</command> statement is + issued, the prepared query need only be executed. Thus, the + parsing, rewriting, and planning stages are only performed once, + instead of every time the query is executed. + </para> + + <para> + Prepared queries can take parameters: values that are + substituted into the query when it is executed. To specify the + parameters to a prepared query, include a list of data-types with + the <command>PREPARE</command> statement. In the query itself, you + can refer to the parameters by position using + <literal>$1</literal>, <literal>$2</literal>, etc. When executing + the query, specify the actual values for these parameters in the + <command>EXECUTE</command> statement -- refer to <xref + linkend="sql-execute" endterm="sql-execute-title"> + for more information. + </para> + + <para> + Prepared queries are stored locally (in the current backend), and + only exist for the duration of the current database session. When + the client exits, the prepared query is forgotten, and so it must be + re-created before being used again. This also means that a single + prepared query cannot be used by multiple simultaneous database + clients; however, each client can create their own prepared query + to use. + </para> + + <para> + Prepared queries have the largest performance advantage when a + single backend is being used to execute a large number of similar + queries. The performance difference will be particularly + significant if the queries 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 query is relatively simple + to plan and rewrite but relatively expensive to execute, the + performance advantage of prepared queries will be less noticeable. + </para> + + <refsect2 id="R2-SQL-PREPARE-3"> + <refsect2info> + <date>2002-08-12</date> + </refsect2info> + <title> + Notes + </title> + + <para> + In some situations, the query plan produced by + <productname>PostgreSQL</productname> for a prepared query may be + inferior to the plan produced if the query were submitted and + executed normally. This is because when the query is planned (and + the optimizer attempts to determine the optimal query plan), the + actual values of any parameters specified in the query are + unavailable. <productname>PostgreSQL</productname> collects + statistics on the distribution of data in the table, and can use + constant values in a query to make guesses about the likely + result of executing the query. Since this data is unavailable when + planning prepared queries with parameters, the chosen plan may be + sub-optimal. + </para> + + <para> + For more information on query planning and the statistics + collected by <productname>PostgreSQL</productname> for query + optimization purposes, see the <xref linkend="sql-analyze" + endterm="sql-analyze-title"> documentation. + </para> + </refsect2> + </refsect1> + + <refsect1 id="R1-SQL-PREPARE-3"> + <title> + Compatibility + </title> + + <refsect2 id="R2-SQL-PREPARE-4"> + <refsect2info> + <date>2002-08-12</date> + </refsect2info> + <title> + SQL92 + </title> + <para> + SQL92 includes a <command>PREPARE</command> statement, but it is + only for use in embedded SQL clients. The + <command>PREPARE</command> statement implemented by + <productname>PostgreSQL</productname> also uses a somewhat + different syntax. + </para> + </refsect2> + </refsect1> +</refentry> + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:nil +sgml-shorttag:t +sgml-minimize-attributes:nil +sgml-always-quote-attributes:t +sgml-indent-step:1 +sgml-indent-data:t +sgml-parent-document:nil +sgml-default-dtd-file:"../reference.ced" +sgml-exposed-tags:nil +sgml-local-catalogs:"/usr/lib/sgml/catalog" +sgml-local-ecat-files:nil +End: +--> diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index effe495f1da..22a3c07a3e6 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -1,5 +1,5 @@ <!-- reference.sgml -$Header: /cvsroot/pgsql/doc/src/sgml/reference.sgml,v 1.36 2002/08/27 03:38:27 momjian Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/reference.sgml,v 1.37 2002/08/27 04:55:07 tgl Exp $ PostgreSQL Reference Manual --> @@ -80,6 +80,7 @@ PostgreSQL Reference Manual &createType; &createUser; &createView; + &deallocate; &declare; &delete; &dropAggregate; @@ -98,10 +99,11 @@ PostgreSQL Reference Manual &dropSequence; &dropTable; &dropTrigger; - &dropType + &dropType; &dropUser; &dropView; &end; + &execute; &explain; &fetch; &grant; @@ -111,6 +113,7 @@ PostgreSQL Reference Manual &lock; &move; ¬ify; + &prepare; &reindex; &reset; &revoke; diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml index 4be58685120..e457504ebef 100644 --- a/doc/src/sgml/release.sgml +++ b/doc/src/sgml/release.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.151 2002/08/25 14:34:24 momjian Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.152 2002/08/27 04:55:07 tgl Exp $ --> <appendix id="release"> @@ -24,6 +24,7 @@ CDATA means the content is "SGML-free", so you can write without worries about funny characters. --> <literallayout><![CDATA[ +PREPARE statement allows caching query plans for interactive statements Type OPAQUE is now deprecated in favor of pseudo-types cstring, trigger, etc Files larger than 2 GB are now supported (if supported by the operating system) SERIAL no longer implies UNIQUE; specify explicitly if index is wanted |