How to write this function as a pL/pgSQl function ?
- by morpheous
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:
How can I write the function correctly to (i.e. EXECUTE the query with the generated WHERE clause, and to return a table
How can I write a PL/pgSQL function that accepts a table and an integer and returns a table (another_table_returning_func) ?
