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';