The number of rules by different characteristics

Copy SQL

WITH rules AS (SELECT 
  r.schemaname AS rule_schema, 
  r.tablename AS host_table, 
  r.rulename AS rule_name, 
  substring(r.definition, 'ON[[:space:]](SELECT|INSERT|UPDATE|DELETE)') AS event,
  substring(r.definition, 'DO.*$') AS rule_definition
FROM 
  pg_catalog.pg_rules r, 
  pg_catalog.pg_namespace n, 
  pg_catalog.pg_authid u
WHERE 
  r.schemaname = n.nspname AND
  n.nspowner = u.oid AND 
 (n.nspname = 'public' OR u.rolname <> 'postgres'))
SELECT event, Count(DISTINCT rule_schema || '.' || host_table || '.' || rule_name) AS nr_of_rules
FROM rules
GROUP BY CUBE (event)
ORDER BY event, nr_of_rules DESC;