<!--
doc/src/sgml/ref/declare.sgml
PostgreSQL documentation
-->

<refentry id="sql-declare">
 <indexterm zone="sql-declare">
  <primary>DECLARE</primary>
 </indexterm>

 <indexterm zone="sql-declare">
  <primary>cursor</primary>
  <secondary>DECLARE</secondary>
 </indexterm>

 <indexterm>
  <primary>portal</primary>
  <secondary>DECLARE</secondary>
 </indexterm>

 <refmeta>
  <refentrytitle>DECLARE</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>DECLARE</refname>
  <refpurpose>define a cursor</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ]
    CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">query</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.
   After the cursor is created, rows are fetched from it using
   <link linkend="sql-fetch"><command>FETCH</command></link>.
  </para>

  <note>
   <para>
    This page describes usage of cursors at the SQL command level.
    If you are trying to use cursors inside a <application>PL/pgSQL</application>
    function, the rules are different &mdash;
    see <xref linkend="plpgsql-cursors"/>.
   </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.
      This must be different from any other active cursor name in the
      session.
     </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>ASENSITIVE</literal></term>
    <term><literal>INSENSITIVE</literal></term>
    <listitem>
     <para>
      Cursor sensitivity determines whether changes to the data underlying the
      cursor, done in the same transaction, after the cursor has been
      declared, are visible in the cursor.  <literal>INSENSITIVE</literal>
      means they are not visible, <literal>ASENSITIVE</literal> means the
      behavior is implementation-dependent.  A third behavior,
      <literal>SENSITIVE</literal>, meaning that such changes are visible in
      the cursor, is not available in <productname>PostgreSQL</productname>.
      In <productname>PostgreSQL</productname>, all cursors are insensitive;
      so these key words have no effect and are only accepted for
      compatibility with the SQL standard.
     </para>

     <para>
      Specifying <literal>INSENSITIVE</literal> together with <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>