diff options
Diffstat (limited to 'doc/src/sgml/ref/fetch.sgml')
| -rw-r--r-- | doc/src/sgml/ref/fetch.sgml | 690 |
1 files changed, 335 insertions, 355 deletions
diff --git a/doc/src/sgml/ref/fetch.sgml b/doc/src/sgml/ref/fetch.sgml index 839732c669d..bd384fabede 100644 --- a/doc/src/sgml/ref/fetch.sgml +++ b/doc/src/sgml/ref/fetch.sgml @@ -1,300 +1,287 @@ -<REFENTRY ID="SQL-FETCH"> -<REFMETA> -<REFENTRYTITLE> -FETCH -</REFENTRYTITLE> -<REFMISCINFO>SQL - Language Statements</REFMISCINFO> -</REFMETA> -<REFNAMEDIV> -<REFNAME> -FETCH -</REFNAME> -<REFPURPOSE> -Gets rows using a cursor -</REFPURPOSE> -</refnamediv> -<REFSYNOPSISDIV> -<REFSYNOPSISDIVINFO> -<DATE>1998-09-01</DATE> -</REFSYNOPSISDIVINFO> -<SYNOPSIS> -FETCH [ <REPLACEABLE CLASS="PARAMETER">selector</REPLACEABLE> ] [ <REPLACEABLE CLASS="PARAMETER">count</REPLACEABLE> ] - { IN | FROM } <REPLACEABLE CLASS="PARAMETER">cursor</REPLACEABLE> -FETCH [ RELATIVE ] [ { [ <REPLACEABLE CLASS="PARAMETER">#</REPLACEABLE> | ALL | NEXT | PRIOR ] } ] - FROM ] <REPLACEABLE CLASS="PARAMETER">cursor</REPLACEABLE> -</SYNOPSIS> - -<REFSECT2 ID="R2-SQL-FETCH-1"> -<REFSECT2INFO> -<DATE>1998-09-01</DATE> -</REFSECT2INFO> -<TITLE> -Inputs -</TITLE> -<PARA> - -<VARIABLELIST> -<VARLISTENTRY> -<TERM> -<REPLACEABLE CLASS="PARAMETER">selector</REPLACEABLE> -</TERM> -<LISTITEM> -<PARA> -<REPLACEABLE CLASS="PARAMETER">selector</REPLACEABLE> -defines the fetch direction. It can be one - the following: -</para> -</listitem> -</varlistentry> -</variablelist> -<VARIABLELIST> -<VARLISTENTRY> -<TERM> -FORWARD -</TERM> -<LISTITEM> -<PARA> -fetch next row(s). This is the default - if <REPLACEABLE CLASS="PARAMETER">selector</REPLACEABLE> is omitted. -</para> -</listitem> -</varlistentry> -<VARLISTENTRY> -<TERM> -BACKWARD -</TERM> -<LISTITEM> -<PARA> -fetch previous row(s). -</para> -</listitem> -</varlistentry> -<VARLISTENTRY> -<TERM> -RELATIVE -</TERM> -<LISTITEM> -<PARA> -Noise word for SQL92 compatibility. -</para> -</listitem> -</varlistentry> - - -<VARLISTENTRY> -<TERM> -<REPLACEABLE CLASS="PARAMETER">count</REPLACEABLE> -</TERM> -<LISTITEM> -<PARA> -<REPLACEABLE CLASS="PARAMETER">count</REPLACEABLE> -determines how many rows to fetch. It can be one of the following: -</para> -</listitem> -</varlistentry> - - -<VARLISTENTRY> -<TERM> -<REPLACEABLE CLASS="PARAMETER">#</REPLACEABLE> -</TERM> -<LISTITEM> -<PARA> -A signed integer that specify how many rows to fetch. -Note that a negative integer is equivalent to changing the sense of -FORWARD and BACKWARD. -</para> -</listitem> -</varlistentry> - -<VARLISTENTRY> -<TERM> -ALL -</TERM> -<LISTITEM> -<PARA> -Retrieve all remaining rows. -</para> -</listitem> -</varlistentry> - -<VARLISTENTRY> -<TERM> -NEXT -</TERM> -<LISTITEM> -<PARA> -Equivalent to specifying a count of <command>1</command>. -</para> -</listitem> -</varlistentry> - -<VARLISTENTRY> -<TERM> -PRIOR -</TERM> -<LISTITEM> -<PARA> -Equivalent to specifying a count of <command>-1</command>. -</para> -</listitem> -</varlistentry> - -<VARLISTENTRY> -<TERM> -<REPLACEABLE CLASS="PARAMETER">cursor</REPLACEABLE> -</TERM> -<LISTITEM> -<PARA> -An open cursor's name. -</para> -</listitem> -</varlistentry> -</variablelist> -</para> -</REFSECT2> - -<REFSECT2 ID="R2-SQL-FETCH-2"> -<REFSECT2INFO> -<DATE>1998-04-15</DATE> -</REFSECT2INFO> -<TITLE> -Outputs -</TITLE> -<PARA> -FETCH returns the results of the query defined by the specified cursor. -The following messages will be returned if the query fails: -</para> - -<VARIABLELIST> -<VARLISTENTRY> -<TERM> -NOTICE: PerformPortalFetch: portal "<REPLACEABLE CLASS="PARAMETER">cursor</REPLACEABLE>" not found -</TERM> -<LISTITEM> -<PARA> -If <REPLACEABLE CLASS="PARAMETER">cursor</REPLACEABLE> - is not previously declared. -The cursor must be declared within a transaction block. -</para> -</listitem> -</varlistentry> - -<VARLISTENTRY> -<TERM> +<refentry id="SQL-FETCH"> + <refmeta> + <refentrytitle> + FETCH + </refentrytitle> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + <refnamediv> + <refname> + FETCH + </refname> + <refpurpose> + Gets rows using a cursor + </refpurpose> + </refnamediv> + <refsynopsisdiv> + <refsynopsisdivinfo> + <date>1998-09-01</date> + </refsynopsisdivinfo> + <synopsis> +FETCH [ <replaceable class="PARAMETER">selector</replaceable> ] [ <replaceable class="PARAMETER">count</replaceable> ] { IN | FROM } <replaceable class="PARAMETER">cursor</replaceable> +FETCH [ RELATIVE ] [ { [ <replaceable class="PARAMETER">#</replaceable> | ALL | NEXT | PRIOR ] } ] FROM ] <replaceable class="PARAMETER">cursor</replaceable> + </synopsis> + + <refsect2 id="R2-SQL-FETCH-1"> + <refsect2info> + <date>1998-09-01</date> + </refsect2info> + <title> + Inputs + </title> + <para> + + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">selector</replaceable></term> + <listitem> + <para> + <replaceable class="PARAMETER">selector</replaceable> + defines the fetch direction. It can be one + the following: + + <variablelist> + <varlistentry> + <term>FORWARD</term> + <listitem> + <para> + fetch next row(s). This is the default + if <replaceable class="PARAMETER">selector</replaceable> is omitted. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term>BACKWARD</term> + <listitem> + <para> + fetch previous row(s). + </para> + </listitem> + </varlistentry> + <varlistentry> + <term>RELATIVE</term> + <listitem> + <para> + Noise word for SQL92 compatibility. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">count</replaceable></term> + <listitem> + <para> + <replaceable class="PARAMETER">count</replaceable> + determines how many rows to fetch. It can be one of the following: + + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">#</replaceable></term> + <listitem> + <para> + A signed integer that specify how many rows to fetch. + Note that a negative integer is equivalent to changing the sense of + FORWARD and BACKWARD. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + ALL + </term> + <listitem> + <para> + Retrieve all remaining rows. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + NEXT + </term> + <listitem> + <para> + Equivalent to specifying a count of <command>1</command>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + PRIOR + </term> + <listitem> + <para> + Equivalent to specifying a count of <command>-1</command>. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">cursor</replaceable></term> + <listitem> + <para> + An open cursor's name. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </refsect2> + + <refsect2 id="R2-SQL-FETCH-2"> + <refsect2info> + <date>1998-04-15</date> + </refsect2info> + <title> + Outputs + </title> + <para> + <command>FETCH</command> returns the results of the query defined by the specified cursor. + The following messages will be returned if the query fails: + + <variablelist> + <varlistentry> + <term><computeroutput> +NOTICE: PerformPortalFetch: portal "<replaceable class="PARAMETER">cursor</replaceable>" not found + </computeroutput></term> + <listitem> + <para> + If <replaceable class="PARAMETER">cursor</replaceable> + is not previously declared. + The cursor must be declared within a transaction block. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><computeroutput> NOTICE: FETCH/ABSOLUTE not supported, using RELATIVE -</TERM> -<LISTITEM> -<PARA> -<productname>Postgres</productname> does not support absolute -positioning of cursors. -</para> -</listitem> -</varlistentry> - -<VARLISTENTRY> -<TERM> + </computeroutput></term> + <listitem> + <para> + <productname>Postgres</productname> does not support absolute + positioning of cursors. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><computeroutput> ERROR: FETCH/RELATIVE at current position is not supported -</TERM> -<LISTITEM> -<PARA> -<acronym>SQL92</acronym> allows one to repetatively retrieve the cursor -at its "current position" using the syntax -<programlisting> -FETCH RELATIVE 0 FROM <REPLACEABLE CLASS="PARAMETER">cursor</REPLACEABLE> -</programlisting> - -<productname>Postgres</productname> does not currently support -this notion; in fact the value zero is reserved to indicate that -all rows should be retrieved and is equivalent to specifying the ALL keyword. -If the RELATIVE keyword has been used, the <productname>Postgres</productname> -assumes that the user intended <acronym>SQL92</acronym> behavior -and returns this error message. -</para> -</listitem> -</varlistentry> - -</variablelist> - -</REFSECT2> -</REFSYNOPSISDIV> - -<REFSECT1 ID="R1-SQL-FETCH-1"> -<REFSECT1INFO> -<DATE>1998-04-15</DATE> -</REFSECT1INFO> -<TITLE> -Description -</TITLE> -<PARA> + </computeroutput></term> + <listitem> + <para> + <acronym>SQL92</acronym> allows one to repetatively retrieve the cursor + at its "current position" using the syntax + <synopsis> +FETCH RELATIVE 0 FROM <replaceable class="PARAMETER">cursor</replaceable> + </synopsis> + </para> + + <para> + <productname>Postgres</productname> does not currently support + this notion; in fact the value zero is reserved to indicate that + all rows should be retrieved and is equivalent to specifying the ALL keyword. + If the RELATIVE keyword has been used, the <productname>Postgres</productname> + assumes that the user intended <acronym>SQL92</acronym> behavior + and returns this error message. + </para> + </listitem> + </varlistentry> + + </variablelist> + </para> + </refsect2> + </refsynopsisdiv> + + <refsect1 id="R1-SQL-FETCH-1"> + <refsect1info> + <date>1998-04-15</date> + </refsect1info> + <title> + Description + </title> + <para> FETCH allows a user to retrieve rows using a cursor. The number of rows retrieved is specified by - <REPLACEABLE CLASS="PARAMETER">#</REPLACEABLE>. + <replaceable class="PARAMETER">#</replaceable>. If the number of rows remaining in the cursor is less - than <REPLACEABLE CLASS="PARAMETER">#</REPLACEABLE>, - then only those available are fetched. + than <replaceable class="PARAMETER">#</replaceable>, + then only those available are fetched. Substituting the keyword ALL in place of a number will cause all remaining rows in the cursor to be retrieved. Instances may be fetched in both FORWARD and BACKWARD directions. The default direction is FORWARD. -<tip> -<para> -Negative numbers are now allowed to be specified for the -row count. A negative number is equivalent to reversing -the sense of the FORWARD and BACKWARD keywords. For example, -<command>FORWARD -1</command> is the same as <command>BACKWARD 1</command>. -</para> -</tip> -</para> -<para> -Note that the FORWARD and BACKWARD keywords are - <productname>Postgres</productname> extensions. -The <acronym>SQL92</acronym> syntax is also supported, specified -in the second form of the command. See below for details -on compatibility issues. -</para> -<para> + <tip> + <para> + Negative numbers are now allowed to be specified for the + row count. A negative number is equivalent to reversing + the sense of the FORWARD and BACKWARD keywords. For example, + <command>FORWARD -1</command> is the same as <command>BACKWARD 1</command>. + </para> + </tip> + </para> + <para> + Note that the FORWARD and BACKWARD keywords are + <productname>Postgres</productname> extensions. + The <acronym>SQL92</acronym> syntax is also supported, specified + in the second form of the command. See below for details + on compatibility issues. + </para> + <para> Once all rows are fetched, every other fetch access returns no rows. -</para> + </para> -<para> + <para> Updating data in a cursor is not supported by -<productname>Postgres</productname>, + <productname>Postgres</productname>, because mapping cursor updates back to base tables is -not generally possible, as is also the case with VIEW updates. - Consequently, + not generally possible, as is also the case with VIEW updates. + Consequently, users must issue explicit UPDATE commands to replace data. </para> -<para> + <para> Cursors may only be used inside of transactions because the data that they store spans multiple user queries. -</para> - -<REFSECT2 ID="R2-SQL-FETCH-3"> -<REFSECT2INFO> -<DATE>1998-04-15</DATE> -</REFSECT2INFO> -<TITLE> -Notes -</TITLE> -<PARA> - Refer to MOVE statements to change cursor position. - Refer to DECLARE statements to declare a cursor. - Refer to BEGIN WORK, COMMIT WORK, ROLLBACK WORK statements - for further information about transactions. -</para> -</REFSECT2> -</refsect1> - -<REFSECT1 ID="R1-SQL-FETCH-2"> -<TITLE> -Usage -</TITLE> -<PARA> -<ProgramListing> + </para> + + <refsect2 id="R2-SQL-FETCH-3"> + <refsect2info> + <date>1998-04-15</date> + </refsect2info> + <title> + Notes + </title> + <para> + Use <command>MOVE</command> to change cursor position. + <command>DECLARE</command> will define a cursor. + Refer to <command>BEGIN</command>, <command>COMMIT</command>, and + <command>ROLLBACK</command> + for further information about transactions. + </para> + </refsect2> + </refsect1> + + <refsect1 id="R1-SQL-FETCH-2"> + <title> + Usage + </title> + <para> + <programlisting> --set up and use a cursor: -- BEGIN WORK; @@ -326,82 +313,75 @@ Usage -- CLOSE liahona; COMMIT WORK; -</ProgramListing> -</para> -</REFSECT1> - -<REFSECT1 ID="R1-SQL-FETCH-3"> -<TITLE> -Compatibility -</TITLE> -<PARA> -The non-embedded use of cursors is a <productname>Postgres</productname> -extension. The syntax and usage of cursors is being compared -against the embedded form of cursors defined in <acronym>SQL92</acronym>. -</PARA> - -<REFSECT2 ID="R2-SQL-FETCH-4"> -<REFSECT2INFO> -<DATE>1998-09-01</DATE> -</REFSECT2INFO> -<TITLE> -SQL92 -</TITLE> -<PARA> -<acronym>SQL92</acronym> allows absolute positioning of the cursor for -FETCH, and allows placing the results into explicit variables. - -<synopsis> -FETCH ABSOLUTE <REPLACEABLE CLASS="PARAMETER">#</REPLACEABLE> - FROM <REPLACEABLE CLASS="PARAMETER">cursor</REPLACEABLE> - INTO :<REPLACEABLE CLASS="PARAMETER">variable</REPLACEABLE> [, ...] -</synopsis> - -<VARIABLELIST> -<VARLISTENTRY> -<TERM> -ABSOLUTE -</TERM> -<LISTITEM> -<PARA> -The cursor should be positioned to the specified absolute -row number. All row numbers in <productname>Postgres</productname> -are relative numbers so this capability is not supported. -</para> -</listitem> -</varlistentry> -<VARLISTENTRY> -<TERM> -:<REPLACEABLE CLASS="PARAMETER">variable</REPLACEABLE> -</TERM> -<LISTITEM> -<PARA> -Target host variable(s). -</para> -</listitem> -</varlistentry> -</variablelist> -</para> -</refsect2> -</refsect1> -</REFENTRY> - -<!-- -<REPLACEABLE CLASS="PARAMETER"> -</REPLACEABLE> -<ReturnValue></ReturnValue> -<PARA> -</PARA> -<VARIABLELIST> -<VARLISTENTRY> -<TERM>• -</TERM> -<LISTITEM> -<PARA> -</PARA> -</LISTITEM> -</VARLISTENTRY> -</VARIABLELIST> -<PARA> -</PARA> + </programlisting> + </para> + </refsect1> + + <refsect1 id="R1-SQL-FETCH-3"> + <title> + Compatibility + </title> + <para> + The non-embedded use of cursors is a <productname>Postgres</productname> + extension. The syntax and usage of cursors is being compared + against the embedded form of cursors defined in <acronym>SQL92</acronym>. + </para> + + <refsect2 id="R2-SQL-FETCH-4"> + <refsect2info> + <date>1998-09-01</date> + </refsect2info> + <title> + SQL92 + </title> + <para> + <acronym>SQL92</acronym> allows absolute positioning of the cursor for + FETCH, and allows placing the results into explicit variables. + + <synopsis> +FETCH ABSOLUTE <replaceable class="PARAMETER">#</replaceable> + FROM <replaceable class="PARAMETER">cursor</replaceable> + INTO :<replaceable class="PARAMETER">variable</replaceable> [, ...] + </synopsis> + + <variablelist> + <varlistentry> + <term>ABSOLUTE</term> + <listitem> + <para> + The cursor should be positioned to the specified absolute + row number. All row numbers in <productname>Postgres</productname> + are relative numbers so this capability is not supported. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term>:<replaceable class="PARAMETER">variable</replaceable></term> + <listitem> + <para> + Target host variable(s). + </para> + </listitem> + </varlistentry> + </variablelist> + </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: --> |