Re: [HACKERS] order by in cursor declaration does not allow update

2007-10-25 Thread Tom Lane 
"Dharmendra Goyal" <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] order by in cursor declaration does not allow update

2007-10-25 Thread Simon Riggs 
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


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] order by in cursor declaration does not allow update

2007-10-25 Thread Dharmendra Goyal 
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 <[EMAIL PROTECTED]> 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
>
>

Re: [HACKERS] order by in cursor declaration does not allow update

2007-10-25 Thread Simon Riggs 
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


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[HACKERS] order by in cursor declaration does not allow update

2007-10-25 Thread Dharmendra Goyal 
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