Re: order by in cursor declaration does not allow update

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.com

On 10/25/07, Simon Riggs <simon(at)2ndquadrant(dot)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
>
>