how to use a cursor for update?

Started by nicola zandonàover 16 years ago2 messagesgeneral
Jump to latest
#1nicola zandonà
nick.zando@gmail.com

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!

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: nicola zandonà (#1)
Re: how to use a cursor for update?

=?ISO-8859-1?Q?nicola_zandon=E0?= <nick.zando@gmail.com> writes:

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

The example works for me, using a simple definition of the "zone"
table. Maybe your "zone" is not a table but a complicated view?

Or it could be a bug that's been fixed. What PG version are you using?

regards, tom lane