Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) by hub.org (8.9.1/8.9.1) with ESMTP id MAA08276 for ; Wed, 4 Nov 1998 12:27:12 -0500 (EST) (envelope-from wieck@sapserv.debis.de) Received: by dsh.de; id SAA05237; Wed, 4 Nov 1998 18:29:12 +0100 (MET) Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) id xmagc0356; Wed, 4 Nov 98 18:28:51 +0100 Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id MAA24398; Wed, 4 Nov 1998 12:15:09 +0100 (MET) Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id MAA14350; Wed, 4 Nov 1998 12:17:52 +0100 Received: from orion.SAPserv.Hamburg.dsh.de by mars.SAPserv.Hamburg.dsh.de with smtp for <> id m0zayeV-000B5AC; Wed, 4 Nov 98 09:50 MET Received: by orion.SAPserv.Hamburg.dsh.de for mwm@phone.net id m0zb1DZ-000EBVC; Wed, 4 Nov 98 12:35 MET Message-Id: From: jwieck@debis.com (Jan Wieck) Subject: Rules (was: Re: [SQL] How can I optimize...) To: elangog@wipinfo.soft.net (G.Elangovan) Date: Wed, 4 Nov 1998 12:35:17 +0100 (MET) Cc: mwm@phone.net, pgsql-sql@postgreSQL.org Reply-To: jwieck@debis.com (Jan Wieck) In-Reply-To: from "G.Elangovan" at Nov 4, 98 09:29:45 am X-Mailer: ELM [version 2.4 PL25] Content-Type: text 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@debis.com (Jan Wieck) #