postgres rules - ghdrako/doc_snipets GitHub Wiki

Rule system that allows you to rewrite and modify the execution of queries. This is a Postgres extension of the SQL Standard for defining actions such as "when we UPDATE table a, also INSERT into table b" or "when we SELECT from table x, instead SELECT from table y".

CREATE OR REPLACE RULE log_order_insertions AS
  ON INSERT TO erp.order_groups
  DO ALSO
    INSERT INTO audit.audit_log (what, who, id, tstamp) VALUES ('Manual order insertion',CURRENT_ROLE::text, NEW.id, clock_timestamp());

Zaloguj dodanie wiersza do tabeli w tabeli audytowej

Rules are simple event handlers. At the user level, it is possible to manage all the events that perform write operations, which are as follows:

• INSERT
• DELETE
• UPDATE The fundamental concept behind rules is to modify the flow of an event.

Rules in PostgreSQL are always executed before the event

If we are given an event,what we can do when certain conditions occur is as follows:

• Do nothing and then undo the action of that event.
• Trigger another event instead of the default one.
• Trigger another event in conjunction with the default. So, given a write operation, for example, an INSERT operation, we can perform one of these three actions:
• Cancel the operation.
• Perform another operation instead of the INSERT.
• Execute the INSERT and, in the same transaction, perform another operation.

Rules in PostgreSQL creates additional commands to be executed whenever any table data is modified/inserted on the table where rule has been created. At the same time, it also can execute the code mentioned in INSTEAD block. The majorly to create additional security on specific table data.

CREATE [ OR REPLACE ] RULE name AS ON event
TO table_name [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | (
command ; command ... ) }
where event can be one of:
SELECT | INSERT | UPDATE | DELETE

Rules on INSERT

CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... )
}

There are three options that we have when
we decide to use a rule:
* The ALSO option
* The INSTEAD option
* The INSTEAD NOTHING option



#### ALSO
Example: every time a record is inserted with a tag value that starts with the letter “O,” as well as being inserted into the tags table, it must also be inserted into the O_tags table. 

create table O_tags ( pk integer not null primary key, tag text, parent integer );

create or replace rule r_tags1 as on INSERT to tags where NEW.tag ilike 'O%' DO ALSO insert into O_tags(pk,tag,parent) values (NEW.pk,NEW.tag,NEW.parent);

#### INSTEAD OF
Every time a record is inserted with a tag value that starts with the letter f, or the capital letter F, it must be moved into the
f_tags table.

create table F_tags ( pk integer not null primary key , tag text, parent integer);

create or replace rule r_tags2 as on INSERT to tags where NEW.tag ilike 'f%' DO INSTEAD insert into f_tags(pk,tag,parent)values (NEW.pk,NEW.tag,NEW.parent);

#### 
To protect ADMIN entry in USER_MST rule can be created as below:

CREATE OR REPLACE RULE user_data_protect1 AS ON UPDATE TO user_mst WHERE OLD.user_name = ‘ADMIN’ DO NOTHING INSTEAD CREATE OR REPLACE RULE user_data_protect2 AS ON DELETE TO user_mst WHERE OLD.user_name = ‘ADMIN’ DO NOTHING INSTEAD