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!
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 |