How to detect if view was created by an extension?

Question

In PostgreSQL on how can one know whether a specific view was created by an extension?

What SQL query must be executed to find out? No manual solutions.

Don't think this is possible without some kind of auditing table. — Jacob H, Jan 26 '18 at 16:18
As stated in the question (and now also highlighted in bold) )I am looking for a SQL query. No manual inspections. The solution (if there is one) will most likely involve the information_schema. — Axel Fontaine, Jan 26 '18 at 16:55

Laurenz Albe · Answer 1 · 2018-01-26T17:04:35.783

0

Check if the view shows up in \dx+ output in psql -E.

This will also show the queries that psql uses to get the result, which will help you construct a query.

edited Jan 26 '18 at 17:04

answered Jan 26 '18 at 16:27

Laurenz Albe

209,280
17
206
263

This does not answer the question. I specifically asked for a SQL query. – Axel Fontaine Jan 26 '18 at 16:55
I have extended the answer; maybe that helps. – Laurenz Albe Jan 26 '18 at 17:05

score 0 · Answer 2 · answered Jan 28 '18 at 15:01

Axel Fontaine pays attention to what Laurenz Albe says

with \ dx + output in psql -E. I got the following query (replace pg_stat_statements by your extension ):

SELECT c.relname FROM pg_catalog.pg_depend join pg_class c  on (c.oid=pg_depend.objid) WHERE refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass AND deptype = 'e'  AND refobjid = ( SELECT  e.oid FROM pg_catalog.pg_extension e WHERE e.extname='pg_stat_statements') and c.relkind='v' ORDER BY 1;

;-)

How to detect if view was created by an extension?

2 Answers2