Fetching rows from a cursor PostgreSQL

Question

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!

I can't see how what you want differs from SELECT * FROM cliente; it may be because I don't usderstand ms-sql-server — Jasen
– Jasen, Commented Jan 18, 2015 at 0:40
It is actually the same. With the difference I want the table to be shown through a cursor. — Mike Pérez
– Mike Pérez, Commented Jan 18, 2015 at 0:43
why do you want to use a cursor? all it's going to do is slow the query down. inline plpgsql runs in a transaction same as plpgsql functions. — Jasen
– Jasen, Commented Jan 18, 2015 at 0:51
How would I use the loop @Jasen ? Thanks for your answer. I have to use cursors in PLPGSQL to do the same as the SQL server block of sentences — Mike Pérez
– Mike Pérez, Commented Jan 18, 2015 at 1:01

sparkhee93 · Accepted Answer · 2018-07-12 22:19:46Z

7

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.

edited Jul 12, 2018 at 22:19

sparkhee93

1,3753 gold badges21 silver badges31 bronze badges

answered Jan 18, 2015 at 0:45

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Pérez Over a year ago

Awesome & clear explanation @Jasen. Thanks for your quick help!

Evgeny Nozdrev · Accepted Answer · 2018-07-19 08:26:11Z

1

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)

answered Jul 19, 2018 at 8:26

Evgeny Nozdrev

1,56613 silver badges15 bronze badges

Collectives™ on Stack Overflow

Fetching rows from a cursor PostgreSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related