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

Laurenz Albe · Accepted Answer · 2018-02-24 22:09:58Z

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.

edited Feb 24, 2018 at 22:09

answered Feb 24, 2018 at 21:58

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

clemens · Accepted Answer · 2018-02-26 06:35:52Z

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

Charlie · Accepted Answer · 2018-03-10 14:25:41Z

0

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.

answered Mar 10, 2018 at 14:25

Charlie

1052 silver badges10 bronze badges

Collectives™ on Stack Overflow

postgresql function return varying table structure

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related