Hi!

I took a stab at implementing MOVE support for cursors in PL/pgsql,
because I wanted it for a project. Attached patch *seems* to work. But..
Given that this is my first venture into touching bison/flex files, I
may be completely off-target in how it's done.

Could somebody take a look at it and comment on if I'm completelyi off
or only slightly off?
(And if there are good arguments not to include MOVE support at all in
pl/pgsql, I'd still be interested in comments on the rest of it, in
order to learn..)

Thanks!

//Magnus

Index: src/pl/plpgsql/src/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.98
diff -c -r1.98 gram.y
*** src/pl/plpgsql/src/gram.y   8 Feb 2007 18:37:14 -0000       1.98
--- src/pl/plpgsql/src/gram.y   15 Feb 2007 12:15:20 -0000
***************
*** 92,97 ****
--- 92,98 ----
                PLpgSQL_exception_block *exception_block;
                PLpgSQL_nsitem                  *nsitem;
                PLpgSQL_diag_item               *diagitem;
+               PLpgSQL_fetch_direction *fetchdir;
  }
  
  %type <declhdr> decl_sect
***************
*** 124,136 ****
  %type <stmt>  stmt_assign stmt_if stmt_loop stmt_while stmt_exit
  %type <stmt>  stmt_return stmt_raise stmt_execsql stmt_execsql_insert
  %type <stmt>  stmt_dynexecute stmt_for stmt_perform stmt_getdiag
! %type <stmt>  stmt_open stmt_fetch stmt_close stmt_null
  
  %type <list>  proc_exceptions
  %type <exception_block> exception_sect
  %type <exception>     proc_exception
  %type <condition>     proc_conditions
  
  
  %type <ival>  raise_level
  %type <str>           raise_msg
--- 125,138 ----
  %type <stmt>  stmt_assign stmt_if stmt_loop stmt_while stmt_exit
  %type <stmt>  stmt_return stmt_raise stmt_execsql stmt_execsql_insert
  %type <stmt>  stmt_dynexecute stmt_for stmt_perform stmt_getdiag
! %type <stmt>  stmt_open stmt_fetch stmt_close stmt_move stmt_null
  
  %type <list>  proc_exceptions
  %type <exception_block> exception_sect
  %type <exception>     proc_exception
  %type <condition>     proc_conditions
  
+ %type <fetchdir> fetch_direction
  
  %type <ival>  raise_level
  %type <str>           raise_msg
***************
*** 140,151 ****
--- 142,156 ----
  %type <ival>  getdiag_kind getdiag_target
  
  %type <ival>  lno
+ %token <ival> T_NUMBER
  
                /*
                 * Keyword tokens
                 */
+ %token        K_ABSOLUTE
  %token        K_ALIAS
  %token        K_ASSIGN
+ %token        K_BACKWARD
  %token        K_BEGIN
  %token        K_BY
  %token        K_CLOSE
***************
*** 165,170 ****
--- 170,177 ----
  %token        K_EXIT
  %token        K_FOR
  %token        K_FETCH
+ %token        K_FIRST
+ %token        K_FORWARD
  %token        K_FROM
  %token        K_GET
  %token        K_IF
***************
*** 173,180 ****
--- 180,189 ----
  %token        K_INSERT
  %token        K_INTO
  %token        K_IS
+ %token        K_LAST
  %token        K_LOG
  %token        K_LOOP
+ %token        K_MOVE
  %token        K_NEXT
  %token        K_NOT
  %token        K_NOTICE
***************
*** 182,189 ****
--- 191,200 ----
  %token        K_OPEN
  %token        K_OR
  %token        K_PERFORM
+ %token        K_PRIOR
  %token        K_ROW_COUNT
  %token        K_RAISE
+ %token        K_RELATIVE
  %token        K_RENAME
  %token        K_RESULT_OID
  %token        K_RETURN
***************
*** 616,621 ****
--- 627,634 ----
                                                { $$ = $1; }
                                | stmt_close
                                                { $$ = $1; }
+                               | stmt_move
+                                               { $$ = $1; }
                                | stmt_null
                                                { $$ = $1; }
                                ;
***************
*** 1446,1452 ****
--- 1459,1553 ----
                                                $$ = (PLpgSQL_stmt *)new;
                                        }
                                ;
+                               
+ stmt_move             : K_MOVE lno cursor_variable fetch_direction ';'
+                                       {
+                                               PLpgSQL_stmt_move *new;
+                                               
+                                               new = 
palloc(sizeof(PLpgSQL_stmt_move));
+                                               new->cmd_type = 
PLPGSQL_STMT_MOVE;
+                                               new->lineno = $2;
+                                               new->curvar = $3;
+                                               new->fetchdir = 
(PLpgSQL_fetch_direction *)$4;
+                                               
+                                               $$ = (PLpgSQL_stmt *)new;
+                                       }
+                               ;
  
+ fetch_direction       : K_FORWARD T_NUMBER
+                                       {
+                                               PLpgSQL_fetch_direction *new;
+                                               
+                                               new = 
palloc(sizeof(PLpgSQL_fetch_direction));
+                                               new->fetch_direction = 
FETCH_FORWARD;
+                                               new->howMany = $2;
+                                               $$ = new;
+                                       }
+                               | K_BACKWARD T_NUMBER
+                                       {
+                                               PLpgSQL_fetch_direction *new;
+                                               
+                                               new = 
palloc(sizeof(PLpgSQL_fetch_direction));
+                                               new->fetch_direction = 
FETCH_BACKWARD;
+                                               new->howMany = $2;
+                                               $$ = new;
+                                       }
+                               | K_NEXT
+                                       {
+                                               PLpgSQL_fetch_direction *new;
+                                               
+                                               new = 
palloc(sizeof(PLpgSQL_fetch_direction));
+                                               new->fetch_direction = 
FETCH_FORWARD;
+                                               new->howMany = 1;
+                                               $$ = new;
+                                       }
+                               | K_PRIOR
+                                       {
+                                               PLpgSQL_fetch_direction *new;
+                                               
+                                               new = 
palloc(sizeof(PLpgSQL_fetch_direction));
+                                               new->fetch_direction = 
FETCH_BACKWARD;
+                                               new->howMany = 1;
+                                               $$ = new;
+                                       }
+                               | K_FIRST
+                                       {
+                                               PLpgSQL_fetch_direction *new;
+                                               
+                                               new = 
palloc(sizeof(PLpgSQL_fetch_direction));
+                                               new->fetch_direction = 
FETCH_ABSOLUTE;
+                                               new->howMany = 0;
+                                               $$ = new;
+                                       }
+                               | K_LAST
+                                       {
+                                               PLpgSQL_fetch_direction *new;
+                                               
+                                               new = 
palloc(sizeof(PLpgSQL_fetch_direction));
+                                               new->fetch_direction = 
FETCH_ABSOLUTE;
+                                               new->howMany = -1;
+                                               $$ = new;
+                                       }
+                               | K_RELATIVE T_NUMBER
+                                       {
+                                               PLpgSQL_fetch_direction *new;
+                                               
+                                               new = 
palloc(sizeof(PLpgSQL_fetch_direction));
+                                               new->fetch_direction = 
FETCH_RELATIVE;
+                                               new->howMany = $2;
+                                               $$ = new;
+                                       }
+                               | K_ABSOLUTE T_NUMBER
+                                       {
+                                               PLpgSQL_fetch_direction *new;
+                                               
+                                               new = 
palloc(sizeof(PLpgSQL_fetch_direction));
+                                               new->fetch_direction = 
FETCH_ABSOLUTE;
+                                               new->howMany = $2;
+                                               $$ = new;
+                                       }
+                               ;
+                               
  stmt_null             : K_NULL ';'
                                        {
                                                /* We do not bother building a 
node for NULL */
***************
*** 1627,1633 ****
                                                $$ = plpgsql_error_lineno = 
plpgsql_scanner_lineno();
                                        }
                                ;
- 
  %%
  
  
--- 1728,1733 ----
Index: src/pl/plpgsql/src/pl_exec.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.188
diff -c -r1.188 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c        8 Feb 2007 18:37:30 -0000       1.188
--- src/pl/plpgsql/src/pl_exec.c        15 Feb 2007 12:14:02 -0000
***************
*** 97,102 ****
--- 97,104 ----
                           PLpgSQL_stmt_open *stmt);
  static int exec_stmt_fetch(PLpgSQL_execstate *estate,
                                PLpgSQL_stmt_fetch *stmt);
+ static int exec_stmt_move(PLpgSQL_execstate *estate,
+                               PLpgSQL_stmt_move *stmt);
  static int exec_stmt_close(PLpgSQL_execstate *estate,
                                PLpgSQL_stmt_close *stmt);
  static int exec_stmt_exit(PLpgSQL_execstate *estate,
***************
*** 1252,1257 ****
--- 1254,1263 ----
                        rc = exec_stmt_close(estate, (PLpgSQL_stmt_close *) 
stmt);
                        break;
  
+               case PLPGSQL_STMT_MOVE:
+                       rc = exec_stmt_move(estate, (PLpgSQL_stmt_move *) stmt);
+                       break;
+ 
                default:
                        estate->err_stmt = save_estmt;
                        elog(ERROR, "unrecognized cmdtype: %d", stmt->cmd_type);
***************
*** 3216,3221 ****
--- 3222,3308 ----
        return PLPGSQL_RC_OK;
  }
  
+ /* ----------
+  * exec_stmt_move                     Move the position of a cursor
+  * ----------
+  */
+ static int
+ exec_stmt_move(PLpgSQL_execstate *estate, PLpgSQL_stmt_move *stmt)
+ {
+       PLpgSQL_var     *curvar = NULL;
+       Portal          portal;
+       char       *curname;
+ 
+       /* ----------
+        * Get the portal of the cursor by name
+        * ----------
+        */
+       curvar = (PLpgSQL_var *) (estate->datums[stmt->curvar]);
+       if (curvar->isnull)
+               ereport(ERROR,
+                               (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+                                errmsg("cursor variable \"%s\" is NULL", 
curvar->refname)));
+       curname = DatumGetCString(DirectFunctionCall1(textout, curvar->value));
+ 
+       portal = SPI_cursor_find(curname);
+       if (portal == NULL)
+               ereport(ERROR,
+                               (errcode(ERRCODE_UNDEFINED_CURSOR),
+                                errmsg("cursor \"%s\" does not exist", 
curname)));
+       pfree(curname);
+ 
+       /*
+        * Now move
+        */
+       switch (stmt->fetchdir->fetch_direction)
+       {
+               case FETCH_FORWARD:
+                       SPI_cursor_move(portal, true, stmt->fetchdir->howMany);
+                       break;
+               case FETCH_BACKWARD:
+                       SPI_cursor_move(portal, false, stmt->fetchdir->howMany);
+                       break;
+               case FETCH_RELATIVE:
+                       if (stmt->fetchdir->howMany > 0)
+                               SPI_cursor_move(portal, true, 
stmt->fetchdir->howMany);
+                       else
+                               SPI_cursor_move(portal, false, 
-stmt->fetchdir->howMany);
+                       break;
+               case FETCH_ABSOLUTE:
+                       if (stmt->fetchdir->howMany == 0)
+                       {
+                               /* Move to start */
+                               SPI_cursor_move(portal, false, 
portal->portalPos);
+                       }
+                       else if (stmt->fetchdir->howMany > 0)
+                       {
+                               /* Absolute position starting from beginning */
+                               SPI_cursor_move(portal, false, 
portal->portalPos);
+                               /* Now move forward to the requested position */
+                               SPI_cursor_move(portal, true, 
stmt->fetchdir->howMany);
+                       }
+                       else 
+                       {
+                               /* Absolute position starting from end */
+                               while (!portal->atEnd)
+                               {
+                                       /* Move forward rapidly until we hit 
the end */
+                                       SPI_cursor_move(portal, true, 1000000);
+                               }
+                               /* Now move backwards the requested number of 
rows, plus one
+                                * extra row because we're positioned _past_ 
the end of the cursor. */
+                               SPI_cursor_move(portal, false, 
-stmt->fetchdir->howMany + 1);
+                       }
+                       break;
+               default:
+                       ereport(ERROR,
+                               (errcode(ERRCODE_INTERNAL_ERROR),
+                                errmsg("invalid move direction %i", 
stmt->fetchdir->fetch_direction)));
+       }
+ 
+       return PLPGSQL_RC_OK;
+ }
+ 
  
  /* ----------
   * exec_assign_expr                   Put an expression's result into
Index: src/pl/plpgsql/src/plpgsql.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/pl/plpgsql/src/plpgsql.h,v
retrieving revision 1.85
diff -c -r1.85 plpgsql.h
*** src/pl/plpgsql/src/plpgsql.h        9 Feb 2007 03:35:34 -0000       1.85
--- src/pl/plpgsql/src/plpgsql.h        15 Feb 2007 11:04:34 -0000
***************
*** 91,96 ****
--- 91,97 ----
        PLPGSQL_STMT_OPEN,
        PLPGSQL_STMT_FETCH,
        PLPGSQL_STMT_CLOSE,
+       PLPGSQL_STMT_MOVE,
        PLPGSQL_STMT_PERFORM
  };
  
***************
*** 459,464 ****
--- 460,478 ----
        int                     curvar;
  } PLpgSQL_stmt_close;
  
+ typedef struct
+ {
+       FetchDirection  fetch_direction;
+       int                     howMany;
+ } PLpgSQL_fetch_direction;
+ 
+ typedef struct
+ {                                                             /* MOVE curvar  
                                        */
+       int                     cmd_type;
+       int                     lineno;
+       int                     curvar;
+       PLpgSQL_fetch_direction *fetchdir;
+ } PLpgSQL_stmt_move;
  
  typedef struct
  {                                                             /* EXIT or 
CONTINUE statement                   */
Index: src/pl/plpgsql/src/scan.l
===================================================================
RCS file: /projects/cvsroot/pgsql/src/pl/plpgsql/src/scan.l,v
retrieving revision 1.55
diff -c -r1.55 scan.l
*** src/pl/plpgsql/src/scan.l   5 Jan 2007 22:20:02 -0000       1.55
--- src/pl/plpgsql/src/scan.l   15 Feb 2007 12:15:31 -0000
***************
*** 112,118 ****
--- 112,120 ----
  :=                            { return K_ASSIGN;                      }
  =                             { return K_ASSIGN;                      }
  \.\.                  { return K_DOTDOT;                      }
+ absolute              { return K_ABSOLUTE;            }
  alias                 { return K_ALIAS;                       }
+ backward              { return K_BACKWARD;            }
  begin                 { return K_BEGIN;                       }
  by                            { return K_BY;                          }
  close                 { return K_CLOSE;                       }
***************
*** 130,137 ****
--- 132,141 ----
  exception             { return K_EXCEPTION;           }
  execute                       { return K_EXECUTE;                     }
  exit                  { return K_EXIT;                        }
+ first                 { return K_FIRST;                       }
  fetch                 { return K_FETCH;                       }
  for                           { return K_FOR;                         }
+ forward                       { return K_FORWARD;                     }
  from                  { return K_FROM;                        }
  get                           { return K_GET;                         }
  if                            { return K_IF;                          }
***************
*** 140,147 ****
--- 144,153 ----
  insert                        { return K_INSERT;                      }
  into                  { return K_INTO;                        }
  is                            { return K_IS;                          }
+ last                  { return K_LAST;                        }
  log                           { return K_LOG;                         }
  loop                  { return K_LOOP;                        }
+ move                  { return K_MOVE;                        }
  next                  { return K_NEXT;                        }
  not                           { return K_NOT;                         }
  notice                        { return K_NOTICE;                      }
***************
*** 149,155 ****
--- 155,163 ----
  open                  { return K_OPEN;                        }
  or                            { return K_OR;                          }
  perform                       { return K_PERFORM;                     }
+ prior                 { return K_PRIOR;                       }
  raise                 { return K_RAISE;                       }
+ relative              { return K_RELATIVE;            }
  rename                        { return K_RENAME;                      }
  result_oid            { return K_RESULT_OID;          }
  return                        { return K_RETURN;                      }
***************
*** 223,229 ****
        plpgsql_error_lineno = plpgsql_scanner_lineno();
        return plpgsql_parse_dblwordrowtype(yytext); }
  
! {digit}+              { return T_NUMBER;                      }
  
  \".                           {
                                plpgsql_error_lineno = plpgsql_scanner_lineno();
--- 231,245 ----
        plpgsql_error_lineno = plpgsql_scanner_lineno();
        return plpgsql_parse_dblwordrowtype(yytext); }
  
! {digit}+              {
!                                       long val;
!                                       char* endptr;
! 
!                                       errno = 0;
!                                       val = strtol(yytext, &endptr, 10);
!                                       yylval.ival = val;
!                                       return T_NUMBER;
!                               }
  
  \".                           {
                                plpgsql_error_lineno = plpgsql_scanner_lineno();
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly

Reply via email to