由 John Doe 七月 4, 2023
摘要：使用 PostgreSQL 函数时，您需要注意不要引用非确定性的函数，因为函数之间的依赖关系在 PostgreSQL 系统表中没有得到很好的维护。
目录
介绍
以下示例显示了如何最终获得悬空函数：
CREATE OR REPLACE FUNCTION dep_func (INT)
RETURNS INT AS $$
BEGIN
RETURN $1 + 2;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION test_func(INT)
RETURNS INT AS $$
BEGIN
RETURN dep_func($1);
END;
$$LANGUAGE plpgsql;
DROP FUNCTION dep_func(int);
test_func函数悬空了。调用此函数时，将引发错误，如下所示：
SELECT test_func (5);
ERROR: function dep_func(integer) does not exist
LINE 1: SELECT dep_func($1)
HINT: No function matches ...
函数是如何存储在 PostgreSQL 中的？
由于函数在 PostgreSQL 中存储为字符串，并且对编写函数的语言没有限制，因此 PostgreSQL 无法分析代码以跟踪依赖关系。
在一般情况下，不可能找出函数的依赖对象；但是有限的（场景受限）解决方案是完全可行的，并且可能足以满足您的需求。
局限性
- 如果将依赖对象的名称指定为带引号的标识符，则失败（漏报）。
- 如果依赖对象的名称作为参数传递，则失败（漏报）。
- 如果在运行时从关系中读取依赖对象的名称，则失败（漏报）。
- 如果依赖对象的名称由标记组合而成，则失败（漏报）。
- 如果依赖对象的名称与文本一样存在，则失败（误报）。
- 如果多行注释中存在依赖对象的名称，则失败（误报）。
- 不考虑函数重载。
- 不考虑通过触发器调用的函数。
- 不考虑每个查询重写规则调用的函数。
- 不考虑查询重写规则的影响。
- 无法对用非解释性过程语言（如 C）编写的函数进行分析。
查找函数的直接依赖关系
WITH routines AS (
SELECT DISTINCT(lower(proname)) AS name, --#0
string_to_array( --#4
regexp_replace( --#3
regexp_replace( --#2
lower(pg_get_functiondef(oid)) --#1
, '--.*?\n', '', 'g')
, '\W+', ' ', 'g')
, ' ') AS objnames
FROM pg_proc
WHERE proowner = to_regrole(current_role)
AND prokind != 'a'
), routine_names AS (
SELECT DISTINCT(lower(proname)) AS name
FROM pg_proc
WHERE proowner = to_regrole(current_role)
ORDER BY 1
), relation_names AS (
SELECT DISTINCT(lower(relname)) AS name
FROM pg_class
WHERE relowner = to_regrole(current_role)
ORDER BY 1
)
SELECT
name AS "Your routine",
array_remove( --#8
array( --#7
SELECT unnest(objnames) --#5
INTERSECT --#6
SELECT name FROM routine_names
), name) AS "Dependent routines",
array(
SELECT unnest(objnames)
INTERSECT
SELECT name FROM relation_names
) AS "Dependent relations"
FROM routines;
实现原理
- #0 收集所有可能是调用者的函数名称。无论如何，我们无法正确处理重载函数，所以为了省去后面处理的麻烦使用
DISTINCT进行了去重；除了带引号的标识符之外，SQL 是不区分大小写的，所以为了简化以后的比较我们使用lower()统一转换为小写。 - #1 通过
pg_get_functiondef()函数获取 CREATE FUNCTION 或 CREATE PROCEDURE 命令的完整文本。同样，再次使用lower()转换为小写。 - #2 去除单行注释。请注意使用的是惰性的（非贪婪）
*?量词：通常的*量词，如果在这里使用，将删除第一个单行注释加上所有后续行！ - #3 将除字母、数字和
_以外的所有字符替换为空格。请注意+量词：它确保 2 个以上连续字符被删除并替换为 1 个空格符。 - #4 按空格拆分为数组；此数组会包含 SQL 关键词、文本、数字和标识符（包括函数名称）。
- #5 将数组转换为行集。
- #6 将引用对象列表和函数列表取交集；结果将仅包含函数名称。
- #7 将行集转换为数组。
- #8 由于输入是
CREATE FUNCTION f ...命令的完整文本，因此提取的函数名称显然会包含f自身;所以我们用array_remove()去除它。