如何在PostgreSQL中获取特定模式数据库中存储的所有函数列表？

Question

我想要连接到一个PostgreSQL数据库，并找到特定模式下的所有函数。

我的想法是可以向pg_catalog或information_schema进行一些查询，并获取所有函数的列表，但我不知道函数名和参数存储在哪里。我正在寻找一种查询方法，可以给出函数名称和它所接受的参数类型（以及按什么顺序接受它们）。

有没有办法做到这一点？

- Rudd Zwolinski

Answer 1

\df <schema>.*

在 psql 中使用该命令将提供必要的信息。

要查看内部使用的查询，请使用 psql 连接到数据库并提供额外的 "-E"（或 "--echo-hidden"）选项，然后执行上述命令。

- Milen A. Radev

---

你能复制粘贴一下那个查询是什么吗？ - Rudd Zwolinski

选择 n.nspname 作为“模式”， p.proname 作为“名称”， pg_catalog.pg_get_function_result(p.oid) 作为“结果数据类型”， pg_catalog.pg_get_function_arguments(p.oid) 作为“参数数据类型”， CASE WHEN p.proisagg THEN '聚合' WHEN p.proiswindow THEN '窗口' WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '触发器' ELSE '正常' END 作为“类型” 从 pg_catalog.pg_proc p 中选择 左连接 pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname ~ '^(public)$' 按 1、2、4 排序;以上是生成的查询（来自 \set ECHO_HIDDEN 'on'）。 - Simon D

这种方法的问题在于查询是针对特定版本的Postgres生成的，可能会在其他版本上失败。例如，在Postgres 11上，您会收到“ERROR：column p.proisagg不存在”的错误信息。 - Code4R7

感谢提到 --echo-hidden，这确实非常有帮助。 - radiospiel

通过运行\c ma_database然后set search_path to my_schema;，命令\df就可以完成工作！ - bcag2

Answer 2

经过一些搜索，我找到了 information_schema.routines 表和 information_schema.parameters 表。利用这些表，可以构建一个查询来实现此目的。使用 LEFT JOIN 而不是 JOIN，可以检索没有参数的函数。

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;

- Rudd Zwolinski

---

你也会发现oidvectortypes非常有用。请查看新答案：https://dev59.com/bnM_5IYBdhLWcg3wgzdl#24034604 - Craig Ringer

以上代码将不会显示所有函数，您需要使用LEFT JOIN而不是JOIN来显示没有输入参数的函数。 - David

Answer 3

有一个方便的函数oidvectortypes，可以使这个过程变得更加容易。

SELECT format('%I.%I(%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes)) 
FROM pg_proc p INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)
WHERE ns.nspname = 'my_namespace';

感谢Leo Hsu和Regina Obe在Postgres Online上指出oidvectortypes。我之前也写过类似的函数，但使用了复杂的嵌套表达式，而这个函数可以消除这种需求。 请参见相关答案。

---

我是一名有用的助手，可以为您进行文本翻译。

（2016年编辑）

总结典型报告选项：

-- Compact:
SELECT format('%I.%I(%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes))

-- With result data type: 
SELECT format(
       '%I.%I(%s)=%s', 
       ns.nspname, p.proname, oidvectortypes(p.proargtypes),
       pg_get_function_result(p.oid)
)

-- With complete argument description: 
SELECT format('%I.%I(%s)', ns.nspname, p.proname, pg_get_function_arguments(p.oid))

-- ... and mixing it.

-- All with the same FROM clause:
FROM pg_proc p INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)
WHERE ns.nspname = 'my_namespace';

注意：使用p.proname||'_'||p.oid AS specific_name来获取唯一名称，或者与information_schema表连接 - 参见@RuddZwolinski的答案中的routines和parameters。

---

函数的OID（参见pg_catalog.pg_proc）和函数的specific_name（参见information_schema.routines）是函数的主要引用选项。以下是一些在报告和其他情境中有用的函数。

--- --- --- --- ---
--- Useful overloads: 

CREATE FUNCTION oidvectortypes(p_oid int) RETURNS text AS $$
    SELECT oidvectortypes(proargtypes) FROM pg_proc WHERE oid=$1;
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION oidvectortypes(p_specific_name text) RETURNS text AS $$
    -- Extract OID from specific_name and use it in oidvectortypes(oid).
    SELECT oidvectortypes(proargtypes) 
    FROM pg_proc WHERE oid=regexp_replace($1, '^.+?([^_]+)$', '\1')::int;
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION pg_get_function_arguments(p_specific_name text) RETURNS text AS $$
    -- Extract OID from specific_name and use it in pg_get_function_arguments.
    SELECT pg_get_function_arguments(regexp_replace($1, '^.+?([^_]+)$', '\1')::int)
$$ LANGUAGE SQL IMMUTABLE;

--- --- --- --- ---
--- User customization: 

CREATE FUNCTION pg_get_function_arguments2(p_specific_name text) RETURNS text AS $$
    -- Example of "special layout" version.
    SELECT trim(array_agg( op||'-'||dt )::text,'{}') 
    FROM (
        SELECT data_type::text as dt, ordinal_position as op
        FROM information_schema.parameters 
        WHERE specific_name = p_specific_name 
        ORDER BY ordinal_position
    ) t
$$ LANGUAGE SQL IMMUTABLE;

- Craig Ringer

---

“proname”是名称，但如何获取OID，例如在“pg_catalog.pg_get_function_result(oid)”中使用？ - Peter Krauss

@PeterKrauss pg_proc 表中的 oid 列是一个隐藏列。 - Craig Ringer

请参阅https://dev59.com/eYLba4cB1Zd3GeqPg5_V#25388031，了解如何排除与扩展相关的函数（例如来自PostGIS的函数）。 - Simon D

Answer 4

如果有人感兴趣，这里是在PostgreSQL 9.1上psql执行的查询：

SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE
  WHEN p.proisagg THEN 'agg'
  WHEN p.proiswindow THEN 'window'
  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
  ELSE 'normal'
 END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;

您可以通过在运行psql时使用-E标志来获取反斜杠命令的psql运行情况。

- jb.

---

刚刚看到了你的回答，并在Postgres 11.5上尝试了这个查询。它显示：“ERROR: column p.proisagg不存在”。 - Christiaan Westerbeek

谢谢你！两个得票最高的答案都没有展示我的函数！ - machineghost

@ChristiaanWesterbeek 这是针对小于11的情况，对于11+使用 p.prokind = 'a' 以进行聚合操作，使用 p.prokind = 'w' 以进行窗口操作。 - DiamondDrake

Answer 5

运行以下 SQL 查询以创建一个视图，该视图将显示所有函数：

CREATE OR REPLACE VIEW show_functions AS
    SELECT routine_name FROM information_schema.routines 
        WHERE routine_type='FUNCTION' AND specific_schema='public';

- laudarch

Answer 6

获取函数模式和函数名称的列表...

SELECT
    n.nspname AS function_schema,
    p.proname AS function_name
FROM
    pg_proc p
    LEFT JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE
    n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY
    function_schema,
    function_name;

- maddy

Answer 7

将函数命名为常用别名的想法是一个好主意，可以使用LIKE过滤名称。

以Postgresql 9.4中的public schema为例，请确保将其替换为所需的schema。

SELECT routine_name 
FROM information_schema.routines 
WHERE routine_type='FUNCTION' 
  AND specific_schema='public'
  AND routine_name LIKE 'aliasmyfunctions%';

- Alberto Morales Fernández

Answer 8

例子：

perfdb-# \df information_schema.*;

List of functions
        Schema      |        Name        | Result data type | Argument data types |  Type  
 information_schema | _pg_char_max_length   | integer | typid oid, typmod integer | normal
 information_schema | _pg_char_octet_length | integer | typid oid, typmod integer | normal
 information_schema | _pg_datetime_precision| integer | typid oid, typmod integer | normal
 .....
 information_schema | _pg_numeric_scale     | integer | typid oid, typmod integer | normal
 information_schema | _pg_truetypid         | oid     | pg_attribute, pg_type     | normal
 information_schema | _pg_truetypmod        | integer | pg_attribute, pg_type     | normal
(11 rows)

- Ritu

---

那跟Milen的回答有什么不同呢？ - user330315

这不是一个查询，而是psql Postgres客户端界面的命令。这只能在psql中使用，严格来说它并不是SQL查询语句。 - GregT

Answer 9

此函数返回当前数据库中所有用户定义的例程。

SELECT pg_get_functiondef(p.oid) FROM pg_proc p
INNER JOIN pg_namespace ns ON p.pronamespace = ns.oid
WHERE ns.nspname = 'public';

- Alex

Answer 10

上述答案中的连接不仅返回输入参数，还返回输出。因此，有必要指定parameter_mode。此选择将返回具有其输入参数列表（如果有）的函数列表。Postgres 14。

select r.routine_name, array_agg(p.data_type::text order by p.ordinal_position) from information_schema.routines r left join information_schema.parameters p on r.specific_name = p.specific_name
where r.routine_type = 'FUNCTION' and r.specific_schema = 'schema_name' and (p.parameter_mode = 'IN' or p.parameter_mode is null)
group by r.routine_name order by r.routine_name;

- Dorregaray