postgresql function return varying table structure

Question

For Postgresql Functions (Stored Procedures):

I want a stored procedure to be able to return different table structures based on a parameter that is passed. Rough Example:

CREATE OR REPLACE FUNCTION getMyinfo(emp_id varchar, inc_status) 
RETURNS TABLE [don't know how to set this...???]
AS 
$$
BEGIN
  if inc_status = false
    then
        return select fname, lname, emp_age from emp_data 
                  where id = emp_id;
    else
        return select fname, lname, emp_age, rev_code, m_stat 
                  where id = emp_id;
    end if;
END
$$

Answer 1

You can define the function as RETURNS SETOF record, but then you'll have to specify the definition of the record in each query that uses the function, because the definition must be known at query parse time.

To avoid that, you could use json as return type.

Answer 2

Alternative solution: Your two possible return types are very similar. You might create a type containing all possible columns and returning that where all unneeded columns are set to NULL. This will avoid too complicate queries when calling your function:

CREATE TYPE result_type AS (
    fname TEXT;
    lname TEXT;
    emp_age INTEGER;
    rev_code TEXT;
    m_stat INTEGER;
);

CREATE OR REPLACE FUNCTION getMyinfo(emp_id varchar, inc_status) 
RETURNS result_type AS 
$$
BEGIN
  if inc_status = false
    then
        return select fname, lname, emp_age, 
        NULL AS rev_code, NULL AS m_stat from emp_data 
                  where id = emp_id;
    else
        return select fname, lname, emp_age, rev_code, m_stat 
                  where id = emp_id;
    end if;
END
$$

Answer 3

I'm going with returning the "max" data structure: in the result set, items "not supposed to be there" will be set to null. Since the calling code is in control of the optional parameter it can decide to ignore the null columns. The json idea is interesting but API consumers will expect standard DB result sets. I looked at "overloading" the API function (e.g. vary the function interface by parameter count) - but IMO I think that's a poor design concept: creates multiple code sets to maintain for essentially the same requirement. Thanks to all for the suggestions.