how to use a cursor for update?

[nicola zandonà <nick.zando@xxxxxxxxx>]

Hi all,

I'm trying to create a stored procedure using a cursor to udate a  
table this is my code:


CREATE OR REPLACE FUNCTION test_select() RETURNS void AS
$BODY$

DECLARE
	cur CURSOR FOR SELECT *				
	    	   FROM zone
		   WHERE patient = '4595'
		   FOR UPDATE;
	var1 record;
	var2 record;
BEGIN
	OPEN cur;					
		
	FETCH  cur INTO var1;				
	       	   					
	FETCH cur INTO var2;

	UPDATE zone SET end_period = var1.end_period WHERE CURRENT OF cur;
		
END;
$BODY$
LANGUAGE 'plpgsql';


When i try to execute this code i get a :

ERROR:  cursor "cur" is not a simply updatable scan of table "zone"
CONTEXT:  SQL statement "UPDATE zone SET end_period =  $1  WHERE  
CURRENT OF  $2 "
PL/pgSQL function "test_select" line 16 at SQL statement

How can i use that cursor for update?

Tnx in advance!

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general