Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE OR REPLACE FUNCTION get_oversight(int)  -- minimal dummy for test
  RETURNS TABLE(person_id int) LANGUAGE sql AS
$func$
VALUES
 (100)
,(101)
,(102)
,(103)
,(104)
$func$;

CREATE OR REPLACE FUNCTION get_unfiltered_responsibility(int)  -- minimal dummy for test
  RETURNS TABLE(person_id int) LANGUAGE sql AS
$func$
SELECT *
FROM (
   VALUES
    (100)
   ,(103)
   ,(104)
   ) t
WHERE $1 <> 2
$func$;
SELECT * FROM get_oversight(1);


person_id


100

101

102

103

104


SELECT * FROM get_unfiltered_responsibility(1);


person_id


100

103

104


SELECT * FROM get_unfiltered_responsibility(2); -- no rows
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$;
SELECT * FROM get_combo_plpgsql(1);


person_id


100

103

104


-- no unfiltered_responsibility, return all from oversight
SELECT * FROM get_combo_plpgsql(2);


person_id


100

101

102

103

104


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$;
SELECT * FROM get_combo_sql(1);


person_id


100

103

104


 -- no unfiltered_responsibility, return all from oversight
 SELECT * FROM get_combo_sql(2);


person_id


100

101

102

103

104