order by in cursor declaration does not allow update
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"
SELECT * FROM test;
FETCH 2 FROM c;
COMMIT;
SELECT * FROM test;
FETCH 2 FROM c;
COMMIT;
Comments for this...??
Regards,
Dharmendra
www.enterprisedb.com
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
Show quoted text
On 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
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