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