| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/declare.sgml,v 1.39 2006/09/18 19:54:01 tgl Exp $ |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="SQL-DECLARE"> |
| <refmeta> |
| <refentrytitle id="SQL-DECLARE-TITLE">DECLARE</refentrytitle> |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>DECLARE</refname> |
| <refpurpose>define a cursor</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-declare"> |
| <primary>DECLARE</primary> |
| </indexterm> |
| |
| <indexterm zone="sql-declare"> |
| <primary>cursor</primary> |
| <secondary>DECLARE</secondary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] |
| CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">query</replaceable> |
| [ FOR { READ ONLY | UPDATE [ OF <replaceable class="parameter">column</replaceable> [, ...] ] } ] |
| </synopsis> |
| </refsynopsisdiv> |
| |
| <refsect1> |
| <title>Description</title> |
| |
| <para> |
| <command>DECLARE</command> allows a user to create cursors, which |
| can be used to retrieve |
| a small number of rows at a time out of a larger query. Cursors can |
| return data either in text or in binary format using |
| <xref linkend="sql-fetch" endterm="sql-fetch-title">. |
| </para> |
| |
| <para> |
| Normal cursors return data in text format, the same as a |
| <command>SELECT</> would produce. Since data is stored natively in |
| binary format, the system must do a conversion to produce the text |
| format. Once the information comes back in text form, the client |
| application may need to convert it to a binary format to manipulate |
| it. In addition, data in the text format is often larger in size |
| than in the binary format. Binary cursors return the data in a |
| binary representation that may be more easily manipulated. |
| Nevertheless, if you intend to display the data as text anyway, |
| retrieving it in text form will |
| save you some effort on the client side. |
| </para> |
| |
| <para> |
| As an example, if a query returns a value of one from an integer column, |
| you would get a string of <literal>1</> with a default cursor |
| whereas with a binary cursor you would get |
| a 4-byte field containing the internal representation of the value |
| (in big-endian byte order). |
| </para> |
| |
| <para> |
| Binary cursors should be used carefully. Many applications, |
| including <application>psql</application>, are not prepared to |
| handle binary cursors and expect data to come back in the text |
| format. |
| </para> |
| |
| <note> |
| <para> |
| When the client application uses the <quote>extended query</> protocol |
| to issue a <command>FETCH</> command, the Bind protocol message |
| specifies whether data is to be retrieved in text or binary format. |
| This choice overrides the way that the cursor is defined. The concept |
| of a binary cursor as such is thus obsolete when using extended query |
| protocol — any cursor can be treated as either text or binary. |
| </para> |
| </note> |
| </refsect1> |
| |
| <refsect1> |
| <title>Parameters</title> |
| |
| <variablelist> |
| <varlistentry> |
| <term><replaceable class="parameter">name</replaceable></term> |
| <listitem> |
| <para> |
| The name of the cursor to be created. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>BINARY</literal></term> |
| <listitem> |
| <para> |
| Causes the cursor to return data in binary rather than in text format. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>INSENSITIVE</literal></term> |
| <listitem> |
| <para> |
| Indicates that data retrieved from the cursor should be |
| unaffected by updates to the tables underlying the cursor while |
| the cursor exists. In <productname>PostgreSQL</productname>, |
| all cursors are insensitive; this key word currently has no |
| effect and is present for compatibility with the SQL standard. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>SCROLL</literal></term> |
| <term><literal>NO SCROLL</literal></term> |
| <listitem> |
| <para> |
| <literal>SCROLL</literal> specifies that the cursor may be used |
| to retrieve rows in a nonsequential fashion (e.g., |
| backward). Depending upon the complexity of the query's |
| execution plan, specifying <literal>SCROLL</literal> may impose |
| a performance penalty on the query's execution time. |
| <literal>NO SCROLL</literal> specifies that the cursor cannot be |
| used to retrieve rows in a nonsequential fashion. The default is to |
| allow scrolling in some cases; this is not the same as specifying |
| <literal>SCROLL</literal>. See <xref linkend="sql-declare-notes" |
| endterm="sql-declare-notes-title"> for details. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>WITH HOLD</literal></term> |
| <term><literal>WITHOUT HOLD</literal></term> |
| <listitem> |
| <para> |
| <literal>WITH HOLD</literal> specifies that the cursor may |
| continue to be used after the transaction that created it |
| successfully commits. <literal>WITHOUT HOLD</literal> specifies |
| that the cursor cannot be used outside of the transaction that |
| created it. If neither <literal>WITHOUT HOLD</literal> nor |
| <literal>WITH HOLD</literal> is specified, <literal>WITHOUT |
| HOLD</literal> is the default. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">query</replaceable></term> |
| <listitem> |
| <para> |
| A <xref linkend="sql-select" endterm="sql-select-title"> or |
| <xref linkend="sql-values" endterm="sql-values-title"> command |
| which will provide the rows to be returned by the cursor. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>FOR READ ONLY</literal></term> |
| <term><literal>FOR UPDATE</literal></term> |
| <listitem> |
| <para> |
| <literal>FOR READ ONLY</literal> indicates that the cursor will |
| be used in a read-only mode. <literal>FOR UPDATE</literal> |
| indicates that the cursor will be used to update tables. Since |
| cursor updates are not currently supported in |
| <productname>PostgreSQL</productname>, specifying <literal>FOR |
| UPDATE</literal> will cause an error message and specifying |
| <literal>FOR READ ONLY</literal> has no effect. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">column</replaceable></term> |
| <listitem> |
| <para> |
| Column(s) to be updated by the cursor. Since cursor updates are |
| not currently supported in |
| <productname>PostgreSQL</productname>, the <literal>FOR |
| UPDATE</literal> clause provokes an error message. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| |
| <para> |
| The key words <literal>BINARY</literal>, |
| <literal>INSENSITIVE</literal>, and <literal>SCROLL</literal> may |
| appear in any order. |
| </para> |
| </refsect1> |
| |
| <refsect1 id="sql-declare-notes"> |
| <title id="sql-declare-notes-title">Notes</title> |
| |
| <para> |
| Unless <literal>WITH HOLD</literal> is specified, the cursor |
| created by this command can only be used within the current |
| transaction. Thus, <command>DECLARE</> without <literal>WITH |
| HOLD</literal> is useless outside a transaction block: the cursor would |
| survive only to the completion of the statement. Therefore |
| <productname>PostgreSQL</productname> reports an error if this |
| command is used outside a transaction block. |
| Use |
| <xref linkend="sql-begin" endterm="sql-begin-title">, |
| <xref linkend="sql-commit" endterm="sql-commit-title"> |
| and |
| <xref linkend="sql-rollback" endterm="sql-rollback-title"> |
| to define a transaction block. |
| </para> |
| |
| <para> |
| If <literal>WITH HOLD</literal> is specified and the transaction |
| that created the cursor successfully commits, the cursor can |
| continue to be accessed by subsequent transactions in the same |
| session. (But if the creating transaction is aborted, the cursor |
| is removed.) A cursor created with <literal>WITH HOLD</literal> |
| is closed when an explicit <command>CLOSE</command> command is |
| issued on it, or the session ends. In the current implementation, |
| the rows represented by a held cursor are copied into a temporary |
| file or memory area so that they remain available for subsequent |
| transactions. |
| </para> |
| |
| <para> |
| The <literal>SCROLL</> option should be specified when defining a |
| cursor that will be used to fetch backwards. This is required by |
| the SQL standard. However, for compatibility with earlier |
| versions, <productname>PostgreSQL</productname> will allow |
| backward fetches without <literal>SCROLL</>, if the cursor's query |
| plan is simple enough that no extra overhead is needed to support |
| it. However, application developers are advised not to rely on |
| using backward fetches from a cursor that has not been created |
| with <literal>SCROLL</literal>. If <literal>NO SCROLL</> is |
| specified, then backward fetches are disallowed in any case. |
| </para> |
| |
| <para> |
| The SQL standard only makes provisions for cursors in embedded |
| <acronym>SQL</acronym>. The <productname>PostgreSQL</productname> |
| server does not implement an <command>OPEN</command> statement for |
| cursors; a cursor is considered to be open when it is declared. |
| However, <application>ECPG</application>, the embedded SQL |
| preprocessor for <productname>PostgreSQL</productname>, supports |
| the standard SQL cursor conventions, including those involving |
| <command>DECLARE</command> and <command>OPEN</command> statements. |
| </para> |
| |
| <para> |
| You can see all available cursors by querying the <link |
| linkend="view-pg-cursors"><structname>pg_cursors</structname></link> |
| system view. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Examples</title> |
| |
| <para> |
| To declare a cursor: |
| <programlisting> |
| DECLARE liahona CURSOR FOR SELECT * FROM films; |
| </programlisting> |
| See <xref linkend="sql-fetch" endterm="sql-fetch-title"> for more |
| examples of cursor usage. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Compatibility</title> |
| |
| <para> |
| The SQL standard allows cursors only in embedded |
| <acronym>SQL</acronym> and in modules. <productname>PostgreSQL</> |
| permits cursors to be used interactively. |
| </para> |
| |
| <para> |
| The SQL standard allows cursors to update table data. All |
| <productname>PostgreSQL</> cursors are read only. |
| </para> |
| |
| <para> |
| Binary cursors are a <productname>PostgreSQL</productname> |
| extension. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>See Also</title> |
| |
| <simplelist type="inline"> |
| <member><xref linkend="sql-close" endterm="sql-close-title"></member> |
| <member><xref linkend="sql-fetch" endterm="sql-fetch-title"></member> |
| <member><xref linkend="sql-move" endterm="sql-move-title"></member> |
| </simplelist> |
| </refsect1> |
| </refentry> |