Re: Bug #668: cursors with params: mismatched parentheses;

Eduardo Rambo (rambo(at)portoweb(dot)com(dot)br) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
cursors with params: mismatched parentheses;

Long Description
cursors with parameters do not seems to work. Is that implemented?

I tried many differents ways and get the same result:

mismatched parentheses,

no matter what table or data type the cursor is based on.

I have a linux conectiva 5 (derrived of Red Hat 7.0), and
Postgres 7.2.1.

Sample Code
create table tab_teste (codigo int primary key ,campo varchar(100));

CREATE or replace FUNCTION fteste(int)
RETURNS numeric
AS '
DECLARE
curs3 CURSOR (key int) IS SELECT * from tab_teste where codigo = key;
reg tab_teste%ROWTYPE;
BEGIN

OPEN curs3(1);
FETCH curs3 INTO reg;
CLOSE curs3;
RETURN 0;
END;'
LANGUAGE 'plpgsql';

select fteste(11);

No file was uploaded with this report

pgsql-bugs(at)postgresql(dot)org writes:
> cursors with parameters do not seems to work. Is that implemented?

Sigh. They used to work, but I seem to have broken 'em with a
last-minute 7.2 fix. (Wish we had better regression tests for plpgsql.)
A fix against 7.2.* is attached.

regards, tom lane

*** src/pl/plpgsql/src/gram.y.orig Thu Nov 29 17:57:37 2001
--- src/pl/plpgsql/src/gram.y Tue May 21 14:50:18 2002
***************
*** 4,10 ****
* procedural language
*
* IDENTIFICATION
! * $Header: /cvsroot/pgsql/src/pl/plpgsql/src/gram.y,v 1.29 2001/11/29 22:57:37 tgl Exp $
*
* This software is copyrighted by Jan Wieck - Hamburg.
*
--- 4,10 ----
* procedural language
*
* IDENTIFICATION
! * $Header: /cvsroot/pgsql/src/pl/plpgsql/src/gram.y,v 1.29.2.1 2002/05/21 18:50:18 tgl Exp $
*
* This software is copyrighted by Jan Wieck - Hamburg.
*
***************
*** 1327,1343 ****
if (tok != '(')
{
plpgsql_error_lineno = yylineno;
! elog(ERROR, "cursor %s has arguments", $3->refname);
}

new->argquery = read_sql_stmt("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_error_lineno = yylineno;
--- 1327,1370 ----
if (tok != '(')
{
plpgsql_error_lineno = yylineno;
! elog(ERROR, "cursor %s has arguments",
! $3->refname);
}

+ /*
+ * Push back the '(', else read_sql_stmt
+ * will complain about unbalanced parens.
+ */
+ plpgsql_push_back_token(tok);
+
new->argquery = read_sql_stmt("SELECT ");
!
! /*
! * Now remove the leading and trailing parens,
! * because we want "select 1, 2", not
! * "select (1, 2)".
*/
cp = new->argquery->query;
!
! if (strncmp(cp, "SELECT", 6) != 0)
! {
! plpgsql_error_lineno = yylineno;
! elog(ERROR, "expected 'SELECT (', got '%s' (internal error)",
! new->argquery->query);
! }
! cp += 6;
! while (*cp == ' ') /* could be more than 1 space here */
! cp++;
! if (*cp != '(')
! {
! plpgsql_error_lineno = yylineno;
! elog(ERROR, "expected 'SELECT (', got '%s' (internal error)",
! new->argquery->query);
! }
! *cp = ' ';
!
! cp += strlen(cp) - 1;
!
if (*cp != ')')
{
plpgsql_error_lineno = yylineno;

I don't know what syntax uses parens for stored procedure cursors, but
we don't support it. Place the '1' in the cursor WHERE clause. You
can't pass it into the cursor using parens.

---------------------------------------------------------------------------

pgsql-bugs(at)postgresql(dot)org wrote:
> Eduardo Rambo (rambo(at)portoweb(dot)com(dot)br) reports a bug with a severity of 3
> The lower the number the more severe it is.
>
> Short Description
> cursors with params: mismatched parentheses;
>
> Long Description
> cursors with parameters do not seems to work. Is that implemented?
>
> I tried many differents ways and get the same result:
>
> mismatched parentheses,
>
> no matter what table or data type the cursor is based on.
>
> I have a linux conectiva 5 (derrived of Red Hat 7.0), and
> Postgres 7.2.1.
>
>
>
> Sample Code
> create table tab_teste (codigo int primary key ,campo varchar(100));
>
> CREATE or replace FUNCTION fteste(int)
> RETURNS numeric
> AS '
> DECLARE
> curs3 CURSOR (key int) IS SELECT * from tab_teste where codigo = key;
> reg tab_teste%ROWTYPE;
> BEGIN
>
> OPEN curs3(1);
> FETCH curs3 INTO reg;
> CLOSE curs3;
> RETURN 0;
> END;'
> LANGUAGE 'plpgsql';
>
> select fteste(11);
>
> No file was uploaded with this report
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026