Re: [SQL] CREATE RULE question

>
> Hi!
> Is there any way to create rule with more than one action?
> The syntax shown in psql help as well as appropriate man page
> definitely show that it's possible:
>    CREATE RULE rule_name AS ON
>         [SELECT|UPDATE|DELETE|INSERT]
>         TO object [WHERE qual]
>         DO [INSTEAD] [action|NOTHING|[actions]];
>

    Hmmm - seems I forgot to update the manpage - sorry.

> but I can't do anything about it:
> CREATE RULE proba_upd
>         AS ON update to proba
>         DO UPDATE proba SET dr_date = 'now'::text where id=OLD.id and
> dr_date='infinity'
>            INSERT INTO proba VALUES (NEW.id, NEW.name)
>         ;
> ERROR:  parser: parse error at or near "insert"
>

    The multiple actions must be surrounded by parens (originally
    and still accepted []'s - but that doesn't  pass  psql  so  I
    added ()'s as well) and except for the last one be terminated
    with a semicolon.

    But the rule above will not work either. The  result  of  the
    first action is again an UPDATE on the same table, which will
    be rewritten again, and again, and again, ... And the  second
    action seems not to have values for all attributes.

    From  the above I assume "id" is the unique key and "dr_date"
    means 'valid until'. Further I assume you also  have  a  date
    meaning  'valid from' and might want it to be forced to 'now'
    at insertion time.  This all means you want to implement some
    history  functionality  and  I  think it's best to do it with
    views and extra rules on one of them.

    CREATE SEQUENCE proba_seq;

    CREATE TABLE proba_r (
        p_id        integer,
        p_name      text,
        p_from      datetime,
        p_until     datetime);

    CREATE VIEW proba AS SELECT * FROM proba_r;

    CREATE VIEW proba_valid AS SELECT * FROM proba_r
        WHERE p_until = 'infinity';

    CREATE RULE ins_proba AS ON INSERT TO proba DO INSTEAD
        INSERT INTO proba_r (p_id, p_name, p_from, p_until)
               VALUES (nextval('proba_seq'), new.p_name,
                       'now'::text, 'infinity'::text);

    CREATE RULE upd_proba AS ON UPDATE TO proba DO INSTEAD (
        UPDATE proba_r SET p_until = 'now'::text
               WHERE p_id = old.p_id AND p_until = 'infinity';
        INSERT INTO proba_r (p_id, p_name, p_from, p_until)
               VALUES (old.p_id, new.p_name,
                       'now'::text, 'infinity'::text);
        );

    CREATE RULE del_proba AS ON DELETE TO proba DO INSTEAD
        UPDATE proba_r SET p_until = 'now'::text
               WHERE p_id = old.p_id AND p_until = 'infinity';

    I've  put  the  nextval('proba_seq')  into  the  insert  rule
    because  in this scenario you cannot create a unique index on
    the p_id column.  This forces a new number from the  sequence
    to get used regardless of what the user specified for it.

    With  this you have a view "proba" which behaves like a table
    where some  triggers  are  fired  and  which  shows  all  the
    history.  The extra view "proba_valid" shows only the entries
    that are active.


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