From: jwieck@debis.com
To: postgres@nest.bistbn.com
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] CREATE RULE question
Date: Tue, 15 Dec 1998 10:11:45 +0100 (MET)
Message-ID: <m0zpqW9-000EBSC@orion.SAPserv.Hamburg.dsh.de> (raw)
In-Reply-To: <Pine.BSF.4.02.9812151002260.6120-100000@nest.bistbn.com>
>
> 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) #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: jwieck@debis.com, postgres@nest.bistbn.com
Subject: Re: [SQL] CREATE RULE question
In-Reply-To: <m0zpqW9-000EBSC@orion.SAPserv.Hamburg.dsh.de>
References: <Pine.BSF.4.02.9812151002260.6120-100000@nest.bistbn.com> <m0zpqW9-000EBSC@orion.SAPserv.Hamburg.dsh.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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