| Query goal: | Find the number of rules in a database in different schemas, excluding the rules that are created to support views. Rules can be used to maintain data integrity in a database by causing rejection of incorrect insertions and updates. Therefore, the number of rules in a database gives an indication about the state of enforcing constraints at the database level. |
| Notes about the query: | Rules are specific to PostgreSQL and thus it is not possible to get information about these from the INFORMATION_SCHEMA views. The query only returns data about schemas that have at least one user-defined rule. |
| 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 n.nspname AS schemaname, c.relname AS tablename, r.rulename FROM pg_rewrite r JOIN pg_class c ON c.oid = r.ev_class JOIN pg_namespace n ON n.oid = c.relnamespace JOIN pg_authid u ON n.nspowner = u.oid WHERE r.rulename <> '_RETURN' AND (n.nspname = 'public' OR u.rolname <> 'postgres')) SELECT schemaname, Count(*) AS number_of_rules FROM rules GROUP BY ROLLUP(schemaname) ORDER BY schemaname; |
| 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. |
| Reference |
|---|
| https://www.postgresql.org/docs/current/rules-views.html |