WITH named_objects AS (
SELECT
n.oid,
''::text AS container_name,
'DATABASE' AS container_type,
n.nspname AS object_name,
'SCHEMA' AS object_type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (n.nspname='public' OR a.rolname<>'postgres')
UNION SELECT
c.oid,
n.nspname AS container_name,
'SCHEMA' AS container_type,
c.relname AS object_name,
CASE WHEN c.relkind='r' THEN 'BASE TABLE'
WHEN c.relkind='S' THEN 'SEQUENCE GENERATOR'
WHEN c.relkind='v' THEN 'VIEW'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW'
WHEN c.relkind='c' THEN 'COMPOSITE TYPE'
WHEN c.relkind='f' THEN 'FOREIGN TABLE'
WHEN c.relkind='p' THEN 'PARTITIONED TABLE'
WHEN c.relkind='I' THEN 'PARTITIONED INDEX'
END AS object_type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_class AS c ON n.oid=c.relnamespace
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND relkind NOT IN ('t','i')
UNION SELECT
c.oid,
n.nspname AS container_name,
'SCHEMA' AS container_type,
c.relname AS object_name,
'INDEX' AS object_type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_class AS c ON n.oid=c.relnamespace
INNER JOIN pg_index AS i ON i.indexrelid=c.oid
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND i.indisprimary = FALSE
AND i.indisexclusion =FALSE
UNION ALL SELECT
pt.oid,
n.nspname AS container_name,
'SCHEMA' AS container_type,
pt.typname AS object_name,
CASE WHEN pt.typtype='d' THEN 'DOMAIN'
WHEN pt.typtype='e' THEN 'ENUMERATION TYPE'
WHEN pt.typtype='r' THEN 'RANGE TYPE' END AS object_type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_type AS pt ON n.oid=pt.typnamespace
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND pt.typtype NOT IN ('b','c')
UNION SELECT
o.oid,
n.nspname || '.' || c.relname AS container_name,
CASE WHEN c.relkind='r' THEN 'BASE TABLE'
WHEN c.relkind='v' THEN 'VIEW'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW'
WHEN c.relkind='f' THEN 'FOREIGN TABLE'
WHEN c.relkind='p' THEN 'PARTITIONED TABLE'
ELSE 'TABLE' END AS container_type,
conname AS object_name,
CASE WHEN o.contype='p' THEN 'PRIMARY KEY'
WHEN o.contype='u' THEN 'UNIQUE'
WHEN o.contype='f' THEN 'FOREIGN KEY'
WHEN o.contype='c' THEN 'TABLE CHECK'
WHEN o.contype='x' THEN 'EXCLUDE'
WHEN o.contype='t' THEN 'CONSTRAINT TRIGGER' END AS object_type
FROM pg_constraint o INNER JOIN pg_class c ON o.conrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (nspname='public' OR rolname<>'postgres')
UNION SELECT
NULL AS oid,
domain_schema || '.' || domain_name AS container_name,
'DOMAIN' AS container_type,
constraint_name AS object_name,
'DOMAIN CHECK' AS object_type
FROM INFORMATION_SCHEMA.domain_constraints
INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name)
WHERE domain_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
AND cc.check_clause NOT LIKE '%IS NOT NULL'
UNION ALL SELECT
pc.oid,
n.nspname AS container_name,
'SCHEMA' AS container_type,
pc.proname AS object_name,
CASE WHEN pc.prokind='f' THEN 'FUNCTION'
WHEN pc.prokind='p' THEN 'PROCEDURE'
WHEN pc.prokind='a' THEN 'AGGREGATE FUNCTION'
WHEN pc.prokind='w' THEN 'WINDOW FUNCTION' END AS object_type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_proc AS pc ON n.oid=pc.pronamespace
WHERE (nspname='public' OR rolname<>'postgres')
UNION SELECT
translate(substring(specific_name,'_[0-9]+$'),'_','')::int::oid,
specific_schema || '.' || regexp_replace(specific_name,'_[0-9]*$','') AS container_name,
'ROUTINE' AS container_type,
parameter_name AS object_name,
'PARAMETER' AS object_type
FROM information_schema.parameters
WHERE specific_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) AND parameter_name IS NOT NULL
UNION SELECT
tr.oid,
n.nspname || '.' || c.relname AS container_name,
CASE WHEN c.relkind='r' THEN 'BASE TABLE'
WHEN c.relkind='v' THEN 'VIEW'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW'
WHEN c.relkind='f' THEN 'FOREIGN TABLE'
WHEN c.relkind='p' THEN 'PARTITIONED TABLE'
ELSE 'TABLE' END AS container_type,
tr.tgname AS object_name,
'TRIGGER' AS object_type
FROM pg_trigger tr INNER JOIN pg_class c ON tr.tgrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND tr.tgisinternal='f'
AND tr.tgconstraint=0
UNION SELECT
NULL AS oid,
schemaname || '.' || tablename AS container_name,
'TABLE' AS container_type,
rulename AS object_name,
'RULE' AS object_type
FROM pg_rules AS r
WHERE r.schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT
NULL AS oid,
'' AS container_name,
'DATABASE' AS container_type,
evtname AS object_name,
'EVENT TRIGGER' AS object_type
FROM pg_event_trigger
UNION ALL SELECT
c.oid,
n.nspname || '.' || c.relname AS container_name,
CASE WHEN c.relkind='r' THEN 'BASE TABLE'
WHEN c.relkind='v' THEN 'VIEW'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW'
WHEN c.relkind='f' THEN 'FOREIGN TABLE'
WHEN c.relkind='p' THEN 'PARTITIONED TABLE'
ELSE 'TABLE' END AS container_type,
at.attname AS object_name,
'COLUMN' AS object_type
FROM pg_attribute at INNER JOIN pg_class c ON at.attrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (nspname='public' OR rolname<>'postgres')
AND c.relkind IN ('r','v','m','f','p')
AND at.attisdropped='f'
AND at.attnum>0
),
named_user_defined_objects AS (
SELECT container_name, container_type, object_name, object_type
FROM named_objects nob
WHERE NOT EXISTS (SELECT 1
FROM pg_catalog.pg_depend d
WHERE EXISTS (SELECT 1 FROM pg_catalog.pg_extension e WHERE d.refobjid=e.oid) AND
d.objid=nob.oid)
)
SELECT container_name, container_type, replace(replace(object_name,'<','<'),'>','>') AS suspected_name_html, object_name AS suspected_name_original, object_type
FROM named_user_defined_objects
WHERE object_name<>lower(object_name)
OR object_name~'[[:upper:]]'
OR object_name~'^[^[:alpha:]_].*'
OR object_name~'^.+[^[:alpha:][:digit:]_$].*'
OR lower(object_name) IN (SELECT word FROM pg_get_keywords() WHERE catcode='R')
ORDER BY object_type, container_name, object_name;