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