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.

Improve this question
3
  • 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 Commented Aug 19, 2014 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. Commented Aug 19, 2014 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; Commented Aug 19, 2014 at 15:55

1 Answer 1

Reset to default
14

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 functions depending on an extension. 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:

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This other answer worked better for me.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.