Thread: Detecting functions installed by an extension
Greetings!
How can I tell, using the system catalog or information schema, if a function/procedure was created by an extension as opposed to by the user (i.e. would be dropped if you performed "DROP EXTENSION")? So far I've looked at the `pg_extension` table, which lists the class IDs of configuration tables but doesn't mention procedures. I've looked at `pg_proc` but that doesn't seem to contain the information. I've also scanned over various other system catalogues but with no luck. Is this information available in one of the system catalogs? Does PostgreSQL itself track this information so that it can perform cleanup, or does it expect the extension to clean up after itself?
Thank you for your time,
Benjie.
On 17/06/18 10:05, Benjie Gillam wrote: > Greetings! > > How can I tell, using the system catalog or information schema, if a > function/procedure was created by an extension as opposed to by the user > (i.e. would be dropped if you performed "DROP EXTENSION")? So far I've > looked at the `pg_extension` table, which lists the class IDs of > configuration tables but doesn't mention procedures. I've looked at > `pg_proc` but that doesn't seem to contain the information. I've also > scanned over various other system catalogues but with no luck. Is this > information available in one of the system catalogs? Does PostgreSQL > itself track this information so that it can perform cleanup, or does it > expect the extension to clean up after itself? All dependencies are tracked in the system catalog pg_depend. If you do \set ECHO_HIDDEN on in psql and then \dx+ an_extension you can see what queries psql uses to get the information. That should put you well on your way to doing what you want. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On 17 June 2018 at 10:26, Vik Fearing <vik.fearing@2ndquadrant.com> wrote:
On 17/06/18 10:05, Benjie Gillam wrote:
> Greetings!
>
> How can I tell, using the system catalog or information schema, if a
> function/procedure was created by an extension as opposed to by the user
> (i.e. would be dropped if you performed "DROP EXTENSION")? So far I've
> looked at the `pg_extension` table, which lists the class IDs of
> configuration tables but doesn't mention procedures. I've looked at
> `pg_proc` but that doesn't seem to contain the information. I've also
> scanned over various other system catalogues but with no luck. Is this
> information available in one of the system catalogs? Does PostgreSQL
> itself track this information so that it can perform cleanup, or does it
> expect the extension to clean up after itself?
All dependencies are tracked in the system catalog pg_depend.
If you do \set ECHO_HIDDEN on in psql and then \dx+ an_extension you
can see what queries psql uses to get the information. That should put
you well on your way to doing what you want.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
It seems so obvious in retrospect! This works beautifully - thanks 🙏
Benjie.