How to migrate oracle's pipelined function into PostgreSQL

Question

As I am newbie to plpgSQL,

I stuck while migrating a Oracle query into PostgreSQL.

Oracle query:

create or replace FUNCTION employee_all_case(
   p_ugr_id IN integer,
   p_case_type_id IN integer
)
RETURN number_tab_t PIPELINED
--       LANGUAGE 'plpgsql'
--     COST 100
--     VOLATILE 
--     AS $$
--     DECLARE
is
  l_user_id        NUMBER;
  l_account_id     NUMBER;
BEGIN
  l_user_id      := p_ugr_id;
  l_account_id   := p_case_type_id;
  FOR cases IN
    (SELECT ccase.case_id, ccase.employee_id
     FROM ct_case ccase
       INNER JOIN ct_case_type ctype
         ON (ccase.case_type_id=ctype.case_type_id)
     WHERE ccase.employee_id = l_user_id)
  LOOP
    IF cases.employee_id IS NOT NULL THEN
      PIPE ROW (cases.case_id);
    END IF;
  END LOOP;
  RETURN;
END;
--$$

When I execute this function then I get the following result

select * from table(select employee_all_case(14533,1190) from dual)

My question here is: I really do not understand the pipelined function and how can I obtain the same result in PostgreSQL as Oracle query ?

Please help.

Answer 1

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

Answer 2

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.