Hi,
Michael Meskes írta:
>> 1. The statement
>>
>> UPDATE table SET fld1 = :input1
>> WHERE CURRENT OF :curname
>> RETURNING id + :input2;
>>
>> is transformed into
>>
>> UPDATE table SET fld1 = $1
>> WHERE CURRENT OF $0
>> RETURNING id + $2;
>>
>> and the $0 is past $1. The current code cannot deal with such
>> a messed up order, and scanning the original query twice is
>> needed, once for $0 substitution, once for mapping $1, etc. to
>> the other input variables.
>>
>
> I cannot seem to reproduce this bug. Could you please send me an example that
> makes this reproducible? Yes, I know that I have to change preproc.y to allow
> for variable cursor names but in my test case everything seems to work well
> and
> $0 gets replaced by the cursor name.
>
> Michael
>
sorry for the late answer. Here is a minimal patch against the
current GIT tree, so the WHERE CURRENT OF accepts
dynamic cursornames, plus the test case that shows the problem.
The problem is caused by line 25 in the attached source, the
UPDATE statement is processed into this code:
if (sqlca.sqlcode != 0)
break;
{ ECPGdo(__LINE__, 1, 1, NULL, 0, ECPGst_prepnormal,
"update t1 set t = $1 where current of $0 returning id + $2 ",
ECPGt_char,&(new_t),(long)0,(long)1,(1)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,&(curname),(long)0,(long)1,(1)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_int,&(one),(long)1,(long)1,sizeof(int),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT,
ECPGt_int,&(id1),(long)1,(long)1,sizeof(int),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
#line 25 "where-current-of.pgc"
if (sqlca.sqlcode < 0) sqlprint();}
#line 25 "where-current-of.pgc"
Running the program needs this table:
create table t1 (id serial primary key, t text);
and a few records in it. Result of running it:
$ ./where-current-of
SQL error: there is no parameter $0 on line 25
SQL error: current transaction is aborted, commands ignored until end of
transaction block on line 27
2 0 0 'x'
SQL error: current transaction is aborted, commands ignored until end of
transaction block on line 32
In the above code, the $1, $0, $2 order is correctly mirrored
in the order of the actual parameters.
The DELETE ... WHERE CURRENT OF ... RETURNING ...
grammar wouldn't cause such problem, $0 would be the first
in this case but what do you suggest solving for UPDATE?
Best regards,
Zoltán Böszörményi
--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/
diff -dcrp postgresql.orig/src/backend/parser/gram.y postgresql/src/backend/parser/gram.y
*** postgresql.orig/src/backend/parser/gram.y 2010-10-25 16:11:35.000000000 +0200
--- postgresql/src/backend/parser/gram.y 2010-10-27 14:07:10.000000000 +0200
*************** where_clause:
*** 8389,8395 ****
/* variant for UPDATE and DELETE */
where_or_current_clause:
WHERE a_expr { $$ = $2; }
! | WHERE CURRENT_P OF name
{
CurrentOfExpr *n = makeNode(CurrentOfExpr);
/* cvarno is filled in by parse analysis */
--- 8389,8395 ----
/* variant for UPDATE and DELETE */
where_or_current_clause:
WHERE a_expr { $$ = $2; }
! | WHERE CURRENT_P OF cursor_name
{
CurrentOfExpr *n = makeNode(CurrentOfExpr);
/* cvarno is filled in by parse analysis */
diff -dcrp postgresql.orig/src/interfaces/ecpg/preproc/ecpg.addons postgresql/src/interfaces/ecpg/preproc/ecpg.addons
*** postgresql.orig/src/interfaces/ecpg/preproc/ecpg.addons 2010-09-21 13:49:59.000000000 +0200
--- postgresql/src/interfaces/ecpg/preproc/ecpg.addons 2010-10-27 14:11:21.000000000 +0200
*************** ECPG: stmtViewStmt rule
*** 192,197 ****
--- 192,202 ----
output_simple_statement($1);
}
+ ECPG: where_or_current_clauseWHERECURRENT_POFcursor_name block
+ {
+ char *cursor_marker = $4[0] == ':' ? make_str("$0") : $4;
+ $$ = cat_str(2,make_str("where current of"), cursor_marker);
+ }
ECPG: CopyStmtCOPYopt_binaryqualified_nameopt_column_listopt_oidscopy_fromcopy_file_namecopy_delimiteropt_withcopy_options addon
if (strcmp($6, "to") == 0 && strcmp($7, "stdin") == 0)
mmerror(PARSE_ERROR, ET_ERROR, "COPY TO STDIN is not possible");
#include <stdio.h>
int main(void)
{
EXEC SQL BEGIN DECLARE SECTION;
char *curname = "mycur";
int id, one = 1, two = 2, id1, id2;
char *new_t = "updated";
char t[64];
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR SQLPRINT;
EXEC SQL CONNECT TO zozo;
EXEC SQL DECLARE :curname CURSOR FOR SELECT id, t FROM t1;
EXEC SQL OPEN :curname;
while (sqlca.sqlcode == 0)
{
EXEC SQL FETCH FROM :curname INTO :id, :t;
if (sqlca.sqlcode != 0)
break;
EXEC SQL UPDATE t1 SET t = :new_t WHERE CURRENT OF :curname
RETURNING id + :one INTO :id1;
EXEC SQL DELETE FROM t1 WHERE CURRENT OF :curname RETURNING id
+ :two INTO :id2;
printf("%d %d %d '%s'\n", id, id1, id2, t);
}
EXEC SQL CLOSE :curname;
EXEC SQL DISCONNECT ALL;
return 0;
}
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
