Re: PL/PGSQL help for getting number of rows matched.

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';