Re: Rows count in the cursor ?

Hi Ivanmara,
There are two things about cursors you may need to remember ( I hope I
do remember fairly well not to error in this response :-) ).
1) A cursor is similar to a pointer to a given record, which means it's
"vision" is limited to the current record, and you move the cursor
forward by FETCH statement which means it may not really give the count
of records which satisfy the query on which it (the cursor) is based on.
2) When you open a cursor, it's bearing is before the first record of
the results set. If you call "GET DIAGNOSTICS i=ROW_COUNT" variable i
will have 0 (zero), you will need to FETCH from the cursor to move it to
the first record (each fetch moves the cursor by one record). After the
first FETCH the execution of "GET DIAGNOSTICS i=ROW_COUNT" after each
subsequent FETCH will give variable "i" a value of 1 provided the end of
result set has not been reached.

You may want to read more on cursors on the well written PostgreSQL
documentation.

Typical use of cursors may be something similar to the following.

DECLARE
_myRecord RECORD;
_curs1 REFCURSOR;
_sql_query TEXT;
BEGIN
_sql_query:='select * from classif order by id';
OPEN _curs1 FOR EXECUTE _sql_query;
LOOP
FETCH _curs1 INTO _myRecord;
EXIT WHEN NOT FOUND;
....

Allan.

Ivanmara wrote:
> Hello. how can i get count of the rows in cursor ?
>
> this code always return 0, but table is not empty:
>
> open curs for select * from classif order by id;
> GET DIAGNOSTICS i = ROW_COUNT;
>
>
>
>