pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Rules (was: Re: [SQL] How can I optimize...)
4+ messages / 2 participants
[nested] [flat]

* Rules (was: Re: [SQL] How can I optimize...)
@ 1998-11-04 11:35  Jan Wieck <jwieck@debis.com>
  0 siblings, 2 replies; 4+ messages in thread

From: jwieck@debis.com @ 1998-11-04 11:35 UTC (permalink / raw)
  To: elangog@wipinfo.soft.net; +Cc: mwm@phone.net; pgsql-sql

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) #





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Rules (was: Re: [SQL] How can I optimize...)
@ 1998-11-05 12:08  Jan Wieck <jwieck@debis.com>
  parent: G.Elangovan  <elangog@wipinfo.soft.net>
  0 siblings, 0 replies; 4+ messages in thread

From: jwieck@debis.com @ 1998-11-05 12:08 UTC (permalink / raw)
  To: elangog@wipinfo.soft.net; +Cc: jwieck@debis.com; mwm@phone.net; pgsql-sql

>
> 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
>
> [...]
>
> 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

    DON'T  TRY  TO USE THE RULE SYSTEM IN A v6.3.2 DATABASE. IT'S
    BUGGY AND DOES NOT WORK AT ALL.

    All I wrote depends on the major changes  I've  done  in  the
    rule system for v6.4.

    In  v6.4 CURRENT is aliased with OLD and in v6.5 CURRENT will
    disappear completely.


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) #





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Rules (was: Re: [SQL] How can I optimize...)
@ 1998-11-05 15:36  G.Elangovan  <elangog@wipinfo.soft.net>
  parent: Jan Wieck <jwieck@debis.com>
  1 sibling, 1 reply; 4+ messages in thread

From: G.Elangovan @ 1998-11-05 15:36 UTC (permalink / raw)
  To: Jan Wieck <jwieck@debis.com>; +Cc: mwm@phone.net; pgsql-sql

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) #
> 
> 
> 




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Rules (was: Re: [SQL] How can I optimize...)
@ 1998-11-05 16:54  G.Elangovan  <elangog@wipinfo.soft.net>
  parent: Jan Wieck <jwieck@debis.com>
  1 sibling, 0 replies; 4+ messages in thread

From: G.Elangovan @ 1998-11-05 16:54 UTC (permalink / raw)
  To: Jan Wieck <jwieck@debis.com>; +Cc: mwm@phone.net; 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) #
> 
> 
> 
> 




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~1998-11-05 16:54 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-11-04 11:35 Rules (was: Re: [SQL] How can I optimize...) Jan Wieck <jwieck@debis.com>
1998-11-05 15:36 ` G.Elangovan  <elangog@wipinfo.soft.net>
1998-11-05 12:08   ` Jan Wieck <jwieck@debis.com>
1998-11-05 16:54 ` G.Elangovan  <elangog@wipinfo.soft.net>

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