如何获取PostgreSQL中特定模式的数据库中存储的所有函数的列表？ [英] How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?

问题描述

我希望能够连接到PostgreSQL数据库并查找特定模式的所有函数。

I want to be able to connect to a PostgreSQL database and find all of the functions for a particular schema.

我的想法是，我可以查询一下pg_catalog或information_schema并获得所有函数的列表，但我无法确定名称和参数的存储位置。我正在寻找一个查询，它会给我函数名称和它需要的参数类型（以及它们带入的顺序）。

My thought was that I could make some query to pg_catalog or information_schema and get a list of all functions, but I can't figure out where the names and parameters are stored. I'm looking for a query that will give me the function name and the parameter types it takes (and what order it takes them in).

有没有办法做这个？

推荐答案

经过一番搜索，我能够找到 information_schema.routines 表和 information_schema.parameters 表。使用这些，可以为此构建查询。 LEFT JOIN，而不是JOIN，是检索不带参数的函数所必需的。

After some searching, I was able to find the information_schema.routines table and the information_schema.parameters tables. Using those, one can construct a query for this purpose. LEFT JOIN, instead of JOIN, is necessary to retrieve functions without parameters.

SELECT routines.routine_name, parameters.data_type, parameters.ordinal_position
FROM information_schema.routines
    LEFT JOIN information_schema.parameters ON routines.specific_name=parameters.specific_name
WHERE routines.specific_schema='my_specified_schema_name'
ORDER BY routines.routine_name, parameters.ordinal_position;

这篇关于如何获取PostgreSQL中特定模式的数据库中存储的所有函数的列表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！