Re: order by in cursor declaration does not allow update

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