How to migrate oracle's pipelined function into PostgreSQL

Thank you guys, your solution was very helpful.

I found the desire result:

-- select * from employee_all_case(14533,1190);

-- drop function employee_all_case

  create or replace FUNCTION employee_all_case(p_ugr_id IN integer ,p_case_type_id IN integer)
returns table (case_id double precision)
  -- PIPELINED
   LANGUAGE 'plpgsql'
COST 100
 VOLATILE 
 AS $$
 DECLARE
 -- is
  l_user_id        integer;
  l_account_id     integer;
BEGIN
  l_user_id      := cp_lookup$get_user_id_from_ugr_id(p_ugr_id);
  l_account_id   := cp_lookup$acctid_from_ugr(p_ugr_id);
  RETURN QUERY SELECT ccase.case_id
    FROM ct_case ccase
    INNER JOIN ct_case_type ctype ON ccase.case_type_id = ctype.case_type_id
    WHERE ccase.employee_id = p_ugr_id
    and ccase.employee_id IS NOT NULL; 
    --return NEXT;
END;
$$

You would rewrite that to a set returning function:

• Change the return type to

  RETURNS SETOF integer
  

  and do away with the PIPELINED.

• Change the PIPE ROW statement to

  RETURN NEXT cases.case_id;
  

Of course, you will have to do the obvious syntactic changes, like using integer instead of NUMBER and putting the IN before the parameter name.

But actually, it is quite unnecessary to write a function for that. Doing it in a single SELECT statement would be both simpler and faster.

Pipelined functions are best translated to a simple SQL function returning a table.

Something like this:

create or replace function employee_all_case(p_ugr_id integer, p_case_type_IN integer)
   returns table (case_id integer)
as
$$
  SELECT ccase.case_id
  FROM ct_case ccase
     INNER JOIN ct_case_type ctype ON ccase.case_type_id = ctype.case_type_id
  WHERE ccase.employee_id = p_ugr_id
    and cases.employee_id IS NOT NULL;
$$
language sql;

Note that your sample code did not use the second parameter p_case_type_id.

Usage is also more straightforward:

select * 
from employee_all_case(14533,1190);

Before diving into the solution, I will provide some information which will help you to understand better. So basically PIPELINED came into picture for improving memory allocation at run time.

• As you all know collections will occupy space when ever they got created. So the more you use, the more memory will get allocated.

• Pipelining negates the need to build huge collections by piping rows out of the function.

• saving memory and allowing subsequent processing to start before all the rows are generated.

• Pipelined table functions include the PIPELINED clause and use the PIPE ROW call to push rows out of the function as soon as they are created, rather than building up a table collection.

By using Pipelined how memory usage will be optimized?

Well, it's very simple. instead of storing data into an array, just process the data by using pipe row(desired type). This actually returns the row and process the next row.

coming to solution in plpgsql

1. simple but not preferred while storing large data.

• Remove PIPELINED from return declaration and return an array of desired type. something like RETURNS typrec2[].

• Where ever you are using pipe row(), add that entry to array and finally return that array.

2. create a temp table like

CREATE TEMPORARY TABLE temp_table (required fields) ON COMMIT DROP;

and insert data into it. Replace pipe row with insert statement and finally return statement like

return query select * from temp_table

• **The best link for understanding PIPELINED in oracle [https://oracle-base.com/articles/misc/pipelined-table-functions]
• pretty ordinary for postgres reference [http://manojadinesh.blogspot.com/2011/11/pipelined-in-oracle-as-well-in.html]

Hope this helps some one conceptually.