looping through cursor results

Question

I am trying to loop through a cursor(plpgsql) result,but somehow nothing is being printed on output console.

create or replace function curs() returns refcursor as 
$body$
declare
    curs cursor for select id from stores;
    store stores.id%TYPE;
begin
    open curs;
    fetch curs into store;
    loop
    exit when not found;
        raise notice 'Value: %',store;
    end loop;
    close curs;
end
$body$ language plpgsql;

select curs();

How do I implement a proper loop?

Database Version : 9.0 Table stores with columns id,name

This question should also provide the table definition and your Postgres version. — Erwin Brandstetter, Oct 29 '15 at 13:11
But that's not a table definition. A table definition is a complete `CREATE TABLE` script showing data types and constraints, or what you get with `\d tbl` in psql. — Erwin Brandstetter, Oct 29 '15 at 13:23

score 2 · Accepted Answer · edited May 23 '17 at 12:30

First of all, your function does not return anything, you just produce notices. In pgAdmin, those would be output in the "Messages" pane, not in the "Data Output" pane.

I assume you want to actually return values ...
But typically, you don't need an explicit cursor to loop. Use the more convenient implicit cursor of a FOR loop:

CREATE OR REPLACE FUNCTION test_loop()
  RETURNS SETOF int AS 
$func$
DECLARE
   _id int;  -- assuming data type integer
BEGIN
   FOR _id IN
      SELECT id FROM stores ORDER BY id
   LOOP
      RETURN NEXT _id;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Note the call syntax:

SELECT * FROM test_loop();

Typically, you don't even need a loop. Just plain SQL ...

CREATE OR REPLACE FUNCTION test_loop1()
  RETURNS SETOF int AS 
$func$
BEGIN
   RETURN QUERY
   SELECT id FROM stores ORDER BY id;
END
$func$  LANGUAGE plpgsql;

Which can be simplified to an SQL function:

CREATE OR REPLACE FUNCTION test_loop2()
  RETURNS SETOF int AS 
$func$
   SELECT id FROM stores ORDER BY id;
$func$  LANGUAGE sql;

Related answers with more details and explanation:

Yes.The first example works. But what if I don't want to use a loop? how can I then read the values individually and return them? — Pradeep, Oct 29 '15 at 15:33

looping through cursor results

1 Answers1