将PostgreSQL函数封装在另一个函数中，以便有条件地组合结果

Question

我有一个函数get_oversight(int)，它返回一个列：

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

以及返回相同结构的另一个函数get_unfiltered_responsibility(int)：

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

我需要第三个函数来计算和返回上面的子集。下面是一些伪代码：

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

第三个功能是什么样子的？(使用9.6节)

Answer 1

假设这两个函数都不返回重复项。否则，您将必须确切地定义如何处理这些问题。

在plpgsql函数中，您可以方便地使用特殊变量发现。

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

这个只使用的假设，即get_unfiltered_responsibility()总是返回get_oversight()的子集，就像您的示例数据所显示的那样。然后，如果联接不返回任何行，我们可以得出结论，get_unfiltered_responsibility()是空的。

相关信息：

• 为什么在检查行类型时不为NULL false？

或者，包装在简单的CTE SQL函数中的在任何情况下都可以工作，子集或不工作(如果需要的话，也可以是plpgsql函数)：

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

相关信息：

• 从…?

db<>fiddle https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=d0d2f4701defe018c956b089ff06543d