Dynamic SQL using Prepared Cursors - IBM DB2 to PostgreSQL Migration

In DB2 you can use the prepared statements to dynamically (at runtime) define the SQL query for a cursor, for example:

IBM DB2:

  --#SET TERMINATOR /
 
  CREATE PROCEDURE sp_selectDept(IN p_deptno CHARACTER(5))
  RESULT SETS 1 
  LANGUAGE SQL
  BEGIN
    DECLARE query VARCHAR(50);
    DECLARE cur CURSOR WITH RETURN FOR s1;
    SET query = 'SELECT * FROM dept';  -- or some complex logic to dynamically build SQL here
    PREPARE s1 FROM query;
    OPEN cur;
  END
  /

In PostgreSQL you can use a REFCURSOR and OPEN FOR EXECUTE statement, the corresponding example:

PostgreSQL:

  CREATE OR REPLACE FUNCTION sp_selectDept(IN p_deptno CHARACTER(5)) 
  RETURNS REFCURSOR
  AS $$
  DECLARE 
    query VARCHAR(50);
    cur REFCURSOR;
  BEGIN
    query := 'SELECT * FROM dept';
    OPEN cur FOR EXECUTE query;
    RETURN cur;
  END;
  $$ LANGUAGE plpgsql;

For more examples, please see IBM DB2 to PostgreSQL Migration Reference