Restructure parsetree representation of DECLARE CURSOR: now it's a

utility statement (DeclareCursorStmt) with a SELECT query dangling from
it, rather than a SELECT query with a few unusual fields in it.  Add
code to determine whether a planned query can safely be run backwards.
If DECLARE CURSOR specifies SCROLL, ensure that the plan can be run
backwards by adding a Materialize plan node if it can't.  Without SCROLL,
you get an error if you try to fetch backwards from a cursor that can't
handle it.  (There is still some discussion about what the exact
behavior should be, but this is necessary infrastructure in any case.)
Along the way, make EXPLAIN DECLARE CURSOR work.

5 files changed, 55 insertions, 35 deletions

diff --git a/doc/src/sgml/ref/declare.sgml b/doc/src/sgml/ref/declare.sgml
index 77c45d90b09..5f481220071 100644
--- a/doc/src/sgml/ref/declare.sgml
+++ b/doc/src/sgml/ref/declare.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/declare.sgml,v 1.18 2002/05/18 15:44:47 petere Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/declare.sgml,v 1.19 2003/03/10 03:53:48 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -47,8 +47,7 @@ DECLARE <replaceable class="parameter">cursorname</replaceable> [ BINARY ] [ INS
       <term>BINARY</term>
       <listitem>
        <para>
-	Causes the cursor to fetch data in binary
-	rather than in text format.
+	Causes the cursor to return data in binary rather than in text format.
        </para>
       </listitem>
      </varlistentry>
@@ -70,9 +69,8 @@ DECLARE <replaceable class="parameter">cursorname</replaceable> [ BINARY ] [ INS
       <term>SCROLL</term>
       <listitem>
        <para>
-	<acronym>SQL92</acronym> keyword indicating that data may be retrieved
-	in multiple rows per FETCH operation. Since this is allowed at all times
-	by <productname>PostgreSQL</productname> this keyword has no effect.
+	Specifies that the cursor may be used to retrieve rows
+	in a nonsequential fashion (e.g., backwards).
        </para>
       </listitem>
      </varlistentry>
@@ -81,10 +79,10 @@ DECLARE <replaceable class="parameter">cursorname</replaceable> [ BINARY ] [ INS
       <term><replaceable class="parameter">query</replaceable></term>
       <listitem>
        <para>
-	An SQL query which will provide the rows to be governed by the
-	cursor.
-	Refer to the SELECT statement for further information about
-	valid arguments.
+	A <command>SELECT</> query which will provide the rows to be
+	returned by the cursor.
+	Refer to <xref linkend="sql-select" endterm="sql-select-title">
+	for further information about valid arguments.
        </para>
       </listitem>
      </varlistentry>
@@ -126,6 +124,10 @@ DECLARE <replaceable class="parameter">cursorname</replaceable> [ BINARY ] [ INS
 
     </variablelist>
    </para>
+
+   <para>
+    The BINARY, INSENSITIVE, and SCROLL keywords may appear in any order.
+   </para>
   </refsect2>
 
   <refsect2 id="R2-SQL-DECLARE-2">
@@ -193,9 +195,8 @@ ERROR:  DECLARE CURSOR may only be used in begin/end transaction blocks
   </para>
 
   <para>
-   Normal cursors return data in text format, either ASCII or another
-   encoding scheme depending on how the <productname>PostgreSQL</productname>
-   backend was built. Since
+   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. In addition,
    text formats are often larger in size than the corresponding binary format.
@@ -228,15 +229,11 @@ ERROR:  DECLARE CURSOR may only be used in begin/end transaction blocks
    representations (e.g., <quote>big-endian</quote> versus <quote>little-endian</quote>),
    you will probably not want your data returned in
    binary format.
-   However, binary cursors may be a
-   little more efficient since there is less conversion overhead in
-   the server to client data transfer.
 
    <tip>
     <para>
-     If you intend to display the data in
-     ASCII,  getting it back in ASCII will save you some
-     effort on the client side.
+     If you intend to display the data as text, retrieving it in text form
+     will save you some effort on the client side.
     </para>
    </tip>
   </para>
@@ -250,7 +247,7 @@ ERROR:  DECLARE CURSOR may only be used in begin/end transaction blocks
    </title>
 
    <para>
-    Cursors are only available in transactions. Use to
+    Cursors are only available within transactions. Use
     <xref linkend="sql-begin" endterm="sql-begin-title">,
     <xref linkend="sql-commit" endterm="sql-commit-title">
     and
@@ -259,6 +256,15 @@ ERROR:  DECLARE CURSOR may only be used in begin/end transaction blocks
    </para>
 
    <para>
+    The <literal>SCROLL</> option should be specified when defining a cursor 
+    that will be used to fetch backwards.  This is required by
+    <acronym>SQL92</acronym>.  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.
+   </para>
+
+   <para>
     In <acronym>SQL92</acronym> cursors are only available in
     embedded <acronym>SQL</acronym> (<acronym>ESQL</acronym>) applications. 
     The <productname>PostgreSQL</productname> backend
diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml
index 14639cc268c..be812bebf29 100644
--- a/doc/src/sgml/ref/explain.sgml
+++ b/doc/src/sgml/ref/explain.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/explain.sgml,v 1.23 2003/02/02 23:46:37 tgl Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/explain.sgml,v 1.24 2003/03/10 03:53:49 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -56,7 +56,8 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="PARAMETER">query</replaceabl
       <listitem>
        <para>
 	Any <command>SELECT</>, <command>INSERT</>, <command>UPDATE</>,
-	<command>DELETE</>, or <command>EXECUTE</> query.
+	<command>DELETE</>, <command>EXECUTE</>,
+	or <command>DECLARE CURSOR</> query.
        </para>
       </listitem>
      </varlistentry>
diff --git a/doc/src/sgml/ref/fetch.sgml b/doc/src/sgml/ref/fetch.sgml
index 4770545cb83..0452cf0144f 100644
--- a/doc/src/sgml/ref/fetch.sgml
+++ b/doc/src/sgml/ref/fetch.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/fetch.sgml,v 1.25 2003/02/04 11:23:58 momjian Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/fetch.sgml,v 1.26 2003/03/10 03:53:49 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -13,7 +13,7 @@ PostgreSQL documentation
    FETCH
   </refname>
   <refpurpose>
-   retrieve rows from a table using a cursor
+   retrieve rows from a query using a cursor
   </refpurpose>
  </refnamediv>
  <refsynopsisdiv>
@@ -66,7 +66,7 @@ FETCH [ FORWARD | BACKWARD | RELATIVE ] [ <replaceable class="PARAMETER">#</repl
 	  <term>RELATIVE</term>
 	  <listitem>
 	   <para>
-	    Noise word for SQL92 compatibility.
+	    Same as FORWARD; provided for SQL92 compatibility.
 	   </para>
 	  </listitem>
 	 </varlistentry>
@@ -247,13 +247,20 @@ WARNING:  FETCH/ABSOLUTE not supported, using RELATIVE
    </title>
 
    <para>
-    Note that the FORWARD, BACKWARD, and ALL keywords are
+    A cursor to be used in backwards fetching should be declared with the
+    SCROLL option.  In simple cases, <productname>PostgreSQL</productname>
+    will allow backwards fetch from cursors not declared with SCROLL, but
+    this behavior is best not relied on.
+   </para>
+
+   <para>
+    The FORWARD, BACKWARD, and ALL keywords are
     <productname>PostgreSQL</productname> extensions.
     See below for details on compatibility issues.
    </para>
 
    <para>
-    Updating data in a cursor is not supported by 
+    Updating data via a cursor is not supported by 
     <productname>PostgreSQL</productname>,
     because mapping cursor updates back to base tables is
     not generally possible, as is also the case with VIEW updates.
@@ -262,8 +269,7 @@ WARNING:  FETCH/ABSOLUTE not supported, using RELATIVE
    </para>
 
    <para>
-    Cursors may only be used inside of transactions because
-    the data that they store spans multiple user queries.
+    Cursors may only be used inside transaction blocks.
    </para>
 
    <para>
@@ -288,7 +294,7 @@ WARNING:  FETCH/ABSOLUTE not supported, using RELATIVE
   </title>
 
   <para>
-   The following examples traverses a table using a cursor.
+   The following example traverses a table using a cursor.
 
 <programlisting>
 -- Set up and use a cursor:
diff --git a/doc/src/sgml/ref/move.sgml b/doc/src/sgml/ref/move.sgml
index 69be788c35c..928faabc818 100644
--- a/doc/src/sgml/ref/move.sgml
+++ b/doc/src/sgml/ref/move.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/move.sgml,v 1.18 2003/02/04 11:23:58 momjian Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/move.sgml,v 1.19 2003/03/10 03:53:49 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -88,13 +88,11 @@ DECLARE liahona CURSOR  FOR SELECT * FROM films;
 -- Skip first 5 rows:
 MOVE FORWARD 5 IN liahona;
 <computeroutput>
-MOVE
+MOVE 5
 </computeroutput>
 -- Fetch 6th row in the cursor liahona:
 FETCH 1 IN liahona;
 <computeroutput>
-FETCH
-
  code  | title  | did | date_prod | kind   | len
 -------+--------+-----+-----------+--------+-------
  P_303 | 48 Hrs | 103 | 1982-10-22| Action | 01:37
diff --git a/doc/src/sgml/ref/prepare.sgml b/doc/src/sgml/ref/prepare.sgml
index 418bd83ace2..75401730915 100644
--- a/doc/src/sgml/ref/prepare.sgml
+++ b/doc/src/sgml/ref/prepare.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/prepare.sgml,v 1.2 2003/02/02 23:46:37 tgl Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/prepare.sgml,v 1.3 2003/03/10 03:53:49 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -54,6 +54,15 @@ PostgreSQL documentation
        </para>
       </listitem>
      </varlistentry>
+     <varlistentry>
+      <term><replaceable class="PARAMETER">query</replaceable></term>
+      <listitem>
+       <para>
+	Any <command>SELECT</>, <command>INSERT</>, <command>UPDATE</>,
+	or <command>DELETE</> query.
+       </para>
+      </listitem>
+     </varlistentry>
     </variablelist>
    </para>
   </refsect2>