Rules (was: Re: [SQL] How can I optimize...) - Mailing list 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) #