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

经过一番搜索，我找到了 information_schema.routines表和 information_schema.parameters表。使用这些工具，可以为此目的构造查询。要检索没有参数的函数，必须使用左连接(而不是 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;

\df <schema>.*

在 psql提供必要的信息。

查看内部使用 psql连接到数据库的查询，并提供一个额外的“ -E”(或“ --echo-hidden”)选项，然后执行上述命令。

例如:

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)

如果有人感兴趣的话，这里是 psql在 postgres 9.1上执行什么查询:

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;

通过使用 -E标志运行 psql，您可以得到 psql为反斜杠命令运行的内容。

有一个很方便的函数，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';

感谢 在 Postgres 在线的 Leo Hsu 和 Regina Obe指出 oidvectortypes。我以前写过类似的函数，但是使用了复杂的嵌套表达式，这个函数不需要。

见相关答案。

-- 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';

函数的 < em > OID (参见 pg_catalog.pg_proc)和函数的 具体 _ 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;

是一个好主意命名的函数与公共别名上的第一个单词过滤的名称与 LIKE Postgreql 9.4中的公共模式示例，一定要用他的模式替换

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

运行 SQL 查询以创建一个显示所有函数的视图:

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

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

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

获取 function _ schema 和 function _ name 的列表..。

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;

上述答案中的连接不仅返回输入参数，还返回输出。因此，还需要指定 (参数模式)。这个 select 将返回带有输入参数的函数列表(如果有的话)。邮差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;