Received: from donald.wipinfo.soft.net ([164.164.67.20]) by hub.org (8.9.1/8.9.1) with SMTP id AAA17789 for ; Thu, 5 Nov 1998 00:08:43 -0500 (EST) (envelope-from elangog@wipinfo.soft.net) Received: from apollo.wipinfo.soft.net by donald.wipinfo.soft.net (SMI-8.6/SMI-SVR4) id KAA06091; Thu, 5 Nov 1998 10:40:49 -0500 Received: from wipdisc.wipinfo.soft.net by apollo.wipinfo.soft.net (SMI-8.6/SMI-SVR4) id KAA24752; Thu, 5 Nov 1998 10:06:57 -0500 Received: from localhost by wipdisc.wipinfo.soft.net (SMI-8.6/SMI-SVR4) id KAA02058; Thu, 5 Nov 1998 10:36:38 -0500 Date: Thu, 5 Nov 1998 10:36:37 -0500 (GMT) From: "G.Elangovan " X-Sender: elangog@wipdisc To: Jan Wieck cc: mwm@phone.net, pgsql-sql@postgreSQL.org Subject: Re: Rules (was: Re: [SQL] How can I optimize...) In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII I tried Creating RULE with both the example In first example both the rule failed with error "old table not found" In case of second example i managed to create but other three rule failed 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); ERROR: old: Table does not exist. CREATE RULE emp_fire AS ON DELETE TO emp DO INSERT INTO emp_log VALUES ( OLD.ename, 'fired', '$0', getpgusername(), 'now'::text); ERROR: old: Table does not exist. Please giveme some more info on the KEY word (NEW and OLD).attribute Note : iam using postgresql 6.3.2 Thanku , for ur kind help On Wed, 4 Nov 1998, Jan Wieck wrote: > 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) # > > >