| 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: | Whole Thread | Raw Message | 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 |