如何找到在哪里使用功能 [英] 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屋！