From: | Vyacheslav Kalinin <vka(at)mgcp(dot)com> |
---|---|
To: | Andreas Kretschmer <akretschmer(at)spamfence(dot)net> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: plpgsql: help with RECORD type |
Date: | 2009-03-03 21:19:12 |
Message-ID: | 9b1af80e0903031319m123f1f39r3f1886c7ba467970@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
There is no set variables in plpgsql. If you want to retrieve set of rows
for futher manipulation either use a CURSOR or ARRAY:
declare
cur refcursor;
begin
open cur for
select * from table where ...;
loop
fetch cur into var1,var2...;
exit when not found;
end loop;
declare
arr offsite_batch[];
i int;
begin
select array(
select * from offsite_batch where ...
) into arr;
for i in 1..array_upper(arr,1)
loop
var1 := arr[i].id;
...
end loop;
Note that ARRAYs of composite type only appeared in 8.3.
I believe what you want to achieve can be done with simple query result
looping:
for batch in (
select * from offsite_batch
where closed is NULL
order by age(opened) ASC
) loop
batch_found := true;
IF NOT unique THEN
RAISE WARNING 'more than one open batch found, closing...';
UPDATE offsite_batch
SET closed = now()
WHERE batch_id = batch.id;
ELSE
latest_batch := batch;
unique := FALSE;
END IF;
end loop;
if not batch_found then
RAISE NOTICE 'creating new offsite batch';
...
end if;
From | Date | Subject | |
---|---|---|---|
Next Message | Semyon Chaichenets | 2009-03-03 21:51:23 | Re: plpgsql: help with RECORD type |
Previous Message | Andreas Kretschmer | 2009-03-03 20:58:25 | Re: plpgsql: help with RECORD type |