how to use a cursor for update?

From: nicola zandonà <nick(dot)zando(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: how to use a cursor for update?
Date: 2009-11-20 09:48:16
Message-ID: 4AFD8164-27B2-406F-BD9C-18FE81691A1C@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message A. Kretschmer 2009-11-20 09:54:20 Re: Allowing for longer table names (>64 characters)
Previous Message Lorenzo Allegrucci 2009-11-20 09:43:40 Strange performance degradation