17

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
Improve this question

2 Answers 2

Reset to default
41

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$;
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.
8

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
        ...
Improve this answer

2 Comments

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, 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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.