Rules (was: Re: [SQL] How can I optimize...)

G.Elangovan wrote:

>
> Can any one help me to CREATE RULE
> please give me an example with
> a simple table(s) definition and to create rule on that table

It's on my personal TODO to create a rule system
documentation for users too. The one in the programmers
manual for v6.4 is a bit technical, but you might want to
read it anyway.

Anything below addresses the v6.4 rule system!

Sample 1 - constraint follow/delete

CREATE TABLE prim (
pkey text,
comment text);

CREATE TABLE ref (
rkey text,
skey integer,
comment text);

--
-- Rule to let ref.rkey follow prim.pkey
--
CREATE RULE prim_follow AS ON UPDATE TO prim
WHERE NEW.pkey != OLD.pkey DO
UPDATE ref SET rkey = NEW.pkey WHERE rkey = OLD.pkey;

--
-- Rule to constraint delete references from ref to pkey
--
CREATE RULE prim_delete AS ON DELETE TO prim DO
DELETE FROM ref WHERE rkey = OLD.pkey;

--
-- Some tests
--
INSERT INTO prim VALUES ('k1', 'to get updated');
INSERT INTO prim VALUES ('k2', 'to get deleted');
INSERT INTO ref VALUES ('k1', 1, 'must follow');
INSERT INTO ref VALUES ('k1', 2, 'must follow');
INSERT INTO ref VALUES ('k2', 1, 'must get deleted');
INSERT INTO ref VALUES ('k2', 2, 'must get deleted');

UPDATE prim SET pkey = 'new1' WHERE pkey = 'k1';
SELECT * FROM ref;
rkey|skey|comment
----+----+----------------
k2 | 1|must get deleted
k2 | 2|must get deleted
new1| 1|must follow
new1| 2|must follow
(4 rows)

DELETE FROM prim WHERE pkey = 'k2';
SELECT * FROM ref;
rkey|skey|comment
----+----+-----------
new1| 1|must follow
new1| 2|must follow
(2 rows)

Sample 2 - logging of table changes

CREATE TABLE emp (
ename text,
dept text,
salary money);

CREATE TABLE emp_log (
ename text,
action text,
newsal money,
uname name,
when datetime);

--
-- Rule to log new created employees
--
CREATE RULE emp_ins AS ON INSERT TO emp DO
INSERT INTO emp_log VALUES (
NEW.ename,
'employed',
NEW.salary,
getpgusername(),
'now'::text);

--
-- Rule to log when salary raises
--
CREATE RULE emp_raise AS ON UPDATE TO emp
WHERE NEW.salary > OLD.salary DO
INSERT INTO emp_log VALUES (
NEW.ename,
'raised',
NEW.salary,
getpgusername(),
'now'::text);

--
-- Rule to log when salary is lowered
--
CREATE RULE emp_lower AS ON UPDATE TO emp
WHERE NEW.salary < OLD.salary DO
INSERT INTO emp_log VALUES (
NEW.ename,
'lowered',
NEW.salary,
getpgusername(),
'now'::text);

--
-- Rule to log when employee is fired
--
CREATE RULE emp_fire AS ON DELETE TO emp DO
INSERT INTO emp_log VALUES (
OLD.ename,
'fired',
'$0',
getpgusername(),
'now'::text);

--
-- Some tests
--
INSERT INTO emp VALUES ('wieck', 'development', '$8000.00');
INSERT INTO emp VALUES ('gates', 'management', '$15000.00');

UPDATE emp SET salary = '$10000.00' WHERE ename = 'wieck';
UPDATE emp SET salary = '$12000.00' WHERE ename = 'gates';

DELETE FROM emp WHERE ename = 'gates';

SELECT * FROM emp_log;
ename|action |newsal |uname|when
-----+--------+----------+-----+----------------------------
wieck|employed|$8,000.00 |pgsql|Wed Nov 04 12:19:24 1998 MET
gates|employed|$15,000.00|pgsql|Wed Nov 04 12:19:26 1998 MET
wieck|raised |$10,000.00|pgsql|Wed Nov 04 12:19:27 1998 MET
gates|lowered |$12,000.00|pgsql|Wed Nov 04 12:19:28 1998 MET
gates|fired |$0.00 |pgsql|Wed Nov 04 12:19:30 1998 MET
(5 rows)

Note: The explicit 'now'::text is required. Otherwise 'now'
is interpreted at CREATE RULE time and all log entries will
contain that date instead of the time it happened.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck(at)debis(dot)com (Jan Wieck) #