Cursor based records in PostgreSQL

by

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$;

More Related Contents:

  1. Loop on tables with PL/pgSQL in Postgres 9.0+
  2. Loop through columns of RECORD
  3. What are ‘$$’ used for in PL/pgSQL
  4. INSERT with dynamic table name in trigger function
  5. The forgotten assignment operator “=” and the commonplace “:=”
  6. Truncating all tables in a Postgres database
  7. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  8. How to set value of composite variable field using dynamic SQL
  9. How do I do large non-blocking updates in PostgreSQL?
  10. Execute a dynamic crosstab query
  11. Is there a way to disable function overloading in Postgres
  12. Can I make a plpgsql function return an integer without using a variable?
  13. PostgreSQL function returning multiple result sets
  14. Dynamic SQL (EXECUTE) as condition for IF statement
  15. Returning from a function with OUT parameter
  16. SELECT raises exception in PL/pgSQL function
  17. Return SETOF rows from PostgreSQL function
  18. Error when setting n_distinct using a plpgsql variable
  19. Postgres FOR LOOP
  20. Dynamically generate columns for crosstab in PostgreSQL
  21. Function to loop through and select data from multiple tables
  22. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  23. SQL Sub queries in check constraint
  24. Iterating over integer[] in PL/pgSQL
  25. ERROR: unterminated quoted string at or near
  26. How can I send some http request from postgresql function or trigger
  27. Select from a table variable
  28. Hashing a String to a Numeric Value in PostgreSQL
  29. plpgsql error “RETURN NEXT cannot have a parameter in function with OUT parameters” in table-returning function
  30. Format specifier for integer variables in format() for EXECUTE?

Leave a Comment