On Monday 10 Nov 2003 6:05 pm, Nigel J. Andrews wrote:
DECLARE
tup RECORD;
BEGIN
FOR tup IN select * from mytable
LOOP
Do the required action
END LOOP;
END
Indeed, I'm not even sure how to loop through the results of the query
using the scheme you show above. What do you assign the results of the
select to?
My working code which i think can be improved is below
note that i treat match=1 and match>1 differently.
I hope it will answer both of your question.
Thanks everyone for the responses though :)
Pl/Pgsql itself seems to be the most mature of all PL
hence we have decided to shift our business logic from
perl layer to DataBase for obvious gains.
-- Actual Code-------
CREATE OR REPLACE FUNCTION general.copy_accounts() returns integer AS
'
DECLARE
users_c CURSOR FOR SELECT userid FROM general.user_accounts ;
userid_v int;
i int;
matched int;
rec RECORD;
BEGIN
OPEN users_c;
i := 1;
LOOP
FETCH users_c INTO userid_v;
EXIT WHEN NOT FOUND ;
SELECT INTO matched count(*) from general.profile_master where userid=userid_v;
IF matched = 1 THEN
SELECT INTO rec email,title1 , fname1 , mname1 , lname1 , desg1 , mobile from general.profile_master where userid=userid_v;
ELSIF matched > 1 THEN
-- multiple profiles then get the profile that
-- has highest score.
SELECT INTO rec email,title1 , fname1 , mname1 , lname1 , desg1 , mobile,source
from general.profile_master join
general.temp_source_priority using(source) where userid=userid_v
order by profile_score(email,title1 , fname1 , mname1 , lname1 , desg1 , mobile) desc limit 1;
END IF;
IF matched >= 1 THEN
i := i + 1;
UPDATE general.user_accounts set
email= rec.email,
title= rec.title1 ,
fname= rec.fname1 ,
mname= rec.mname1 ,
lname= rec.lname1 ,
desg = rec.desg1 ,
mobile= rec.mobile where userid = userid_v;
END IF;
IF i % 100 = 0 THEN
RAISE INFO '' copied % accounts '' , i;
END IF;
END LOOP;
CLOSE users_c;
RAISE INFO '' Successfully finished with % accounts '' , i;
RETURN 1;
END
' LANGUAGE 'plpgsql';
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to
ma*******@postgresql.org so that your
message can get through to the mailing list cleanly