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

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