如何找到在哪里使用功能 [英] How to find where function is being used
问题描述
我有一个名为 func1
的函数。它在数据库中的某个地方使用,但我不知道在哪里。
I have a function named func1
. It's used somewhere in the database but I don't know where.
我编写了此查询来帮助我找到它的使用位置:
I wrote this query to help me find where it's being used:
select proname,prosrc
from pg_proc
where prosrc like '%func1%';
如何修改此查询以检查触发器是否使用 func1 $
How can I modify this query to check if triggers use func1
as well?
推荐答案
假设您知道这是一个触发函数(即 RETURNS TRIGGER
),应该这样做:
Assuming you know it's a trigger function (i.e. RETURNS TRIGGER
), this should do it:
SELECT tgname, tgrelid::regclass
FROM pg_trigger
WHERE tgfoid = 'func1'::regproc
如果 func1
已超载,您需要使用例如 tgfoid ='func1(text,text)':: regprocedure
。
If func1
is overloaded, you would need to use e.g. tgfoid = 'func1(text,text)'::regprocedure
.
但是通常,它也可能会出现在 pg_aggregate
或 pg_cast
中,或在视图定义,检查约束或其他十几个位置中,以及
But in general, it might also appear in pg_aggregate
, or pg_cast
, or in a view definition, or a check constraint, or a dozen other places, and you don't want to have to check them all.
您可以通过 pg_depend
,它跟踪数据库中的所有对象依赖性。例如:
You can get to the bottom of this via pg_depend
, which tracks all object dependencies in the database. For example:
SELECT classid::regclass
FROM pg_depend
WHERE refobjid = 'func1'::regproc
如果返回,例如 pg_attrdef
,那么您知道它在列默认值中使用。 pg_depend
中的其他字段将准确告诉您它是哪个表/列。请注意,从另一个函数进行的调用不被视为依赖项,因此您仍然需要检查 pg_proc.prosrc
。
If this returns e.g. pg_attrdef
, then you know it's used in a column default. The other fields in pg_depend
will tell you exactly which table/column it is. Note that a call from another function is not considered to be a dependency, so you still need to check pg_proc.prosrc
.
但是有一种更简单的方法来跟踪大多数依赖项:
But there's a simpler way to track down the majority of dependencies:
BEGIN;
DROP FUNCTION func1();
ROLLBACK;
如果使用 func1
,则 DROP
可能会失败,并且错误会告诉您确切的位置。
If func1
is being used, the DROP
will (probably) fail, and the error will tell you exactly where.
有了一个方便的shell:只需运行 pg_dump-仅适用于模式,并查看
func1
出现的位置。
Even easier, if you've got a shell handy: Just to run pg_dump --schema-only
and see where func1
turns up.
这篇关于如何找到在哪里使用功能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!