Thread: order by in cursor declaration does not allow update

If a cursor is declared using "Order by" then it gives following error <br /> during updation of the cursor: <br />
ERROR: cursor "c" is not a simply updatable scan of table "test" <br /> Ex: <br /> DROP TABLE IF EXISTS test;<br />
createtable test (num int,num2 int );<br /> insert into test values(1,100);<br /> insert into test values(2,200);<br />
insertinto test values(3,300);<br /> insert into test values(4,400);<br /> insert into test values(5,500);<br /> BEGIN;
<br/> DECLARE c CURSOR FOR SELECT * FROM test ORDER BY num; <br /> FETCH 2 FROM c; <br /> UPDATE test SET num = 500
WHERECURRENT OF c; <br /> ERROR:  cursor "c" is not a simply updatable scan of table "test" <br /> SELECT * FROM test;
<br/> FETCH 2 FROM c; <br /> COMMIT; <br /> SELECT * FROM test; <br /> FETCH 2 FROM c; <br /> COMMIT;<br /><br />
Commentsfor this...??<br /><br /> Regards,<br /> Dharmendra<br /><a
href="http://www.enterprisedb.com">www.enterprisedb.com</a><br/> 

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

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 Thu, 2007-10-25 at 16:53 +0530, Dharmendra Goyal wrote:
> According to SQL specifications: If "READ ONLY" is not specified in cursor declaration then for update is
> implicit.

Though that isn't what the PostgreSQL docs say.

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com

"Dharmendra Goyal" <dharmendra.goyal@gmail.com> writes:
> 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"

This is not a bug.  (See also quote from SQL92 in the other thread.)
        regards, tom lane