Query goal: | Find the number of rules by event (SELECT, INSERT, UPDATE, and DELETE) and in total. |
Query type: | Sofware measure (Numeric values (software measures) about the database) |
Query license: | MIT License |
Data source: | system catalog only |
SQL query: | Click on query to copy it
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; |
Collection name | Collection description |
---|---|
Find problems by overview | Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not . |
Find quick numeric overview of the database | Queries that return numeric values showing mostly the number of different types of database objects in the database |
Category name | Category description |
---|---|
Triggers and rules | Queries of this category provide information about triggers and rules in a database. |