如何在PostgreSQL中使用特定表获取存储过程列表？

Question

在PostgreSQL（9.3）中，有没有一种简单的方法可以获取使用特定表的存储过程列表？

我正在更改几个表格，并需要修复使用它们的存储过程。

- Alan Wayne

---

https://dev59.com/FHI_5IYBdhLWcg3wBuX3 - Greg

@Greg 问题是，我只需要使用特定表的过程--而不是数据库中所有过程的列表？我需要手动阅读每个过程吗？ - Alan Wayne

不知道，很抱歉。 - Greg

Answer 1

带有“thetable”文本的函数。

该查询返回函数名称、行号以及包含“thetable”的行：

select *
from (
    select proname, row_number() over (partition by proname) as line, textline
    from (
        select proname, unnest(string_to_array(prosrc, chr(10))) textline
        from pg_proc p
        join pg_namespace n on n.oid = p.pronamespace
        where nspname = 'public'
        and prosrc ilike '%thetable%'
        ) lines
    ) x
    where textline ilike '%thetable%';

具有与thetable相关联类型的任何参数或返回值的函数。

例如：

create function f2(rec thetable)...
create function f1() returns setof thetable... 

这个查询会给出函数的名称、返回类型以及参数类型：

with rtype as (
    select reltype 
    from pg_class
    where relname = 'thetable')
select distinct on (proname) proname, prorettype, proargtypes
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
cross join rtype
where nspname = 'public'
and (
    prorettype = reltype 
    or reltype::text = any(string_to_array(proargtypes::text, ' '))) 

当然，你可以将这些查询合并成一个。我在使用它们时有不同的目的。

- klin

---

@kiln 非常感谢您，您刚刚为我节省了数小时的时间 :) - Alan Wayne

Answer 2

这项任务并不简单，因为PostgreSQL没有任何关于函数和表之间依赖关系的证据。据我所知，没有任何公共工具可以完成这项任务。Skype曾经开发过一个类似的工具，但我不确定是否已在Skype以外发布。如果你懂C语言，那么你可以修改plpgsql_check，从中获取这些信息，但目前还未被使用。

有一个较差的解决方案，即尝试在源代码中搜索特定的字符串。

postgres=# CREATE OR REPLACE FUNCTION foo()
RETURNS int AS $$
BEGIN
  RETURN (SELECT a FROM t1);
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION

postgres=# SELECT oid::regprocedure FROM pg_proc WHERE prosrc ~ '\mt1\M';
┌───────┐
│  oid  │
╞═══════╡
│ foo() │
└───────┘
(1 row)

\m \M是正则表达式的约束条件，详见相关文档。

- Pavel Stehule

---

所以我猜测是将文本转储到文件中，然后手动搜索表名？ - Alan Wayne

而要搜索完整的函数定义，包括头文件（其中表名可能会弹出作为类型名称），请使用 pg_get_functiondef()。详情请见：http://stackoverflow.com/a/22191178/939860（当用作头文件中的行类型时，存在对系统中类型的依赖关系）。 - Erwin Brandstetter

@AlanWayne，不需要 - 你可以在存储源代码的表中进行搜索。 - Pavel Stehule