| Query goal: | Find the number of rules in a database, excluding the rules that are associated with system catalog tables and 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 some indications 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. |
| 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 Count(*) AS number_of_rules FROM rules; |
| 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 |