HOW TO SET RULES IN POSTGRESQL?

PostgreSQL supports a powerful rule system for the specification of views and ambiguous view updates. Originally the PostgreSQL rule system consisted of two implementations:

• The first one worked using tuple level processing and was implemented deep in the executor. The rule system was called whenever an individual tuple had been accessed. This implementation was removed in 1995 when the last official release of the PostgreSQL project was transformed into Postgres95.

• The second implementation of the rule system is a technique called query rewriting. The rewrite system} is a module that exists between the parser stage and the planner/optimizer. This technique is still implemented.

Procedure For creating rule:

We can define new rule by the command listed below:

CREATE RULE — Defines a new rule

CREATE RULE name AS ON event
 TO object [ WHERE condition ]
 DO [ INSTEAD ] [ action | NOTHING ]

Where the inputes indicates :

name

The name of a rule to create.

event

Event is one of select, update, delete or insert.

object

Object is either table or table.column.

condition

Any SQL WHERE clause, new or old can appear instead of an instance variable whenever an instance variable is permissible in SQL.

action

Any SQL statement, new or old can appear instead of an instance variable whenever an instance variable is permissible in SQL.

The Postgres rule system allows one to define an alternate action to be performed on inserts, updates, or deletions from database tables or classes. Currently, rules are used to implement table views.

The semantics of a rule is that at the time an individual instance is accessed, inserted, updated, or deleted, there is a old instance (for selects, updates and deletes) and a new instance (for inserts and updates). If the event specified in the ON clause and the condition specified in the WHERE clause are true for the old instance, the action part of the rule is executed. First, however, values from fields in the old instance and/or the new instance are substituted for old.attribute-name and new.attribute-name.

The action part of the rule executes with the same command and transaction identifier as the user command that caused activation.

For Example Look at the below shown example:

Make Sam get the same salary adjustment as Joe:

CREATE RULE example_1 AS
 ON UPDATE emp.salary WHERE old.name = "Joe"
 DO
UPDATE emp
SET salary = new.salary
WHERE emp.name = "Sam";

At the time Joe receives a salary adjustment, the event will become true and Joe's old instance and proposed new instance are available to the execution routines. Hence, his new salary is substituted into the action part of the rule which is subsequently executed. This propagates Joe's salary on to Sam.

Make Bill get Joe's salary when it is accessed:

CREATE RULE example_2 AS
 ON SELECT TO EMP.salary
 WHERE old.name = "Bill"
 DO INSTEAD
SELECT emp.salary
FROM emp
WHERE emp.name = "Joe";

Deny Joe access to the salary of employees in the shoe department (current_user returns the name of the current user):

CREATE RULE example_3 AS
 ON
SELECT TO emp.salary
 WHERE old.dept = "shoe" AND current_user = "Joe"
 DO INSTEAD NOTHING;

Create a view of the employees working in the toy department.

CREATE toyemp(name = char16, salary = int4);

CREATE RULE example_4 AS
 ON SELECT TO toyemp
 DO INSTEAD
  SELECT emp.name, emp.salary
FROM emp
     WHERE emp.dept = "toy";

All new employees must make 5,000 or less

CREATE RULE example_5 AS
 ON INERT TO emp WHERE new.salary > 5000
 DO
UPDATE NEWSET SET salary = 5000;