Search Postgresql Archives

Re: Rows count in the cursor ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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;




--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux