Thread: Cursor

Xavier Bermeo wrote:
> Hi, guys.......
>  
> I have  problems with cursosrs.
>  
> Anyone have an example complete the how  load and read each position 
> of a cursor?
>  
> I wait your answer........
>  
> Thanks.......guys

Assuming you're using ECPG, there are a couple of examples in the 
documentation, e.g.,

http://www.postgresql.org/docs/8.3/static/ecpg-commands.html(see Select using cursors:

http://www.postgresql.org/docs/8.3/static/ecpg-variables.html#AEN33442

The essence is you DECLARE the cursor, OPEN it and then FETCH rows in 
some kind of loop, and finish by CLOSE cursor.  To exit the loop, you 
can check sqlca.sqlcode for 100 (NOT FOUND) or a SQLSTATE of 02000.

Joe

> De: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org
<mailto:pgsql-sql-owner@postgresql.org>] En nombre de Xavier Bermeo
> Enviado el: Sábado, 16 de Agosto de 2008 14:54
> Para: pgsql-sql@postgresql.org
> Asunto: [SQL] Cursor
>
> Hi, guys.......
> I have  problems with cursosrs.
> Anyone have an example complete the how  load and read each position of a
cursor?
> I wait your answer........
> Thanks.......guys


Hi Xavier. This is a very simple example of a function with an implicit
cursor.
Regards,
Fernando.


CREATE OR REPLACE FUNCTION f_cursor() RETURNS void AS
$BODY$
DECLARE     idx INTEGER;a mytable.col1%TYPE;b mytable.col2%TYPE;     c mytable.col3%TYPE;     d mytable.col4%TYPE;

BEGIN   idx :=0;
   FOR a, b, c, d IN   SELECT col1, col2, col3, col4     FROM mytable    ORDER BY col1 ;
   LOOP      -- Comment: Every iteration in loop will read a row from the cursor       idx := idx + 1;      raise
notice'Row %: [%, %, %, %]', idx, a, b, c, d;   END LOOP;   -- Comment: all rows have been read 
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;