Fetching rows from a cursor PostgreSQL

I have the cursor code:

BEGIN;
DECLARE cliente_cursor 
CURSOR FOR SELECT * FROM cliente;

I want to read all the content from the Table 'cliente':

Table cliente

With the use of a cursor. I have the code working for SQL Server:

DECLARE cliente_cursor CURSOR
      FOR SELECT * FROM cliente
OPEN cliente_cursor
FETCH NEXT FROM cliente_cursor;
While @@FETCH_STATUS=0
BEGIN
     FETCH NEXT FROM cliente_cursor;
End
CLOSE cliente_cursor
DEALLOCATE cliente_cursor

And I want to have a working code for PostgreSQL.

I have been looking for a solution & seen people usually suggest using functions. I wonder if there is any way in this PostgreSQL DBMS to create something similar to the code in SQL Server.

I had written this code:

CREATE OR REPLACE FUNCTION MyFunction()
RETURNS setof cliente AS $$
DECLARE 
cursor_cliente CURSOR FOR SELECT * FROM cliente;
rec cliente%ROWTYPE;
 BEGIN
 OPEN cursor_cliente;
loop
--fetch the table row inside the loop
FETCH cursor_cliente INTO rec;
-- check if there is no record
   --exit from loop when record not found
   if not found then
        exit ;
   end if;
end loop;
RETURN;
END;
$$ LANGUAGE plpgsql;

But when I run it, I only get:

select MyFunction();

Any idea what should the code be instead?

Results

Any help would be appreciated a lot!

CREATE OR REPLACE FUNCTION foo() RETURNS setof cliente 
   language plpgsql AS $$
DECLARE
  x cliente%rowtype ;
BEGIN 
  FOR x IN SELECT * FROM cliente loop
    RETURN NEXT x;
  END loop;
END $$;

SELECT * FROM foo();

it can also be done with an explicit cursor.

CREATE OR REPLACE FUNCTION foo() RETURNS setof cliente
  language plpgsql as $$
DECLARE 
  x cliente%rowtype ;
  cliente_cursor CURSOR FOR SELECT * FROM cliente; 
BEGIN
  FOR x IN cliente_cursor loop
    RETURN NEXT x;
  END loop;
END $$;

SELECT * FROM foo();

The function is going to persist so either give it a useful name and keep it around or drop it after you are done.

If you want a private name for the function pg_temp.foo will be private to your session.

If you want just to return all rows from the query, use

RETURN QUERY
SELECT ...

and RETURNS TABLE(column1 type1, column2 type2, ...) as function's type.

Or for cursor:

RETURN QUERY
FETCH ALL FROM cliente_cursor;

To do something with each row, use

FOR _record IN
  SELECT ...
LOOP
  <action1>;
  <action2>;
  ...
END LOOP;

from this answer.

Or for cursor:

FOR _record IN
  FETCH ALL FROM ...
LOOP
  <action1>;
  <action2>;
  ...
END LOOP;

for cursor.

Note that PostgreSQL has refcursor type which allows you to use a cursor by it text name. IMHO it is simplest way (read more)

Fetching rows from a cursor PostgreSQL

Related

Recent Posts