According to SQL specifications: If "READ ONLY" is not specified in cursor declaration then for update is
implicit.
Anyway, even if i specify "for update" in the declare clause, behaviour is same.
DROP TABLE IF EXISTS test;
create table test (num int,num2 int );
insert into test values(1,100);
insert into test values(2,200);
insert into test values(3,300);
insert into test values(4,400);
insert into test values(5,500);
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM test ORDER BY num FOR UPDATE;
FETCH 2 FROM c;
UPDATE test SET num = 500 WHERE CURRENT OF c;
ERROR: cursor "c" is not a simply updatable scan of table "test"
SELECT * FROM test;
FETCH 2 FROM c;
COMMIT;
SELECT * FROM test;
FETCH 2 FROM c;
COMMIT;
Regards,
Dharmendra
www.enterprisedb.comOn 10/25/07, Simon Riggs <simon@2ndquadrant.com> wrote: On Thu, 2007-10-25 at 12:28 +0530, Dharmendra Goyal wrote:
> If a cursor is declared using "Order by" then it gives following
> error
> during updation of the cursor:
> ERROR: cursor "c" is not a simply updatable scan of table "test"
> Ex:
> DROP TABLE IF EXISTS test;
> create table test (num int,num2 int );
> insert into test values(1,100);
> insert into test values(2,200);
> insert into test values(3,300);
> insert into test values(4,400);
> insert into test values(5,500);
> BEGIN;
> DECLARE c CURSOR FOR SELECT * FROM test ORDER BY num;
> FETCH 2 FROM c;
> UPDATE test SET num = 500 WHERE CURRENT OF c;
> ERROR: cursor "c" is not a simply updatable scan of table "test"
> Comments for this...??
You haven't specified FOR UPDATE on the query in the DECLARE clause.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
