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(at)debis(dot)com (Jan Wieck) #