Ideas, suggestions to rules (based on a real problem) - Mailing list pgsql-general

Hi,

i have an idea for some addition to the rule concept and would like to
hear comment on that. I dont know if these are possible and/or sane.
I would like to suggest a syntax form like this (description follows):

        CREATE [ OR REPLACE ] RULE name AS ON event
             TO table [ WHERE condition ]
             DO [ ONCE ] [ BEFORE | AFTER | INSTEAD ] action

where ONCE makes the RULE used only once in the rewrite process. Making
Rules like this possible (the details to the query follow later):

    CREATE RULE jokus AS ON UPDATE
             TO tab1 WHERE bit_length(OLD.f1)<bit_length(NEW.f1)
         DO ONCE
        UPDATE jokus SET f1=bit_set_len(f1,bit_length(NEW.f1));

What means on update on one field update also all other fields in the
table. The background for this is that i have a bitmask on some tables,
that holds state information.
Because i dont want to bound these to a length i decided to use bit
varying for that. To make binary operations possible for that fields it
is nessecary to keep all of the inserted masks at the same length (I
could choose a fixed length at the beginning and make a bit(10) or such,
but i want to keep it flexible.) So bit_set_len is a plpgsql function
that perfoms the following:

    bit_set_len(bit varying, int4) -- changes the length of $1 to $2 in
    a way one would expect from a binary type (at least on a little
    endian machine) by cutting from from left when $2<bit_length($1) and
    filling up with nulls to the left when $2>bit_length($1)


Now for the other two now options BEFORE | AFTER. They should control
when the original query tree is called, before or after the new created
query tree, which would make rules like this possible (again details
follow):

    CREATE RULE jokus1 AS ON DELETE
         TO tab1 WHERE ref_count(OLD.f1)<>0
         DO INSTEAD NOTHING;

    CREATE RULE jokus2 AS ON UPDATE
         TO tab2 WHERE NEW.used=0
             DO AFTER
            DELETE FROM tab1 WHERE tab2.f1=NEW.f1;

Here entries in tab1 can be referenced by multiple row in multiple
tables. To keep track on this every possible key for tab1 is also stored
in tab2 with e reference-counter which shows how often the entry in tab
one is referenced. ref_count(int4) returns the number of reference
actually set to the entrie. It should not be possible to delete an entry
that is referenced, for this is rule jokus1. And when the reference
counter in tab2 goes to 0 the entrie in tab1 should be removed
automagically, that is rule jokus2. Actually this is not possible
because the rule-system performs first the new created query and then
the query that triggers the rule, so when the delete in jokus 2 is
called the reference count (which is <<used>> in the example btw.) has
the old value and not the new one.
If you do such a thing you can for sure no longer reference to OLD
because OLD is alreade altered...or in other words OLD = NEW when perfom
an DO AFTER.
The BOFORE does similar if you use ON INSERT but i have actualle no
practical example for it.

Thats it so far, please comment :-)

Best regards
   Georg Steffers