如何找到所有用户定义的（与扩展无关的功能）？ [英] How to find all user-defined (not extension-related) functions?

问题描述

有类似的问题，但是它是模棱两可的，被接受的答案表明该问题与我的。

There is a similar question, but it is ambiguous, and the accepted answer suggests that the question is slightly different from mine.

如何查找不属于任何扩展名（如PostGIS）的用户定义函数？链接问题的答案提供了一个查询，该查询返回了大多数PostGIS函数（出于我的目的而产生的噪音），并且我对它的理解不够深，无法更改为仅返回我的函数（缺少详细的解释，说明其工作方式以及工作方式更改设置）。

How to find user defined functions not belonging to any extension such as PostGIS? Linked question's answer provided a query that returns most of the PostGIS functions (noise for my purpose) and I didn't understand it well enough to change it to return only my functions (lack of detailed explanation why it works the way it does and how to change the settings).

现在我没有C函数，我的所有函数都在架构公共中-您可以使用此事实，但请明确说明如何发布这些约束。如果扩展的确切列表很重要，那么现在就假定PostGIS，但是如果代码中的内容不言自明，请说明如何将其他添加到列表中。

Right now I have no C functions and all my functions are in schema public - you can use this fact, but make it clear how to release these constraints. If exact list of extensions is important, assume just PostGIS for now, but explain how to add others to the list if it is not self-evident from the code.

推荐答案

如@Craig所述，依赖项存储在 pg_catalog.pg_depend 。

As commented by @Craig, dependencies are stored in pg_catalog.pg_depend.

查询看起来像这样（ Postgres 11 或更高版本）：

The query can look like this (Postgres 11 or later):

SELECT p.proname AS function_name
     , pg_get_function_identity_arguments(p.oid) AS parameter_list
     , pg_get_functiondef(p.oid) AS function_def  -- CREATE FUNCTION statement
FROM   pg_proc p
LEFT   JOIN pg_depend d ON d.objid = p.oid 
                       AND d.deptype = 'e'        -- would depend on extension
WHERE  p.pronamespace = 'public'::regnamespace    -- your schema(s) of interest
AND    d.objid IS NULL                            -- no such dependency
AND    p.prokind = 'f';                           -- only plain functions

这将根据结果的扩展名排除所有函数。 手册有关依赖类型 deptype ='e'：

This excludes all functions depending on an extension from the result. The manual about the dependency type deptype = 'e':

DEPENDENCY_EXTENSION（e）

DEPENDENCY_EXTENSION (e)

从属对象是扩展程序的成员，该扩展程序是
引用的对象（请参见 pg_extension ）。只能通过引用对象上的 DROP EXTENSION 删除依赖对象
。在功能上
与内部依赖项相同，但是为了清楚起见和简化pg_dump，将
分开。

The dependent object is a member of the extension that is the referenced object (see pg_extension). The dependent object can be dropped only via DROP EXTENSION on the referenced object. Functionally this dependency type acts the same as an internal dependency, but it's kept separate for clarity and to simplify pg_dump.

和 p.prokind ='f'将结果限制为纯函数。 手册：

And p.prokind = 'f' restricts the result to plain functions. The manual:

f 用于常规函数， p 用于过程， a 表示聚合函数，或者 w 表示窗口函数

f for a normal function, p for a procedure, a for an aggregate function, or w for a window function

这是Postgres 11中的新功能。对于 Postgres 10或更早版本，请使用：

That's new in Postgres 11. For Postgres 10 or older use instead:

SELECT ...
...
AND    NOT proisagg     -- no aggregate functions
AND    NOT proiswindow  -- no window functions

尚无程序。

查找 pg_get_function_identity_arguments（）和 pg_get_functiondef（） 中找到current / functions-info.html＃FUNCTIONS-INFO-CATALOG-TABLE rel = nofollow noreferrer>。相关：

Find pg_get_function_identity_arguments() and pg_get_functiondef() in the manual here. Related:

• 如何获取函数参数列表（以便删除函数）

• How to get function parameter lists (so I can drop a function)

这篇关于如何找到所有用户定义的（与扩展无关的功能）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！