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

My first mail was bit incomplete

Note : OLD is not recognized when i used to create rule on example given
below,i tried with CURRENT instead of OLD , rule was created
but when i execute some operation on it backend connection get closed

my practical result is
CREATE TABLE prim (
> pkey text,
> comment text);
>
> CREATE TABLE ref (
> rkey text,
> skey integer,
> comment text);
>
is Ok
CREATE RULE emp_ins AS ON INSERT TO emp DO
> INSERT INTO emp_log VALUES (
> NEW.ename,
> 'employed',
> NEW.salary,
> getpgusername(),
> 'now'::text);
is also OK Created
CREATE RULE emp_del as on DELETE to emp
do DELETE from emp_log where ename=OLD.ename

old table not found
but
systemdb=> CREATE RULE emp_del as on DELETE to emp
systemdb-> do DELETE from emp_log where ename=current.ename
systemdb-> ;
CREATE
systemdb=>
systemdb=> select * from emp;
ename |dept |salary
-------+-----------+----------
wieck |development|$8,000.00
gates |management |$15,000.00
elangog|lucent |$1,000.00
(3 rows)
systemdb=> delete from emp where ename='gates';
PQexec() -- Request was sent to backend, but backend closed the channel
before responding.
This probably means the backend terminated abnormally before or
while processing the request.
systemdb=>

please help me
to create a rule on DELETE

Thanku ,expecting helpful reply

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(at)debis(dot)com (Jan Wieck) #
>
>
>
>