Cursor based records in PostgreSQL

Question

I'm trying to use cursors for a query that joins multiple tables. I've seen that for oracle there is a cursor based record. When I try the same for Postgres, it throws some error. How can I do the same in Postgres?

CREATE OR REPLACE FUNCTION avoidable_states()
RETURNS SETOF varchar AS
$BODY$
DECLARE
    xyz CURSOR FOR select * from address ad
                            join city ct on ad.city_id = ct.city_id;    
    xyz_row RECORD;
BEGIN   
    open xyz;

    LOOP
    fetch xyz into xyz_row;
        exit when xyz_row = null;
        if xyz_row.city like '%hi%' then
            return next xyz_row.city;               
        end if;
    END LOOP;
    close xyz;  
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Error I get is:

ERROR:  relation "xyz" does not exist
CONTEXT:  compilation of PL/pgSQL function "avoidable_states" near line 4

Erwin Brandstetter · Answer 1 · 2022-03-29T05:11:08.827

1. Implicit cursor

It's almost always better to use the implicit cursor of a FOR loop than to resort to a somewhat slower and unwieldy explicit cursor. I have written thousands of PL/pgSQL functions and only a hand full of times explicit cursors made any sense.

CREATE OR REPLACE FUNCTION avoidable_states()
  RETURNS SETOF varchar
  LANGUAGE plpgsql STABLE AS
$func$
DECLARE
    rec record;
BEGIN   
   FOR rec IN
      SELECT *
      FROM   address ad
      JOIN   city ct USING (city_id)
   LOOP
      IF rec.city LIKE '%hi%' THEN
         RETURN NEXT rec.city;               
      END IF;
   END LOOP;
END
$func$;

Aside: Nothing in the function would need volatility VOLATILE. Use STABLE.

2. Set-based approach

It's almost always better to use a set-based approach if possible. Use RETURN QUERY to return as set from a query directly.

CREATE OR REPLACE FUNCTION avoidable_states()
  RETURNS SETOF varchar
  LANGUAGE plpgsql STABLE AS
$func$
BEGIN   
   RETURN QUERY
   SELECT ct.city
   FROM   address ad
   JOIN   city    ct USING (city_id)
   WHERE  ct.city LIKE '%hi%';
END
$func$;

3. SQL function

For the simple case (probably a simplification), you might also use a simple SQL function or even just the query:

CREATE OR REPLACE FUNCTION avoidable_states()
  RETURNS SETOF varchar
  LANGUAGE sql STABLE AS
$func$
   SELECT ct.city
   FROM   address ad
   JOIN   city    ct USING (city_id)
   WHERE  ct.city LIKE '%hi%';
$func$;

I'm coming from an Oracle PL/SQL background, and I'm wondering whether there's a way to "hide" the implicit cursor in the `DECLARE` part of the function, then reference it only by cursor-name in the `FOR` loop? Granted that when your select-statement is only a few lines of code, it doesn't make much of a difference. But if the `SELECT` statement is 50 lines of code, then it's really distracting (for debugging purposes) to have to include SQL in your procedural code. — cartbeforehorse, Sep 06 '17 at 10:00

MatheusOl · Accepted Answer · 2014-03-12T11:47:29.550

7

Just use the RECORD type:

DECLARE
    ...
    cur_row RECORD;
BEGIN
    ...
    FETCH xyz INTO cur_row;
    EXIT WHEN NOT FOUND;
    IF cur_row.city LIKE 'CH%' THEN
        ...

edited Mar 12 '14 at 11:47

answered Mar 12 '14 at 00:45

MatheusOl

10,870
3
30
28

Thanks that worked. But, when in loop, how do i break out of the loop? say I used exit when cur_row = null ; it does not break out. I updated the original question with new code. – Zeus Mar 12 '14 at 01:14
1

@Zeus, I updated the answer to show what you want. See Erwin's answer, as it shows you some other (and generally better IMHO) ways of iterating over a query, although sometimes we do need a cursor, so... – MatheusOl Mar 12 '14 at 11:49

Cursor based records in PostgreSQL

2 Answers2

1. Implicit cursor

2. Set-based approach

3. SQL function

Linked