Search code examples
sqlpostgresqlfunctionjoinplpgsql

Wrap PostgreSQL functions in another to conditionally combine results

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)

Solution

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

    In a PL/pgSQL 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$;

    fiddle

    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.

    Related:

    Alternatively, this CTE wrapped in a simple SQL function works in any case, subset or not (can be a PL/pgSQL 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$;

    fiddle

    Related: