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

In PostgreSQL (9.3) is there a simple way to get a list of the stored procedures that use a specific table?

在PostgreSQL(9.3)中有一种简单的方法来获取使用特定表的存储过程列表吗?

I'm changing several tables and need to fix the stored procedures that use them.

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

2 个解决方案

#1

Functions which have text 'thetable' in their body.

在其正文中包含文本“thetable”的函数。

The query returns function name, line number and line containg '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%';

Functions which have any argument or return value of type associated with thetable.

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

For example:

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

This query gives name, return type and types of arguments of the functions:

此查询提供函数的名称,返回类型和参数类型:

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, ' ')))

Of course, you can merge the queries into one. I am using them for different purposes.

当然,您可以将查询合并为一个。我将它们用于不同的目的。

#2

This task is not simple, because PostgreSQL has not any evidence about dependencies between functions and tables. What I know, there are not any public tool, that does it. One did Skype, but I am not sure if this tool was released outside Skype. If you know C, then you can modify plpgsql_check, where this information is available, but it is not used.

这个任务并不简单,因为PostgreSQL没有任何关于函数和表之间依赖关系的证据。我所知道的,没有任何公共工具可以做到。一个人做了Skype,但我不确定这个工具是否在Skype之外发布。如果您了解C,那么您可以修改plpgsql_check,其中此信息可用,但未使用。

There is poor solution - you can try to search specific string in source codes.

解决方案很糟糕 - 您可以尝试在源代码中搜索特定字符串。

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 are Regular Expression Constraint see related docs.

\ m \ M是正则表达式约束,请参阅相关文档。

#1