Wrap PostgreSQL functions in another to conditionally combine results

Question

I have a function get_oversight(int) that returns a single column:

person_id
----------
100
101
102
103
104

And another function get_unfiltered_responsibility(int) that returns the same structure:

person_id
----------
100
103
104

I need a 3rd function that evaluates and returns a subset of the above. Here's some pseudo code:

def function get_responsibility(person_id int):
    oversight = get_oversight(person_id)
    unfiltered_responsibility = get_responsibility(person_id)

    if number_of_records(unfiltered_responsibility) == 0:
        return oversight
    else
        return intersection(unfiltered_responsibility, oversight)
        # only ids from unfiltered_responsibility that are ALSO IN oversight

What would that 3rd function look like? (using v9.6)

I described it as pseudo-code... just wanted to describe the logic I am looking for. — Matt Graham, Sep 24 '19 at 03:02

Erwin Brandstetter · Accepted Answer · 2019-09-26T23:07:56.613

Assuming that both functions never return duplicates. Else you'll have to define exactly how to deal with those.

In a plpgsql function you can conveniently use the special variable FOUND

CREATE OR REPLACE FUNCTION get_combo_plpgsql(int)
  RETURNS TABLE(person_id int) LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT *
   FROM   get_oversight($1)
   JOIN   get_unfiltered_responsibility($1) USING (person_id);

   IF NOT FOUND THEN
      RETURN QUERY
      SELECT * FROM get_oversight($1);
   END IF;
END
$func$;

This only works on the assumption that get_unfiltered_responsibility() always returns a subset of get_oversight(), like your sample data seems to suggest. Then, if the join returns no rows, we can conclude that get_unfiltered_responsibility() came up empty.

Why is IS NOT NULL false when checking a row type?

Alternatively, this CTE wrapped in a simple SQL function works in any case, subset or not (can be a plpgsql function too, if needed):

CREATE OR REPLACE FUNCTION get_combo_sql(int)
  RETURNS TABLE(person_id int) LANGUAGE sql AS
$func$
WITH cte AS (SELECT * FROM get_unfiltered_responsibility($1))
SELECT *
FROM   get_oversight($1) o
WHERE  EXISTS (
   SELECT FROM cte
   WHERE  person_id = o.person_id
   )
OR NOT EXISTS (TABLE cte)
$func$;

Is there a shortcut for SELECT * FROM?

db<>fiddle here

Wrap PostgreSQL functions in another to conditionally combine results

1 Answers1