SQL 列出在 PostgreSQL 中引用表的存储函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1559008/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
List stored functions that reference a table in PostgreSQL
提问by Paolo B.
Just a quick and simple question: in PostgreSQL, how do you list the names of all stored functions/stored procedures using a table using just a SELECT statement, if possible? If a simple SELECT is insufficient, I can make do with a stored function.
只是一个快速而简单的问题：在 PostgreSQL 中，如果可能，您如何使用仅使用 SELECT 语句的表列出所有存储函数/存储过程的名称？如果一个简单的 SELECT 是不够的，我可以用一个存储的函数来凑合。
My question, I think, is somewhat similar to this other question, but this other question is for SQL Server 2005:
List of Stored Procedure from Table
我认为我的问题有点类似于另一个问题，但另一个问题是针对 SQL Server 2005:
List of Stored Procedure from Table
(optional) For that matter, how do you also list the triggers and constraints that use the same table in the same manner?
（可选）就此而言，您如何还以相同的方式列出使用同一表的触发器和约束？
回答by Quassnoi
SELECT p.proname
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p
ON p.pronamespace = n.oid
WHERE n.nspname = 'public';
回答by davidwhthomas
SELECT proname, prosrc
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p
ON pronamespace = n.oid
WHERE nspname = 'public';
回答by dayer4b
If you are using psql, try \df
如果您使用的是 psql，请尝试 \df
From the man page:
从手册页：
Tip
To look up functions taking arguments or returning values of a specific type, use your pager's search capability to scroll through the \df output.
Running \set ECHO_HIDDENwill reveal what \dfis running behind the scenes.
运行\set ECHO_HIDDEN将揭示\df幕后运行的内容。
回答by Matt Dressel
Same as @quassnoi and @davidwhthomas, except I added the argument names in there:
与@quassnoi 和@davidwhthomas 相同，除了我在那里添加了参数名称：
SELECT proname, proargnames, prosrc
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p
ON pronamespace = n.oid
WHERE nspname = 'public';
If the purpose behind listing the functions is to clean them up or iterate a new function with a changing params list, you will frequently need to drop functions:
如果列出函数的目的是清理它们或使用不断变化的参数列表迭代新函数，您将经常需要删除函数：
DROP FUNCTION <name>(<args>);
By adding proargnames, I am able to construct the applicable function name for the drop.
通过添加 proargnames，我能够为 drop 构造适用的函数名称。
Additionally, it's nice to see a more complete picture when evaluating the functions.
此外，很高兴在评估函数时看到更完整的画面。
回答by Luká? Lalinsky
You can use the standard information_schemaschema to get metadata about your database (it's in the SQL standard, so it should work the same way in different database systems). In this case you want information_schema.routines.
您可以使用标准information_schema架构来获取有关您的数据库的元数据（它在 SQL 标准中，因此在不同的数据库系统中应该以相同的方式工作）。在这种情况下，您想要information_schema.routines.
回答by Micha? Niklas
Have a look at my recipe. It reads functions and triggers. It is based on informations from: Extracting META information from PostgreSQL (INFORMATION_SCHEMA)
看看我的食谱。它读取函数和触发器。它基于以下信息：从 PostgreSQL (INFORMATION_SCHEMA) 中提取 META 信息
回答by windyjonas
Excluding the system stuff:
排除系统内容：
select proname from pg_proc where proowner <> 1;
回答by storm_m2138
For retrieving the argument typesof the functions, which are required when referencing the function in ALTER-- using oldevectortypesworked well for me.
为了检索在ALTER 中引用函数时需要的函数的参数类型——使用oldevectortypes对我来说效果很好。
See How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?
回答by Govind Gupta
Please change the schema_name and table_name in the below query:
请在以下查询中更改 schema_name 和 table_name：
SELECT n.nspname AS schema_name
, p.proname AS function_name
, pg_get_function_arguments(p.oid) AS args
, pg_get_functiondef(p.oid) AS func_def
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
AND n.nspname = 'schema_name'
AND p.prosrc like '%table_name%'
Since the table name is case sensitive, so need to define the exact table name.
由于表名区分大小写，所以需要定义准确的表名。
