Re: [HACKERS] MOVE strangeness - Mailing list pgsql-patches

Bruce Momjian wrote:
> OK, patch attached.  The patch also makes the cursor offset a long from
> the parser into the executor.  The tuple counter is already a long in
> the executor.

Here is an updated patch with doc changes.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/fetch.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/fetch.sgml,v
retrieving revision 1.21
diff -c -c -r1.21 fetch.sgml
*** doc/src/sgml/ref/fetch.sgml    21 Apr 2002 19:02:39 -0000    1.21
--- doc/src/sgml/ref/fetch.sgml    27 Dec 2002 14:46:03 -0000
***************
*** 89,95 ****
         <para>
          A signed integer that specifies how many rows to fetch.
          Note that a negative integer is equivalent to changing the sense of
!         FORWARD and BACKWARD.
         </para>
        </listitem>
       </varlistentry>
--- 89,95 ----
         <para>
          A signed integer that specifies how many rows to fetch.
          Note that a negative integer is equivalent to changing the sense of
!         FORWARD and BACKWARD. Zero re-fetches the current row.
         </para>
        </listitem>
       </varlistentry>
***************
*** 176,205 ****
         <para>
      <productname>PostgreSQL</productname> does not support absolute
      positioning of cursors.
-        </para>
-       </listitem>
-      </varlistentry>
-
-      <varlistentry>
-       <term><computeroutput>
- ERROR:  FETCH/RELATIVE at current position is not supported
-        </computeroutput></term>
-       <listitem>
-        <para>
-     <acronym>SQL92</acronym> allows one to repetitively retrieve the cursor
-     at its <quote>current position</quote> using the syntax
-     <synopsis>
- FETCH RELATIVE 0 FROM <replaceable class="PARAMETER">cursor</replaceable>.
-     </synopsis>
-        </para>
-
-        <para>
-     <productname>PostgreSQL</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, <productname>PostgreSQL</productname>
-     assumes that the user intended <acronym>SQL92</acronym> behavior
-     and returns this error message.
         </para>
        </listitem>
       </varlistentry>
--- 176,181 ----
Index: doc/src/sgml/ref/move.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/move.sgml,v
retrieving revision 1.14
diff -c -c -r1.14 move.sgml
*** doc/src/sgml/ref/move.sgml    13 Nov 2002 00:44:08 -0000    1.14
--- doc/src/sgml/ref/move.sgml    27 Dec 2002 14:46:03 -0000
***************
*** 35,42 ****
     Description
    </title>
    <para>
!    <command>MOVE</command> allows a user to move cursor position a specified
!    number of rows.
     <command>MOVE</command> works like the <command>FETCH</command> command,
     but only positions the cursor and does not return rows.
     <replaceable class="PARAMETER">LAST</replaceable> moves to the end
--- 35,42 ----
     Description
    </title>
    <para>
!    <command>MOVE</command> allows a user to move the cursor position a
!    specified number of rows.
     <command>MOVE</command> works like the <command>FETCH</command> command,
     but only positions the cursor and does not return rows.
     <replaceable class="PARAMETER">LAST</replaceable> moves to the end
Index: src/backend/commands/portalcmds.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/commands/portalcmds.c,v
retrieving revision 1.6
diff -c -c -r1.6 portalcmds.c
*** src/backend/commands/portalcmds.c    15 Dec 2002 16:17:42 -0000    1.6
--- src/backend/commands/portalcmds.c    27 Dec 2002 14:46:04 -0000
***************
*** 65,71 ****
  void
  PerformPortalFetch(char *name,
                     bool forward,
!                    int count,
                     CommandDest dest,
                     char *completionTag)
  {
--- 65,71 ----
  void
  PerformPortalFetch(char *name,
                     bool forward,
!                    long count,
                     CommandDest dest,
                     char *completionTag)
  {
***************
*** 100,113 ****
          return;
      }

!     /* If zero count, we are done */
      if (count == 0)
!         return;

      /* Internally, zero count processes all portal rows */
!     if (count == INT_MAX)
          count = 0;
!
      /*
       * switch into the portal context
       */
--- 100,147 ----
          return;
      }

!     /* If zero count, handle specially */
      if (count == 0)
!     {
!         bool on_row = false;
!
!         /* Are we sitting on a row? */
!         oldcontext = MemoryContextSwitchTo(PortalGetHeapMemory(portal));
!         queryDesc = PortalGetQueryDesc(portal);
!         estate = queryDesc->estate;
!         if (portal->atStart == false && portal->atEnd == false)
!             on_row = true;
!         MemoryContextSwitchTo(oldcontext);
!
!         if (dest == None)
!         {
!             /* MOVE 0 returns 0/1 based on if FETCH 0 would return a row */
!             if (completionTag && on_row)
!                 strcpy(completionTag, "MOVE 1");
!             return;
!         }
!         else
!         {
!             /* If we are not on a row, FETCH 0 returns nothing */
!             if (!on_row)
!                 return;
!
!             /* Since we are sitting on a row, return the row */
!             /* Back up so we can reread the row */
!             PerformPortalFetch(name, false /* backward */, 1,
!                                None, /* throw away output */
!                                NULL /* do not modify the command tag */);
!
!             /* Set up to fetch one row */
!             count = 1;
!             forward = true;
!         }
!     }

      /* Internally, zero count processes all portal rows */
!     if (count == LONG_MAX)
          count = 0;
!
      /*
       * switch into the portal context
       */
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.388
diff -c -c -r2.388 gram.y
*** src/backend/parser/gram.y    12 Dec 2002 20:35:13 -0000    2.388
--- src/backend/parser/gram.y    27 Dec 2002 14:46:15 -0000
***************
*** 2591,2603 ****
  FetchStmt:    FETCH direction fetch_how_many from_in name
                  {
                      FetchStmt *n = makeNode(FetchStmt);
-                     if ($2 == RELATIVE)
-                     {
-                         if ($3 == 0)
-                             elog(ERROR,
-                     "FETCH / RELATIVE at current position is not supported");
-                         $2 = FORWARD;
-                     }
                      if ($3 < 0)
                      {
                          $3 = -$3;
--- 2591,2596 ----
***************
*** 2629,2638 ****
              | FETCH direction from_in name
                  {
                      FetchStmt *n = makeNode(FetchStmt);
-                     if ($2 == RELATIVE)
-                     {
-                         $2 = FORWARD;
-                     }
                      n->direction = $2;
                      n->howMany = 1;
                      n->portalname = $4;
--- 2622,2627 ----
***************
*** 2719,2738 ****

  direction:    FORWARD                                    { $$ = FORWARD; }
              | BACKWARD                                { $$ = BACKWARD; }
!             | RELATIVE                                { $$ = RELATIVE; }
              | ABSOLUTE
                  {
                      elog(NOTICE,
                      "FETCH / ABSOLUTE not supported, using RELATIVE");
!                     $$ = RELATIVE;
                  }
          ;

  fetch_how_many:
              Iconst                                    { $$ = $1; }
              | '-' Iconst                            { $$ = - $2; }
!             | ALL                                    { $$ = INT_MAX; }
!             | LAST                                    { $$ = INT_MAX; }
              | NEXT                                    { $$ = 1; }
              | PRIOR                                    { $$ = -1; }
          ;
--- 2708,2727 ----

  direction:    FORWARD                                    { $$ = FORWARD; }
              | BACKWARD                                { $$ = BACKWARD; }
!             | RELATIVE                                { $$ = FORWARD; }
              | ABSOLUTE
                  {
                      elog(NOTICE,
                      "FETCH / ABSOLUTE not supported, using RELATIVE");
!                     $$ = FORWARD;
                  }
          ;

  fetch_how_many:
              Iconst                                    { $$ = $1; }
              | '-' Iconst                            { $$ = - $2; }
!             | ALL                                    { $$ = LONG_MAX; }
!             | LAST                                    { $$ = LONG_MAX; }
              | NEXT                                    { $$ = 1; }
              | PRIOR                                    { $$ = -1; }
          ;
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/tcop/utility.c,v
retrieving revision 1.185
diff -c -c -r1.185 utility.c
*** src/backend/tcop/utility.c    6 Dec 2002 05:00:31 -0000    1.185
--- src/backend/tcop/utility.c    27 Dec 2002 14:46:16 -0000
***************
*** 257,263 ****
                  FetchStmt  *stmt = (FetchStmt *) parsetree;
                  char       *portalName = stmt->portalname;
                  bool        forward;
!                 int            count;

                  forward = (bool) (stmt->direction == FORWARD);

--- 257,263 ----
                  FetchStmt  *stmt = (FetchStmt *) parsetree;
                  char       *portalName = stmt->portalname;
                  bool        forward;
!                 long        count;

                  forward = (bool) (stmt->direction == FORWARD);

Index: src/include/commands/portalcmds.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/commands/portalcmds.h,v
retrieving revision 1.3
diff -c -c -r1.3 portalcmds.h
*** src/include/commands/portalcmds.h    13 Nov 2002 00:44:09 -0000    1.3
--- src/include/commands/portalcmds.h    27 Dec 2002 14:46:17 -0000
***************
*** 25,31 ****
   *        BadArg if forward invalid.
   *        "ERROR" if portal not found.
   */
! extern void PerformPortalFetch(char *name, bool forward, int count,
                     CommandDest dest, char *completionTag);

  /*
--- 25,31 ----
   *        BadArg if forward invalid.
   *        "ERROR" if portal not found.
   */
! extern void PerformPortalFetch(char *name, bool forward, long count,
                     CommandDest dest, char *completionTag);

  /*
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/nodes/parsenodes.h,v
retrieving revision 1.223
diff -c -c -r1.223 parsenodes.h
*** src/include/nodes/parsenodes.h    12 Dec 2002 20:35:16 -0000    1.223
--- src/include/nodes/parsenodes.h    27 Dec 2002 14:46:23 -0000
***************
*** 1198,1204 ****
  {
      NodeTag        type;
      int            direction;        /* FORWARD or BACKWARD */
!     int            howMany;        /* amount to fetch */
      char       *portalname;        /* name of portal (cursor) */
      bool        ismove;            /* TRUE if MOVE */
  } FetchStmt;
--- 1198,1204 ----
  {
      NodeTag        type;
      int            direction;        /* FORWARD or BACKWARD */
!     long        howMany;        /* amount to fetch */
      char       *portalname;        /* name of portal (cursor) */
      bool        ismove;            /* TRUE if MOVE */
  } FetchStmt;