pg_rules
A system view listing query rewrite rules
pg_rules
is a system catalogue view listing query rewrite rules. pg_rules
extends the underlying system catalogue table pg_rewrite
to provide a human-readable representation of rewrite rules.
pg_rules
was added in PostgreSQL 6.4.
Definition by PostgreSQL version
pg_rules (PostgreSQL 15)
View "pg_catalog.pg_rules" Column | Type | Collation | Nullable | Default ------------+------+-----------+----------+--------- schemaname | name | | | tablename | name | | | rulename | name | | | definition | text | | |
Documentation: pg_rules
pg_rules (PostgreSQL 14)
View "pg_catalog.pg_rules" Column | Type | Collation | Nullable | Default ------------+------+-----------+----------+--------- schemaname | name | | | tablename | name | | | rulename | name | | | definition | text | | |
Documentation: pg_rules
pg_rules (PostgreSQL 13)
View "pg_catalog.pg_rules" Column | Type | Collation | Nullable | Default ------------+------+-----------+----------+--------- schemaname | name | | | tablename | name | | | rulename | name | | | definition | text | | |
Documentation: pg_rules
pg_rules (PostgreSQL 12)
View "pg_catalog.pg_rules" Column | Type | Collation | Nullable | Default ------------+------+-----------+----------+--------- schemaname | name | | | tablename | name | | | rulename | name | | | definition | text | | |
Documentation: pg_rules
pg_rules (PostgreSQL 11)
View "pg_catalog.pg_rules" Column | Type | Collation | Nullable | Default ------------+------+-----------+----------+--------- schemaname | name | | | tablename | name | | | rulename | name | | | definition | text | | |
Documentation: pg_rules
pg_rules (PostgreSQL 10)
View "pg_catalog.pg_rules" Column | Type | Collation | Nullable | Default ------------+------+-----------+----------+--------- schemaname | name | | | tablename | name | | | rulename | name | | | definition | text | | |
Documentation: pg_rules
pg_rules (PostgreSQL 9.6)
View "pg_catalog.pg_rules" Column | Type | Modifiers ------------+------+----------- schemaname | name | tablename | name | rulename | name | definition | text |
Documentation: pg_rules
pg_rules (PostgreSQL 9.5)
View "pg_catalog.pg_rules" Column | Type | Modifiers ------------+------+----------- schemaname | name | tablename | name | rulename | name | definition | text |
Documentation: pg_rules
pg_rules (PostgreSQL 9.4)
View "pg_catalog.pg_rules" Column | Type | Modifiers ------------+------+----------- schemaname | name | tablename | name | rulename | name | definition | text |
Documentation: pg_rules
pg_rules (PostgreSQL 9.3)
View "pg_catalog.pg_rules" Column | Type | Modifiers ------------+------+----------- schemaname | name | tablename | name | rulename | name | definition | text |
Documentation: pg_rules
pg_rules (PostgreSQL 9.2)
View "pg_catalog.pg_rules" Column | Type | Modifiers ------------+------+----------- schemaname | name | tablename | name | rulename | name | definition | text |
Documentation: pg_rules
pg_rules (PostgreSQL 9.1)
View "pg_catalog.pg_rules" Column | Type | Modifiers ------------+------+----------- schemaname | name | tablename | name | rulename | name | definition | text |
Documentation: pg_rules
pg_rules (PostgreSQL 9.0)
View "pg_catalog.pg_rules" Column | Type | Modifiers ------------+------+----------- schemaname | name | tablename | name | rulename | name | definition | text |
Documentation: pg_rules
pg_rules (PostgreSQL 8.4)
View "pg_catalog.pg_rules" Column | Type | Modifiers ------------+------+----------- schemaname | name | tablename | name | rulename | name | definition | text | View definition: SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM pg_rewrite r JOIN pg_class c ON c.oid = r.ev_class LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE r.rulename <> '_RETURN'::name;
Documentation: pg_rules
pg_rules (PostgreSQL 8.3)
View "pg_catalog.pg_rules" Column | Type | Modifiers ------------+------+----------- schemaname | name | tablename | name | rulename | name | definition | text | View definition: SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM pg_rewrite r JOIN pg_class c ON c.oid = r.ev_class LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE r.rulename <> '_RETURN'::name;
Documentation: pg_rules
pg_rules (PostgreSQL 8.2)
View "pg_catalog.pg_rules" Column | Type | Modifiers ------------+------+----------- schemaname | name | tablename | name | rulename | name | definition | text | View definition: SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM pg_rewrite r JOIN pg_class c ON c.oid = r.ev_class LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE r.rulename <> '_RETURN'::name;
Documentation: pg_rules
Change history
- PostgreSQL 7.3
- column
schemaname
added (commit 3b78826b)
- column
- PostgreSQL 6.4
Examples
After creating the following rule:
postgres=# CREATE RULE foobar AS ON INSERT TO foo WHERE NEW.id % 2 = 1 DO INSTEAD NOTHING; CREATE RULE
the following record is present in pg_rules
:
postgres=# SELECT * FROM pg_rules WHERE rulename='foobar'\gx -[ RECORD 1 ]----------------------------------------------- schemaname | public tablename | foo rulename | foobar definition | CREATE RULE foobar AS + | ON INSERT TO public.foo + | WHERE ((new.id % 2) = 1) DO INSTEAD NOTHING;
By default, two rules are always present for the pg_settings
view:
postgres=# SELECT * FROM pg_rules\gx -[ RECORD 1 ]------------------------------------------------------------------------------------------------- schemaname | pg_catalog tablename | pg_settings rulename | pg_settings_u definition | CREATE RULE pg_settings_u AS + | ON UPDATE TO pg_catalog.pg_settings + | WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config; -[ RECORD 2 ]------------------------------------------------------------------------------------------------- schemaname | pg_catalog tablename | pg_settings rulename | pg_settings_n definition | CREATE RULE pg_settings_n AS + | ON UPDATE TO pg_catalog.pg_settings DO INSTEAD NOTHING;
References
- PostgreSQL documentation: pg_rules