pg_get_ruledef()

A function for recreating the command a rule was created with

pg_get_ruledef() is a system function which reproduces the command used to create a rule.

pg_get_ruledef() was added in PostgreSQL 6.4.

Usage

pg_get_ruledef ( rule oid [, pretty boolean ] ) → text

By default, the rule creation command is returned in a verbose format suitable for inclusion in pg_dump output, however if the pretty parameter  (default: false) is set to true, the command is produced in a more compact, human-readable form.

Note that the generated command is a decompiled reconstruction, not the text the command was originally defined with.

Internally, pg_get_ruledef() is used by the system view pg_rules to generate a user-friendlier representation of the rule than is available from the underlying pg_rewrite system table.

Change history

Examples

Given a rule generated as follows:

postgres=# CREATE RULE foobar AS                                                   
             ON INSERT TO foo
               WHERE NEW.id % 2 = 1
             DO INSTEAD NOTHING;
CREATE RULE

pg_get_ruledef() reproduces the command as either:

postgres=# SELECT pg_get_ruledef(oid) FROM pg_rewrite WHERE rulename='foobar';
                 pg_get_ruledef                  
-------------------------------------------------
 CREATE RULE foobar AS                          +
     ON INSERT TO public.foo                    +
    WHERE ((new.id % 2) = 1) DO INSTEAD NOTHING;
(1 row)

or:

postgres=# SELECT pg_get_ruledef(oid, true) FROM pg_rewrite WHERE rulename='foobar';
                pg_get_ruledef                 
-----------------------------------------------
 CREATE RULE foobar AS                        +
     ON INSERT TO foo                         +
    WHERE (new.id % 2) = 1 DO INSTEAD NOTHING;
(1 row)

Categories

Rules, System function

See also

pg_rewrite, pg_rules