Обсуждение: how to use a cursor for update?
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!
=?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