使用SQL查询在PostgreSQL中获取函数、序列、类型等的定义

Question

我需要创建PostgreSQL数据库对象的脚本。

我没有访问pg_dump的权限，因此必须使用SQL查询获取所有内容。我该怎么做？

- John

---

有关单个函数，请参见\ef，它可以“编辑CREATE FUNCTION命令形式的函数”（http://www.postgresql.org/docs/9.3/static/app-psql.html）。 - Jared Beck

Answer 1

要获取函数的定义，请使用pg_get_functiondef()：

select pg_get_functiondef(oid)
from pg_proc
where proname = 'foo';

有类似的函数可以检索索引、视图、规则等的定义，详情请参见手册：http://www.postgresql.org/docs/current/static/functions-info.html

获取用户类型的定义有点棘手。你需要查询 information_schema.attributes：

select attribute_name, data_type
from information_schema.attributes
where udt_schema = 'public'
  and udt_name = 'footype'
order by ordinal_position;

你需要重新组装create type语句。

要了解更多细节，你需要阅读系统目录的文档：http://www.postgresql.org/docs/current/static/catalogs.html

但如果information_schema视图返回相同的信息，则应优先使用它们。

- user330315

---

谢谢回复。我在数据库中有大约8种用户类型，但是当我查看information_schema.attributes时，发现没有任何行。有什么想法吗？ - John

@John: 不知道。你能发一下这些类型的定义吗？要获取更多详细语句，请按照 Erwin 的建议启动 psql，并使用 -E 开关查看它正在使用哪些语句。最有可能涉及 pg_type 和 pg_attribute。 - user330315

Answer 2

在你探索查询的过程中，你会发现psql -E非常有用。
它显示了psql执行反斜杠命令时使用的查询语句-比如\df+ myfunc以获取有关此函数的详细信息。

- Erwin Brandstetter

Answer 3

以下是使用 pg_get_functiondef 的完整示例查询：

WITH funcs AS (
  SELECT
    n.nspname AS schema
    ,proname AS sproc_name
    ,proargnames AS arg_names
    ,t.typname AS return_type
    ,d.description
    ,pg_get_functiondef(p.oid) as definition
  FROM pg_proc p
    JOIN pg_type t on p.prorettype = t.oid
    JOIN pg_description d on p.oid = d.objoid
    JOIN pg_namespace n on n.oid = p.pronamespace
  WHERE n.nspname = 'some_schema_name_here'
)
SELECT *
FROM funcs
;;

请注意，你显然需要指定模式名称（如果你使用该模式，则为“public”）。

- StartupGuy

---

注意，我更喜欢这种格式，因为我可以使用此查询来搜索包含特定文本的函数描述（我的基本函数文档）或函数定义中的文本。 - Michael M

Answer 4

您也可以在psql中使用\sf来查看用户定义的函数。

- Sachin Telang

Answer 5

SELECT
    pp.proname,                                     --function name.
    pp.pronamespace::regnamespace::text AS schema,  --function located schema
    pg_get_functiondef(oid),                        --function def
    pg_get_function_arguments(oid),                 --(including default values).
    pg_get_function_identity_arguments(oid),        --This form omits default values.
    pg_get_function_result(oid)                     --Reconstructs the RETURNS clause of a function
FROM
    pg_proc pp
WHERE
    proname = 'eval_safe';

来自手册

pg_get_functiondef ( func oid ) → text   

重构函数或过程的创建命令。（这是反编译的重构，而不是命令的原始文本。）结果是一个完整的CREATE OR REPLACE FUNCTION或CREATE OR REPLACE PROCEDURE语句。

pg_get_function_arguments ( func oid ) → text

重构函数或过程的参数列表，使其符合在CREATE FUNCTION中出现的形式（包括默认值）。

pg_get_function_identity_arguments ( func oid ) → text

重构参数列表，以识别函数或过程所需的形式，该形式需要出现在诸如ALTER FUNCTION之类的命令中。此形式省略了默认值。

pg_get_function_result ( func oid ) → text

重构函数的RETURNS子句，以在CREATE FUNCTION语句中正确呈现。对于过程返回NULL。

- jian