How to find all user-defined (non-extension) functions in PostgreSQL?

Question

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

How to find user-defined functions that do not belong to any extension, for example PostGIS? The answer to a related question was provided by a query that returns most PostGIS functions (noise for my purpose), and I did not understand it well enough to change it to only return my functions (lack of a detailed explanation of why it works the way it is and how to change the settings).

Now I do not have C functions, and all my functions are in a public scheme - you can use this fact, but let me know how to free these restrictions. If the exact list of extensions is important, suppose that now only PostGIS, but explain how to add others to the list if it is not self-evident from the code.

Answer 1

As @Craig comments, dependencies are stored in pg_catalog.pg_depend.

The request may look like this:

SELECT p.proname, pg_get_function_identity_arguments(p.oid) AS params
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
LEFT   JOIN pg_depend d ON d.objid = p.oid 
                       AND d.deptype = 'e'
WHERE  nspname = 'public'  -- your schema(s) of interest
AND    d.objid IS NULL;

This excludes all functions depending on the extension from the result.

Dependency Type Guidedeptype = 'e' :

DEPENDENCY_EXTENSION (e)

, (. pg_extension). DROP EXTENSION . , , pg_dump.

pg_get_function_identity_arguments():

• ( )