How to write this function as a pL/pgSQl function ?

Question

I am trying to implement some business logic in a PL/pgSQL function.

I have hacked together some pseudo code that explains the type of business logic I want to include in the function.

Note: This function returns a table, so I can use it in a query like:

SELECT A.col1, B.col1 FROM (SELECT * from some_table_returning_func(1, 1, 2, 3) as A), tbl2 as B;

The pseudocode of the pl/PgSQL function is below:

CREATE FUNCTION some_table_returning_func(uid int, type_id int, filter_type_id int, filter_id int) RETURNS TABLE AS $$

  DECLARE
  where_clause text := 'tbl1.id = ' + uid;
  ret TABLE;

  BEGIN

  switch (filter_type_id)
  {
      case 1:
     switch (filter_id)
     {  
         case 1:
                where_clause += ' AND tbl1.item_id = tbl2.id AND tbl2.type_id = filter_id';
                break;

             //other cases follow ...
     }
     break;

      //other cases follow ...
   }

   // where clause has been built, now run query based on the type
   ret = SELECT [COL1, ... COLN] WHERE where_clause;

   IF (type_id <> 1) THEN
      return ret;
   ELSE
      return select * from another_table_returning_func(ret,123);
   ENDIF;

END;
$$ LANGUAGE plpgsql;

I have the following questions:

1. How can I write the function correctly to (i.e. EXECUTE the query with the generated WHERE clause, and to return a table

2. How can I write a PL/pgSQL function that accepts a table and an integer and returns a table (another_table_returning_func) ?

Answer 1

1.) You can return a table-like result with the SETOF clause: RETURNS SETOF tablename