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_ac counts() returns integer AS
'
DECLARE
users_c CURSOR FOR SELECT userid FROM general.user_ac counts ;
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_so urce_priority using(source) where userid=userid_v
order by profile_score(e mail,title1 , fname1 , mname1 , lname1 , desg1 , mobile) desc limit 1;
END IF;
IF matched >= 1 THEN
i := i + 1;
UPDATE general.user_ac counts 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*******@postg resql.org so that your
message can get through to the mailing list cleanly