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

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