Re: Rules (was: Re: [SQL] How can I optimize...) - Mailing list pgsql-sql

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@debis.com (Jan Wieck) #
>
>
>
>