Skip site navigation (1) Skip section navigation (2)

Patch for cursors with multiple parameters

From: Ian Lance Taylor <ian(at)airs(dot)com>
To: pgsql-patches(at)postgresql(dot)org
Subject: Patch for cursors with multiple parameters
Date: 2001-06-06 07:02:11
Message-ID: 20010606070211.10196.qmail@daffy.airs.com (view raw or flat)
Thread:
Lists: pgsql-patches
The recent cursor patch does not quite support cursors with multiple
parameters.

Here is a test case:

==================================================
CREATE TABLE customer (
    firstname CHAR(15),
    lastname  CHAR(20),
    id INTEGER ); 

INSERT INTO customer VALUES ('Penelope' , 'Cruz', 1);
INSERT INTO customer VALUES ('Gloria' , 'Ferrer', 2);
INSERT INTO customer VALUES ('Shalma' , 'Hayek', 3);

DROP FUNCTION cursor_test1();

CREATE FUNCTION cursor_test1() RETURNS INTEGER AS'
DECLARE
   c1  CURSOR  (low_i INTEGER , top_i INTEGER) IS
          SELECT * FROM customer WHERE id >= low_i AND id <= top_i;
    fn VARCHAR;
    ln VARCHAR;
    id INTEGER;
BEGIN
OPEN c1(2,3);
FETCH c1 INTO fn , ln, id;
RAISE NOTICE ''The record is % % %'', id, fn,ln;
RETURN 1;
END; 
' LANGUAGE 'plpgsql';

SELECT cursor_test1();
DROP TABLE customer;
==================================================

This should print something like

NOTICE:  The record is 2 Gloria          Ferrer

Instead, I get this:

ERROR:  parser: parse error at or near ""

When the cursor code evaluates
    OPEN c1(2,3)
it tries to evaluate
    SELECT (2,3)
That produces a parse error.

I have no real idea whether this should be a parse error or not.  Note
that
    SELECT 2,3
does work; it returns a two column table, as one would expect.  Since
I get the parse error in both 7.0.3 and current CVS sources, I assume
that it should be a parse error, and that the cursor code is
incorrect.  Here is a hack patch which fixes the problem.

Ian

Index: gram.y
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.20
diff -u -p -r1.20 gram.y
--- gram.y	2001/05/31 17:15:40	1.20
+++ gram.y	2001/06/06 06:56:49
@@ -1404,6 +1405,8 @@ stmt_open		: K_OPEN lno cursor_varptr
 						{
 							if ($3->cursor_explicit_argrow >= 0)
 							{
+								char   *cp;
+
 								tok = yylex();
 
 								if (tok != '(')
@@ -1412,7 +1415,20 @@ stmt_open		: K_OPEN lno cursor_varptr
 									elog(ERROR, "cursor %s has arguments", $3->refname);
 								}
 
-								new->argquery = read_sqlstmt(';', ";", "SELECT (");
+								new->argquery = read_sqlstmt(';', ";", "SELECT ");
+								/* Remove the trailing right paren,
+                                 * because we want "select 1, 2", not
+                                 * "select (1, 2)".
+								 */
+								cp = new->argquery->query;
+								cp += strlen(cp);
+								--cp;
+								if (*cp != ')')
+								{
+									plpgsql_comperrinfo();
+									elog(ERROR, "missing )");
+								}
+								*cp = '\0';
 							}
 							else
 							{

Responses

pgsql-patches by date

Next:From: Tom LaneDate: 2001-06-06 15:05:11
Subject: Re: Cursor support buffer patch
Previous:From: Ian Lance TaylorDate: 2001-06-06 06:39:44
Subject: Cursor support buffer patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group