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

PL/PGSQL help for getting number of rows matched.

From
Rajesh Kumar Mallah
Date:
Hi,

We need to implement following logic efficiently.

SELECT * from some_table where .... [ Query 1 ]

IF rows_matched = 1 THEN

    use the single row that matched.

ELSIF

    loop thru the results of [Query 1]

END IF;


Currently i am doing select count(*) for getting rows_matched
in the top and repeating the same query in both branches of IF
to get the data of matching rows.

I have tried GET DIAGNOSTICS ROW_COUNT but for
"SELECTS" if returns 0 or 1 based on matching

I am sure there exists better methods. Kindly post a link
to better documentation of pl/pgsql or point out section in
the standard docs that discuss this issue.

Regds
Mallah.




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

From
Pavel Stehule
Date:
Hello,

it isn't problem. You can write

SELECT INTO ....
IF FOUND THEN
 ...
END IF

or

SELECT INTO  ..
GET DIAGNOSTICS variable = ROW_COUNT;
IF variable > 0 THEN
  ...
END IF

You can see on
http://developer.postgresql.org/docs/postgres/plpgsql-statements.html#PLPGSQL-SELECT-INTO

Regards
Pavel


On Mon, 10 Nov 2003, Rajesh Kumar Mallah wrote:

>
> Hi,
>
> We need to implement following logic efficiently.
>
> SELECT * from some_table where .... [ Query 1 ]
>
> IF rows_matched = 1 THEN
>
>     use the single row that matched.
>
> ELSIF
>
>     loop thru the results of [Query 1]
>
> END IF;
>
>
> Currently i am doing select count(*) for getting rows_matched
> in the top and repeating the same query in both branches of IF
> to get the data of matching rows.
>
> I have tried GET DIAGNOSTICS ROW_COUNT but for
> "SELECTS" if returns 0 or 1 based on matching
>
> I am sure there exists better methods. Kindly post a link
> to better documentation of pl/pgsql or point out section in
> the standard docs that discuss this issue.
>
> Regds
> Mallah.
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>


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

From
"Nigel J. Andrews"
Date:
On Mon, 10 Nov 2003, Pavel Stehule wrote:

> Hello,
>
> it isn't problem. You can write
>
> SELECT INTO ....
> IF FOUND THEN
>  ...
> END IF
>
> or
>
> SELECT INTO  ..
> GET DIAGNOSTICS variable = ROW_COUNT;
> IF variable > 0 THEN
>   ...
> END IF
>
> You can see on
> http://developer.postgresql.org/docs/postgres/plpgsql-statements.html#PLPGSQL-SELECT-INTO

Probably sectino 37.7.4 of the docs ( in
http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html
) is a better source as shown lower.

>
> On Mon, 10 Nov 2003, Rajesh Kumar Mallah wrote:
>
> >
> > Hi,
> >
> > We need to implement following logic efficiently.
> >
> > SELECT * from some_table where .... [ Query 1 ]
> >
> > IF rows_matched = 1 THEN
> >
> >     use the single row that matched.
> >
> > ELSIF
> >
> >     loop thru the results of [Query 1]
> >
> > END IF;
> >
> >
> > Currently i am doing select count(*) for getting rows_matched
> > in the top and repeating the same query in both branches of IF
> > to get the data of matching rows.
> >
> > I have tried GET DIAGNOSTICS ROW_COUNT but for
> > "SELECTS" if returns 0 or 1 based on matching
> >
> > I am sure there exists better methods. Kindly post a link
> > to better documentation of pl/pgsql or point out section in
> > the standard docs that discuss this issue.

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?


--
Nigel Andrews


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

From
"Jaime Casanova"
Date:
Maybe you can use a for if there is only one row it will do the job just
like if there were many rows:

    FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order
LOOP
        a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE
''''''''''
                 || referrer_keys.key_string || '''''''''' THEN RETURN
''''''
                 || referrer_keys.referrer_type || ''''''; END IF;'';
    END LOOP;

_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail


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

From
Rajesh Kumar Mallah
Date:
On Monday 10 Nov 2003 5:38 pm, Pavel Stehule wrote:
> Hello,
>
> it isn't problem. You can write
>
> SELECT INTO ....
> IF FOUND THEN
>  ...
> END IF

I have *different* logic for match=1
and for match > 1 , so FOUND is not a
solution as manual says.


There is a special variable named FOUND of type boolean.
FOUND starts out false within each PL/pgSQL function.
It is set by each of the following types of statements:

Section 19.5.5
http://www.postgresql.org/docs/7.3/static/plpgsql-statements.html



>
> or
>
> SELECT INTO  ..
> GET DIAGNOSTICS variable = ROW_COUNT;
> IF variable > 0 THEN
>   ...
> END IF


Even this does not solve my problem.
See my actual code and the output.



-- *****************   CODE *******************
CREATE OR REPLACE FUNCTION general.copy_accounts() returns integer AS
'
DECLARE
        users_c CURSOR FOR SELECT userid FROM general.user_accounts where userid=46  ;
        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 rec  profile_id from general.profile_master where userid=userid_v;
                GET DIAGNOSTICS matched = ROW_COUNT;
                RAISE INFO ''matched = % '' , matched;

                SELECT INTO matched  count(*) from general.profile_master where userid=userid_v;
                RAISE INFO ''matched = % '' , matched;

                EXIT;

        END LOOP;
        CLOSE users_c;
        RETURN 1;
END
' LANGUAGE 'plpgsql';

-- ------  CODE ENDS


RESULTS BELOW:

tradein_clients=# SELECT copy_accounts();
INFO:  matched = 1
INFO:  matched = 3

Note that matched was 3 but in first place it did not come.

+---------------+
| copy_accounts |
+---------------+
|             1 |
+---------------+
(1 row)

Time: 386.76 ms
tradein_clients=#


Regds
Mallah.

>
> You can see on
> http://developer.postgresql.org/docs/postgres/plpgsql-statements.html#PLPGS
>QL-SELECT-INTO
>
> Regards
> Pavel
>
> On Mon, 10 Nov 2003, Rajesh Kumar Mallah wrote:
> > Hi,
> >
> > We need to implement following logic efficiently.
> >
> > SELECT * from some_table where .... [ Query 1 ]
> >
> > IF rows_matched = 1 THEN
> >
> >     use the single row that matched.
> >
> > ELSIF
> >
> >     loop thru the results of [Query 1]
> >
> > END IF;
> >
> >
> > Currently i am doing select count(*) for getting rows_matched
> > in the top and repeating the same query in both branches of IF
> > to get the data of matching rows.
> >
> > I have tried GET DIAGNOSTICS ROW_COUNT but for
> > "SELECTS" if returns 0 or 1 based on matching
> >
> > I am sure there exists better methods. Kindly post a link
> > to better documentation of pl/pgsql or point out section in
> > the standard docs that discuss this issue.
> >
> > Regds
> > Mallah.
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 7: don't forget to increase your free space map settings


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

From
Rajesh Kumar Mallah
Date:
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';


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

From
Rajesh Kumar Mallah
Date:
Once again ,

I have different logic based on match,
i need to know a prioi the number of matches.

See the actual code in response to Nigel .

Thanks for your response.

regds
mallah.

On Monday 10 Nov 2003 6:57 pm, Jaime Casanova wrote:
> Maybe you can use a for if there is only one row it will do the job just
> like if there were many rows:
>
>     FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order
> LOOP
>         a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE
> ''''''''''
>
>                  || referrer_keys.key_string || '''''''''' THEN RETURN
>
> ''''''
>
>                  || referrer_keys.referrer_type || ''''''; END IF;'';
>
>     END LOOP;
>
> _________________________________________________________________
> Add photos to your e-mail with MSN 8. Get 2 months FREE*.
> http://join.msn.com/?page=features/featuredemail
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org