fix for parameterized queries in DECLARE CURSOR statements - Mailing list pgsql-patches

Here's a patch that allows parameterized queries to be used in a DECLARE
CURSOR statement.

Previously, the DECLARE would succeed but any FETCHes would fail as the
parameter values supplied to DECLARE were not propagated to the portal
it created. This patch adds that propagation. See
http://archives.postgresql.org/pgsql-hackers/2004-07/msg01047.php for
discussion.

I've tested the V3 protocol and SQL function paths by hand. The SPI path
hasn't been specifically tested but the regression tests appear happy.

-O

? src/Makefile.global
? src/include/pg_config.h
? src/include/stamp-h
Index: src/backend/commands/portalcmds.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/portalcmds.c,v
retrieving revision 1.29
diff -u -c -r1.29 portalcmds.c
*** src/backend/commands/portalcmds.c    17 Jul 2004 03:28:47 -0000    1.29
--- src/backend/commands/portalcmds.c    26 Jul 2004 11:37:24 -0000
***************
*** 36,42 ****
   *        Execute SQL DECLARE CURSOR command.
   */
  void
! PerformCursorOpen(DeclareCursorStmt *stmt)
  {
      List       *rewritten;
      Query       *query;
--- 36,42 ----
   *        Execute SQL DECLARE CURSOR command.
   */
  void
! PerformCursorOpen(DeclareCursorStmt *stmt, ParamListInfo params)
  {
      List       *rewritten;
      Query       *query;
***************
*** 104,111 ****
                        list_make1(plan),
                        PortalGetHeapMemory(portal));

-     MemoryContextSwitchTo(oldContext);
-
      /*
       * Set up options for portal.
       *
--- 104,109 ----
***************
*** 123,131 ****
      }

      /*
!      * Start execution --- never any params for a cursor.
       */
!     PortalStart(portal, NULL);

      Assert(portal->strategy == PORTAL_ONE_SELECT);

--- 121,133 ----
      }

      /*
!      * Start execution. The original DECLARE may have had parameters
!      * (if V3 protocol Parse/Bind messages were used); we must
!      * pass those parameters into the new portal, making sure to copy them
!      * into the new portal's memory context as the originals are owned by
!      * our caller.
       */
!     PortalStart(portal, copyParamList(params));

      Assert(portal->strategy == PORTAL_ONE_SELECT);

***************
*** 133,138 ****
--- 135,142 ----
       * We're done; the query won't actually be run until
       * PerformPortalFetch is called.
       */
+
+     MemoryContextSwitchTo(oldContext);
  }

  /*
Index: src/backend/commands/prepare.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/prepare.c,v
retrieving revision 1.28
diff -u -c -r1.28 prepare.c
*** src/backend/commands/prepare.c    11 Jun 2004 01:08:38 -0000    1.28
--- src/backend/commands/prepare.c    26 Jul 2004 11:37:24 -0000
***************
*** 211,217 ****
      int            nargs = list_length(argtypes);
      ParamListInfo paramLI;
      List       *exprstates;
!     ListCell   *l;
      int            i = 0;

      /* Parser should have caught this error, but check for safety */
--- 211,217 ----
      int            nargs = list_length(argtypes);
      ParamListInfo paramLI;
      List       *exprstates;
!     ListCell   *le, *la;
      int            i = 0;

      /* Parser should have caught this error, but check for safety */
***************
*** 223,231 ****
      paramLI = (ParamListInfo)
          palloc0((nargs + 1) * sizeof(ParamListInfoData));

!     foreach(l, exprstates)
      {
!         ExprState  *n = lfirst(l);
          bool        isNull;

          paramLI[i].value = ExecEvalExprSwitchContext(n,
--- 223,231 ----
      paramLI = (ParamListInfo)
          palloc0((nargs + 1) * sizeof(ParamListInfoData));

!     forboth(le, exprstates, la, argtypes)
      {
!         ExprState  *n = lfirst(le);
          bool        isNull;

          paramLI[i].value = ExecEvalExprSwitchContext(n,
***************
*** 235,240 ****
--- 235,241 ----
          paramLI[i].kind = PARAM_NUM;
          paramLI[i].id = i + 1;
          paramLI[i].isnull = isNull;
+         paramLI[i].valuetype = lfirst_oid(la);

          i++;
      }
Index: src/backend/commands/schemacmds.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/schemacmds.c,v
retrieving revision 1.20
diff -u -c -r1.20 schemacmds.c
*** src/backend/commands/schemacmds.c    25 Jun 2004 21:55:53 -0000    1.20
--- src/backend/commands/schemacmds.c    26 Jul 2004 11:37:24 -0000
***************
*** 168,174 ****
              /* schemas should contain only utility stmts */
              Assert(querytree->commandType == CMD_UTILITY);
              /* do this step */
!             ProcessUtility(querytree->utilityStmt, None_Receiver, NULL);
              /* make sure later steps can see the object created here */
              CommandCounterIncrement();
          }
--- 168,174 ----
              /* schemas should contain only utility stmts */
              Assert(querytree->commandType == CMD_UTILITY);
              /* do this step */
!             ProcessUtility(querytree->utilityStmt, None_Receiver, NULL, NULL);
              /* make sure later steps can see the object created here */
              CommandCounterIncrement();
          }
Index: src/backend/executor/functions.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/executor/functions.c,v
retrieving revision 1.83
diff -u -c -r1.83 functions.c
*** src/backend/executor/functions.c    15 Jul 2004 13:51:38 -0000    1.83
--- src/backend/executor/functions.c    26 Jul 2004 11:37:24 -0000
***************
*** 61,66 ****
--- 61,67 ----
      Oid            rettype;        /* actual return type */
      int            typlen;            /* length of the return type */
      bool        typbyval;        /* true if return type is pass by value */
+     Oid        *argtypes;       /* resolved types of arguments */
      bool        returnsTuple;    /* true if returning whole tuple result */
      bool        shutdown_reg;    /* true if registered shutdown callback */

***************
*** 77,83 ****
  static execution_state *init_execution_state(List *queryTree_list);
  static void init_sql_fcache(FmgrInfo *finfo);
  static void postquel_start(execution_state *es, SQLFunctionCachePtr fcache);
! static TupleTableSlot *postquel_getnext(execution_state *es);
  static void postquel_end(execution_state *es);
  static void postquel_sub_params(SQLFunctionCachePtr fcache,
                      FunctionCallInfo fcinfo);
--- 78,84 ----
  static execution_state *init_execution_state(List *queryTree_list);
  static void init_sql_fcache(FmgrInfo *finfo);
  static void postquel_start(execution_state *es, SQLFunctionCachePtr fcache);
! static TupleTableSlot *postquel_getnext(execution_state *es, ParamListInfo params);
  static void postquel_end(execution_state *es);
  static void postquel_sub_params(SQLFunctionCachePtr fcache,
                      FunctionCallInfo fcinfo);
***************
*** 224,229 ****
--- 225,236 ----
      else
          argOidVect = NULL;

+
+     /*
+      * remember computed arg types so we can set them on the ParamListInfo lists we generate.
+      */
+     fcache->argtypes = argOidVect;
+
      tmp = SysCacheGetAttr(PROCOID,
                            procedureTuple,
                            Anum_pg_proc_prosrc,
***************
*** 277,289 ****
  }

  static TupleTableSlot *
! postquel_getnext(execution_state *es)
  {
      long        count;

      if (es->qd->operation == CMD_UTILITY)
      {
!         ProcessUtility(es->qd->parsetree->utilityStmt, es->qd->dest, NULL);
          return NULL;
      }

--- 284,296 ----
  }

  static TupleTableSlot *
! postquel_getnext(execution_state *es, ParamListInfo params)
  {
      long        count;

      if (es->qd->operation == CMD_UTILITY)
      {
!         ProcessUtility(es->qd->parsetree->utilityStmt, es->qd->dest, NULL, params);
          return NULL;
      }

***************
*** 334,339 ****
--- 341,347 ----
              paramLI[i].id = i + 1;
              paramLI[i].value = fcinfo->arg[i];
              paramLI[i].isnull = fcinfo->argnull[i];
+             paramLI[i].valuetype = fcache->argtypes[i];
          }
          paramLI[nargs].kind = PARAM_INVALID;
      }
***************
*** 357,363 ****
      if (es->status == F_EXEC_START)
          postquel_start(es, fcache);

!     slot = postquel_getnext(es);

      if (TupIsNull(slot))
      {
--- 365,371 ----
      if (es->status == F_EXEC_START)
          postquel_start(es, fcache);

!     slot = postquel_getnext(es, fcache->paramLI);

      if (TupIsNull(slot))
      {
Index: src/backend/executor/spi.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/executor/spi.c,v
retrieving revision 1.120
diff -u -c -r1.120 spi.c
*** src/backend/executor/spi.c    1 Jul 2004 21:17:13 -0000    1.120
--- src/backend/executor/spi.c    26 Jul 2004 11:37:24 -0000
***************
*** 821,826 ****
--- 821,827 ----
              paramLI[k].kind = PARAM_NUM;
              paramLI[k].id = k + 1;
              paramLI[k].isnull = (Nulls && Nulls[k] == 'n');
+             paramLI[k].valuetype = spiplan->argtypes[k];
              if (paramLI[k].isnull)
              {
                  /* nulls just copy */
***************
*** 1184,1190 ****
                  res = SPI_OK_UTILITY;
                  if (plan == NULL)
                  {
!                     ProcessUtility(queryTree->utilityStmt, dest, NULL);

                      if (IsA(queryTree->utilityStmt, TransactionStmt))
                      {
--- 1185,1191 ----
                  res = SPI_OK_UTILITY;
                  if (plan == NULL)
                  {
!                     ProcessUtility(queryTree->utilityStmt, dest, NULL, NULL);

                      if (IsA(queryTree->utilityStmt, TransactionStmt))
                      {
***************
*** 1306,1312 ****
              dest = CreateDestReceiver(queryTree->canSetTag ? SPI : None, NULL);
              if (queryTree->commandType == CMD_UTILITY)
              {
!                 ProcessUtility(queryTree->utilityStmt, dest, NULL);
                  res = SPI_OK_UTILITY;

                  if (IsA(queryTree->utilityStmt, TransactionStmt))
--- 1307,1313 ----
              dest = CreateDestReceiver(queryTree->canSetTag ? SPI : None, NULL);
              if (queryTree->commandType == CMD_UTILITY)
              {
!                 ProcessUtility(queryTree->utilityStmt, dest, NULL, paramLI);
                  res = SPI_OK_UTILITY;

                  if (IsA(queryTree->utilityStmt, TransactionStmt))
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.288
diff -u -c -r1.288 copyfuncs.c
*** src/backend/nodes/copyfuncs.c    12 Jul 2004 05:37:21 -0000    1.288
--- src/backend/nodes/copyfuncs.c    26 Jul 2004 11:37:25 -0000
***************
*** 25,31 ****
--- 25,33 ----
  #include "nodes/parsenodes.h"
  #include "nodes/plannodes.h"
  #include "nodes/relation.h"
+ #include "nodes/params.h"
  #include "utils/datum.h"
+ #include "utils/lsyscache.h"


  /*
***************
*** 3094,3096 ****
--- 3096,3147 ----

      return retval;
  }
+
+ /*
+  * ParamListInfo copy function.
+  *
+  * ParamListInfo isn't a Node, but since the header lives in nodes/param.h
+  * this seems the best place.
+  */
+ ParamListInfo
+ copyParamList(ParamListInfo from)
+ {
+     int i, size;
+     ParamListInfo retval;
+
+     if (from == NULL)
+         return NULL;
+
+     size = 0;
+     while (from[size].kind != PARAM_INVALID)
+         ++size;
+
+     retval = (ParamListInfo) palloc0((size + 1) * sizeof(ParamListInfoData));
+
+     for (i = 0; i < size; ++i) {
+         /* copy metadata */
+         retval[i].kind = from[i].kind;
+         retval[i].id = from[i].id;
+         if (from[i].kind == PARAM_NAMED)
+             retval[i].name = pstrdup(from[i].name);
+
+         /* copy value */
+         retval[i].isnull = from[i].isnull;
+         retval[i].valuetype = from[i].valuetype;
+         if (from[i].isnull)
+         {
+             retval[i].value = from[i].value; /* nulls just copy */
+         }
+         else
+         {
+             int16 typLen;
+             bool  typByVal;
+             get_typlenbyval(from[i].valuetype, &typLen, &typByVal);
+             retval[i].value = datumCopy(from[i].value, typByVal, typLen);
+         }
+     }
+
+     retval[size].kind = PARAM_INVALID;
+     return retval;
+ }
+
Index: src/backend/tcop/postgres.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/tcop/postgres.c,v
retrieving revision 1.424
diff -u -c -r1.424 postgres.c
*** src/backend/tcop/postgres.c    17 Jul 2004 03:29:00 -0000    1.424
--- src/backend/tcop/postgres.c    26 Jul 2004 11:37:27 -0000
***************
*** 1496,1501 ****
--- 1496,1502 ----
              params[i].kind = PARAM_NUM;
              params[i].id = i + 1;
              params[i].isnull = isNull;
+             params[i].valuetype = ptype;

              i++;
          }
Index: src/backend/tcop/pquery.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/tcop/pquery.c,v
retrieving revision 1.81
diff -u -c -r1.81 pquery.c
*** src/backend/tcop/pquery.c    17 Jul 2004 03:29:00 -0000    1.81
--- src/backend/tcop/pquery.c    26 Jul 2004 11:37:27 -0000
***************
*** 800,813 ****
      if (query->canSetTag)
      {
          /* utility statement can override default tag string */
!         ProcessUtility(utilityStmt, dest, completionTag);
          if (completionTag && completionTag[0] == '\0' && portal->commandTag)
              strcpy(completionTag, portal->commandTag);    /* use the default */
      }
      else
      {
          /* utility added by rewrite cannot set tag */
!         ProcessUtility(utilityStmt, dest, NULL);
      }

      /* Some utility statements may change context on us */
--- 800,813 ----
      if (query->canSetTag)
      {
          /* utility statement can override default tag string */
!         ProcessUtility(utilityStmt, dest, completionTag, portal->portalParams);
          if (completionTag && completionTag[0] == '\0' && portal->commandTag)
              strcpy(completionTag, portal->commandTag);    /* use the default */
      }
      else
      {
          /* utility added by rewrite cannot set tag */
!         ProcessUtility(utilityStmt, dest, NULL, portal->portalParams);
      }

      /* Some utility statements may change context on us */
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/tcop/utility.c,v
retrieving revision 1.220
diff -u -c -r1.220 utility.c
*** src/backend/tcop/utility.c    25 Jun 2004 21:55:57 -0000    1.220
--- src/backend/tcop/utility.c    26 Jul 2004 11:37:28 -0000
***************
*** 297,302 ****
--- 297,303 ----
   *    dest: where to send results
   *    completionTag: points to a buffer of size COMPLETION_TAG_BUFSIZE
   *        in which to store a command completion status string.
+  *  params: parameters to use during execution (currently only used by DECLARE)
   *
   * completionTag is only set nonempty if we want to return a nondefault
   * status (currently, only used for MOVE/FETCH).
***************
*** 306,312 ****
  void
  ProcessUtility(Node *parsetree,
                 DestReceiver *dest,
!                char *completionTag)
  {
      check_xact_readonly(parsetree);

--- 307,314 ----
  void
  ProcessUtility(Node *parsetree,
                 DestReceiver *dest,
!                char *completionTag,
!                ParamListInfo params)
  {
      check_xact_readonly(parsetree);

***************
*** 368,374 ****
               * Portal (cursor) manipulation
               */
          case T_DeclareCursorStmt:
!             PerformCursorOpen((DeclareCursorStmt *) parsetree);
              break;

          case T_ClosePortalStmt:
--- 370,376 ----
               * Portal (cursor) manipulation
               */
          case T_DeclareCursorStmt:
!             PerformCursorOpen((DeclareCursorStmt *) parsetree, params);
              break;

          case T_ClosePortalStmt:
Index: src/include/commands/portalcmds.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/commands/portalcmds.h,v
retrieving revision 1.15
diff -u -c -r1.15 portalcmds.h
*** src/include/commands/portalcmds.h    17 Jul 2004 03:30:56 -0000    1.15
--- src/include/commands/portalcmds.h    26 Jul 2004 11:37:29 -0000
***************
*** 15,23 ****
  #define PORTALCMDS_H

  #include "utils/portal.h"


! extern void PerformCursorOpen(DeclareCursorStmt *stmt);

  extern void PerformPortalFetch(FetchStmt *stmt, DestReceiver *dest,
                     char *completionTag);
--- 15,24 ----
  #define PORTALCMDS_H

  #include "utils/portal.h"
+ #include "nodes/params.h"


! extern void PerformCursorOpen(DeclareCursorStmt *stmt, ParamListInfo params);

  extern void PerformPortalFetch(FetchStmt *stmt, DestReceiver *dest,
                     char *completionTag);
Index: src/include/nodes/params.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/nodes/params.h,v
retrieving revision 1.24
diff -u -c -r1.24 params.h
*** src/include/nodes/params.h    29 Nov 2003 22:41:06 -0000    1.24
--- src/include/nodes/params.h    26 Jul 2004 11:37:29 -0000
***************
*** 58,63 ****
--- 58,64 ----
   *        name   : the parameter name (valid if kind == PARAM_NAMED)
   *        id       : the parameter id (valid if kind == PARAM_NUM)
   *        isnull : true if the value is null (if so 'value' is undefined)
+  *              valuetype: the type of the parameter value.
   *        value  : the value that has to be substituted in the place
   *                 of the parameter.
   *
***************
*** 73,78 ****
--- 74,80 ----
      char       *name;
      AttrNumber    id;
      bool        isnull;
+         Oid             valuetype;
      Datum        value;
  } ParamListInfoData;

***************
*** 103,106 ****
--- 105,113 ----
      bool        isnull;
  } ParamExecData;

+ /*
+  * Copying of parameter lists; implemented in backend/nodes/copyfuncs.c.
+  */
+ extern ParamListInfo copyParamList(ParamListInfo from);
+
  #endif   /* PARAMS_H */
Index: src/include/tcop/utility.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/tcop/utility.h,v
retrieving revision 1.21
diff -u -c -r1.21 utility.h
*** src/include/tcop/utility.h    29 Nov 2003 22:41:14 -0000    1.21
--- src/include/tcop/utility.h    26 Jul 2004 11:37:30 -0000
***************
*** 15,23 ****
  #define UTILITY_H

  #include "executor/execdesc.h"

  extern void ProcessUtility(Node *parsetree, DestReceiver *dest,
!                char *completionTag);

  extern bool UtilityReturnsTuples(Node *parsetree);

--- 15,24 ----
  #define UTILITY_H

  #include "executor/execdesc.h"
+ #include "nodes/params.h"

  extern void ProcessUtility(Node *parsetree, DestReceiver *dest,
!                char *completionTag, ParamListInfo params);

  extern bool UtilityReturnsTuples(Node *parsetree);

Index: src/test/regress/expected/portals.out
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/portals.out,v
retrieving revision 1.7
diff -u -c -r1.7 portals.out
*** src/test/regress/expected/portals.out    24 Aug 2003 21:02:43 -0000    1.7
--- src/test/regress/expected/portals.out    26 Jul 2004 11:37:30 -0000
***************
*** 738,740 ****
--- 738,796 ----
  -- should fail
  FETCH FROM foo26;
  ERROR:  cursor "foo26" does not exist
+ --
+ -- Parameterized DECLARE
+ --
+ BEGIN;
+ CREATE FUNCTION declares_cursor(int4)
+    RETURNS void
+    AS 'DECLARE c CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = $1;'
+    LANGUAGE 'sql';
+ SELECT declares_cursor(42);
+  declares_cursor
+ -----------------
+
+ (1 row)
+
+ FETCH ALL FROM c;
+  unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even
|stringu1 | stringu2 | string4  
+
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+       42 |    5530 |   0 |    2 |   2 |      2 |      42 |       42 |          42 |        42 |       42 |  84 |   85
|QBAAAA   | SEIAAA   | OOOOxx 
+ (1 row)
+
+ ROLLBACK;
+ --
+ -- Parameterized DECLARE with a non-primitive type
+ --
+ BEGIN;
+ CREATE FUNCTION declares_cursor(text)
+    RETURNS void
+    AS 'DECLARE c CURSOR FOR SELECT * FROM tenk1 WHERE stringu1 LIKE $1;'
+    LANGUAGE 'sql';
+ SELECT declares_cursor('AB%');
+  declares_cursor
+ -----------------
+
+ (1 row)
+
+ FETCH ALL FROM c;
+  unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even
|stringu1 | stringu2 | string4  
+
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+     1378 |     604 |   0 |    2 |   8 |     18 |      78 |      378 |        1378 |      1378 |     1378 | 156 |  157
|ABAAAA   | GXAAAA   | AAAAxx 
+     4758 |     758 |   0 |    2 |   8 |     18 |      58 |      758 |         758 |      4758 |     4758 | 116 |  117
|ABAAAA   | EDBAAA   | OOOOxx 
+     2054 |     778 |   0 |    2 |   4 |     14 |      54 |       54 |          54 |      2054 |     2054 | 108 |  109
|ABAAAA   | YDBAAA   | OOOOxx 
+     8138 |    1871 |   0 |    2 |   8 |     18 |      38 |      138 |         138 |      3138 |     8138 |  76 |   77
|ABAAAA   | ZTCAAA   | VVVVxx 
+     6786 |    2206 |   0 |    2 |   6 |      6 |      86 |      786 |         786 |      1786 |     6786 | 172 |  173
|ABAAAA   | WGDAAA   | OOOOxx 
+      702 |    2433 |   0 |    2 |   2 |      2 |       2 |      702 |         702 |       702 |      702 |   4 |    5
|ABAAAA   | PPDAAA   | HHHHxx 
+     7462 |    2529 |   0 |    2 |   2 |      2 |      62 |      462 |        1462 |      2462 |     7462 | 124 |  125
|ABAAAA   | HTDAAA   | HHHHxx 
+     8814 |    3033 |   0 |    2 |   4 |     14 |      14 |      814 |         814 |      3814 |     8814 |  28 |   29
|ABAAAA   | RMEAAA   | HHHHxx 
+     4082 |    3403 |   0 |    2 |   2 |      2 |      82 |       82 |          82 |      4082 |     4082 | 164 |  165
|ABAAAA   | XAFAAA   | VVVVxx 
+     6110 |    4039 |   0 |    2 |   0 |     10 |      10 |      110 |         110 |      1110 |     6110 |  20 |   21
|ABAAAA   | JZFAAA   | VVVVxx 
+       26 |    8152 |   0 |    2 |   6 |      6 |      26 |       26 |          26 |        26 |       26 |  52 |   53
|ABAAAA   | OBMAAA   | AAAAxx 
+     5434 |    8243 |   0 |    2 |   4 |     14 |      34 |      434 |        1434 |       434 |     5434 |  68 |   69
|ABAAAA   | BFMAAA   | VVVVxx 
+     3406 |    8275 |   0 |    2 |   6 |      6 |       6 |      406 |        1406 |      3406 |     3406 |  12 |   13
|ABAAAA   | HGMAAA   | VVVVxx 
+     2730 |    8511 |   0 |    2 |   0 |     10 |      30 |      730 |         730 |      2730 |     2730 |  60 |   61
|ABAAAA   | JPMAAA   | VVVVxx 
+     9490 |    8928 |   0 |    2 |   0 |     10 |      90 |      490 |        1490 |      4490 |     9490 | 180 |  181
|ABAAAA   | KFNAAA   | AAAAxx 
+ (15 rows)
+
+ ROLLBACK;
Index: src/test/regress/sql/portals.sql
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/test/regress/sql/portals.sql,v
retrieving revision 1.4
diff -u -c -r1.4 portals.sql
*** src/test/regress/sql/portals.sql    29 Apr 2003 03:21:30 -0000    1.4
--- src/test/regress/sql/portals.sql    26 Jul 2004 11:37:30 -0000
***************
*** 218,220 ****
--- 218,256 ----

  -- should fail
  FETCH FROM foo26;
+
+ --
+ -- Parameterized DECLARE
+ --
+
+ BEGIN;
+
+ CREATE FUNCTION declares_cursor(int4)
+    RETURNS void
+    AS 'DECLARE c CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = $1;'
+    LANGUAGE 'sql';
+
+ SELECT declares_cursor(42);
+
+ FETCH ALL FROM c;
+
+ ROLLBACK;
+
+ --
+ -- Parameterized DECLARE with a non-primitive type
+ --
+
+ BEGIN;
+
+ CREATE FUNCTION declares_cursor(text)
+    RETURNS void
+    AS 'DECLARE c CURSOR FOR SELECT * FROM tenk1 WHERE stringu1 LIKE $1;'
+    LANGUAGE 'sql';
+
+ SELECT declares_cursor('AB%');
+
+ FETCH ALL FROM c;
+
+ ROLLBACK;
+
+