8

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

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Pavel V.
  • 2,653
  • 10
  • 43
  • 74
  • You can only distinguish your functions from those created by an extension if you install the extension in a separate schema. Otherwise I don't think there is a way to tell the difference –  Aug 19 '14 at 15:33
  • 2
    @a_horse_with_no_name There must be - because `DROP EXTENSION` works, and extensions don't have to supply uninstall SQL files. I haven't specifically checked, but I'd say it'll be in `pg_depend`. – Craig Ringer Aug 19 '14 at 15:45
  • 1
    @a_horse_with_no_name ... yup. e.g. `select e.oid, e.extname, d.* from pg_extension e inner join pg_depend d on d.refclassid = 'pg_extension'::regclass and d.refobjid = e.oid;` – Craig Ringer Aug 19 '14 at 15:55

1 Answers1

12

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

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

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

DEPENDENCY_EXTENSION (e)

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.

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

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

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

There were no procedures, yet.

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228