From: jwieck@debis.com
To: elangog@wipinfo.soft.net
Cc: mwm@phone.net
Cc: pgsql-sql@postgreSQL.org
Subject: Rules (was: Re: [SQL] How can I optimize...)
Date: Wed, 4 Nov 1998 12:35:17 +0100 (MET)
Message-ID: <m0zb1DZ-000EBVC@orion.SAPserv.Hamburg.dsh.de> (raw)
In-Reply-To: <Pine.SOL.3.96.981104092805.20300B-100000@wipdisc>
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) #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: jwieck@debis.com, elangog@wipinfo.soft.net, mwm@phone.net
Subject: Re: Rules (was: Re: [SQL] How can I optimize...)
In-Reply-To: <m0zb1DZ-000EBVC@orion.SAPserv.Hamburg.dsh.de>
References: <Pine.SOL.3.96.981104092805.20300B-100000@wipdisc> <m0zb1DZ-000EBVC@orion.SAPserv.Hamburg.dsh.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox