PIPELINED Functions - Oracle to PostgreSQL Migration

In Oracle you can have a pipelined table function that can return multiple records and can be used in the FROM clause:

Oracle:

  -- Define the record type
  CREATE TYPE t_record AS OBJECT (
    id   NUMBER(7),
    name VARCHAR2(50)
  );
  /
 
  -- Define the table type
  CREATE TYPE t_table IS TABLE OF t_record;
  /
 
  -- Pipelined function returning multiple rows
  CREATE OR REPLACE FUNCTION get_rows (p_rows IN NUMBER) 
    RETURN t_table PIPELINED AS
  BEGIN
    FOR i IN 1 .. p_rows LOOP
      PIPE ROW(t_record(i, CHR(i + 64)));   
    END LOOP;
  END;
  /

Then you can call this function and get rows as follows:

Oracle:

  SELECT * FROM TABLE(get_rows(7)); 
 
  #  Result:
  #  ID     NAME
  # ------ --------------------------------------------------
  #       1 A
  #       2 B
  #       3 C
  #       4 D
  #       5 E
  #       6 F
  #       7 G
  #  7 rows selected.

In PostgreSQL, you can use SETOF return type for a function, and RETURN NEXT statement:

PostgreSQL:

  -- Record type
  CREATE TYPE t_record AS (
    id   INT,
    name VARCHAR(50)
  );
 
  CREATE OR REPLACE FUNCTION get_rows (p_rows IN INT) 
    RETURNS SETOF t_record 
  AS $$
  BEGIN
    FOR i IN 1 .. p_rows LOOP
      RETURN NEXT ROW(i, CAST(CHR(i + 64) AS VARCHAR(50)));   
    END LOOP;
  END;
  $$ LANGUAGE plpgsql;

Now you can call this function as follows:

PostgreSQL:

  SELECT  get_rows(7);
 
  # Result:
  # get_rows
  # ----------
  # (1,A)
  # (2,B)
  # (3,C)
  # (4,D)
  # (5,E)
  # (6,F)
  # (7,G)
  #(7 rows)

For more information, see Oracle to PostgreSQL Migration.