Home Explore Blog CI



postgresql

2nd chunk of `doc/src/sgml/ref/declare.sgml`
740edbc0eab627a3f3179f519e16ce0adb4ff1d499c885790000000100000fba
 <literal>FOR
      UPDATE</literal> or <literal>FOR SHARE</literal> is an error.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>SCROLL</literal></term>
    <term><literal>NO SCROLL</literal></term>
    <listitem>
     <para><literal>SCROLL</literal> specifies that the cursor can 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> might 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"/>
      below 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 can
      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 <link linkend="sql-select"><command>SELECT</command></link> or
      <link linkend="sql-values"><command>VALUES</command></link> command
      which will provide the rows to be returned by the cursor.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>

  <para>
   The key words <literal>ASENSITIVE</literal>, <literal>BINARY</literal>,
   <literal>INSENSITIVE</literal>, and <literal>SCROLL</literal> can
   appear in any order.
  </para>
 </refsect1>

 <refsect1 id="sql-declare-notes" xreflabel="Notes">
  <title>Notes</title>

  <para>
   Normal cursors return data in text format, the same as a
   <command>SELECT</command> would produce.  The <literal>BINARY</literal> option
   specifies that the cursor should return data in binary format.
   This reduces conversion effort for both the server and client,
   at the cost of more programmer effort to deal with platform-dependent
   binary data formats.
   As an example, if a query returns a value of one from an integer column,
   you would get a string of <literal>1</literal> 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</quote> protocol
    to issue a <command>FETCH</command> 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 &mdash; any cursor can be treated as either text or binary.
   </para>
  </note>

   <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</command> 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>

Title: DECLARE Parameters and Notes
Summary
This section details the parameters for the DECLARE command, including WITH/WITHOUT HOLD to specify whether the cursor can be used after the transaction commits, and the query that provides rows for the cursor. It also discusses the BINARY option, which returns data in binary format, and notes that binary cursors should be used carefully due to potential compatibility issues. The section also clarifies that the choice of text or binary data retrieval can be overridden by the Bind protocol message in extended query protocol.