Re: Cursors

On Sun, 24 Aug 2003, Michael Guerin wrote:

> I'm trying to convert a sql server stored procedure into a postgresql
> function and I seem to get stuck on converting the cursor.
>
> sql server snipet
> DECLARE @x int, @y int
> DECLARE rs CURSOR FOR
> SELECT x,y from foo;
> OPEN rs
> FETECH NEXT FROM rs into @x, @y
> WHILE (@@FETCH_STATUS =0)
>    BEGIN
>       --do something
>       FETECH NEXT FROM rs into @x, @y
>    END
> CLOSE rs
> DEALLOCATE rs
> ...
>
> --------------------------
> PostgreSQL ???
> x int;
> y int;
> DECLARE rs CURSOR FOR
> SELECT x,y from foo;
> OPEN rs
> FETCH NEXT FROM rs into :x, :y;
> While (FOUND) loop
>    --do something
>    FETCH NEXT FROM rs into :x, :y;
> END LOOP
> CLOSE rs;
>
>
> It complains about an error near FETCH?

I get an error at or around "x" so I assume this
isn't the function body you're actually trying.

I'd probably say something like:
CREATE OR REPLACE FUNCTION f2() returns void as'
DECLARE
 r record;
 rs CURSOR FOR SELECT x,y from foo;
BEGIN
 OPEN rs;
 FETCH rs into r;
 While (FOUND) loop
    --do something (using r.x and r.y)
    RAISE NOTICE ''% %'', r.x, r.y;
    --
    FETCH rs into r;
 END LOOP;
 CLOSE rs;
 RETURN;
END;' language 'plpgsql';