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

How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?

提问by Rudd Zwolinski

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

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

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).

我的想法是我可以对 pg_catalog 或 information_schema 进行一些查询并获得所有函数的列表，但我无法弄清楚名称和参数的存储位置。我正在寻找一个查询，它会给我函数名称和它采用的参数类型（以及它采用的顺序）。

Is there a way to do this?

有没有办法做到这一点？

采纳答案by Rudd Zwolinski

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

经过一番搜索，我找到了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;

回答by Milen A. Radev

\df <schema>.*

in psqlgives the necessary information.

inpsql给出了必要的信息。

To see the query that's used internally connect to a database with psqland supply an extra "-E" (or "--echo-hidden") option and then execute the above command.

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

回答by jb.

If any one is interested here is what query is executed by psqlon postgres 9.1:

如果有人对此感兴趣，那就是psqlpostgres 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;

You can get what psqlruns for a backslash command by running psqlwith the -Eflag.

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

回答by Craig Ringer

There's a handy function, oidvectortypes, that makes this a lot easier.

有一个方便的功能，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';

Credit to Leo Hsu and Regina Obe at Postgres Onlinefor pointing out oidvectortypes. I wrote similar functions before, but used complex nested expressions that this function gets rid of the need for.

感谢狮子座Hsu和里贾纳OBE在网上的Postgres的指出oidvectortypes。我之前写过类似的函数，但使用了复杂的嵌套表达式，这个函数不需要。

See related answer.

见相关回答。

---

---

(edit in 2016)

（2016年编辑）

Summarizing typical report options:

总结典型的报告选项：

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

NOTICE: use p.proname||'_'||p.oid AS specific_nameto obtain unique names, or to JOIN with information_schematables — see routinesand parametersat @RuddZwolinski's answer.

注意：使用p.proname||'_'||p.oid AS specific_name以获得唯一的名称，或加入information_schema表-看routines和parameters在@ RuddZwolinski的回答。

---

---

The function's OID(see pg_catalog.pg_proc) and the function's specific_name(see information_schema.routines) are the main reference options to functions. Below, some useful functions in reporting and other contexts.

函数的 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=;
$$ 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(, '^.+?([^_]+)$', '')::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(, '^.+?([^_]+)$', '')::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;

回答by laudarch

Run below SQL query to create a view which will show all functions:

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

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

回答by Alberto Morales Fernández

Is a good idea named the functions with commun alias on the first words for filtre the name with LIKEExample with public schema in Postgresql 9.4, be sure to replace with his scheme

LIKE在 Postgresql 9.4 中用公共模式过滤名称的第一个单词上使用公共别名命名函数是个好主意，一定要替换为他的方案

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

回答by Ritu

Example:

例子：

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)

回答by maddy

Get List of function_schema and function_name...

获取 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;

回答by Alex

This function returns all user defined routines in current database.

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

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