Thread: RULE with conditional behaviour?
Hello all,
I've got two versions of the same question;
- The short version:
As I understand it a rule cannot fire a trigger?
- And the long version:
What I am trying to do is this:
I have three tables and a view on those tables ( see end of message if
the view below alone isn't enough info and you're in a helpful mood )
CREATE VIEW field_label_locales AS
-- [REFNAME: flabel_loc]
SELECT
field_labels.id AS field_label_id,
field_labels.label,
fields.id AS field_id,
fields.identifier,
locales.id AS locale_id,
locales.iso639,
locales.iso3166
FROM
field_labels
RIGHT OUTER JOIN
fields ON field_labels.field_id = fields.id
RIGHT OUTER JOIN
locales ON field_labels.locale_id = locales.id;
a select on this view looks like:
field_label_id | label | field_id | identifier | locale_id
| iso639 | iso3166
----------------+----------------+----------+----------------+-----------+--------+---------
1 | Naam Ontvanger | 2 | sender_address | 1
| nl | NL
| | | | 2
| nl | BE
2 | Recipient Name | 2 | sender_address | 3
| en | GB
| | | | 4
| en | US
| | | | 5
| de | DE
What I would like to be able to do is:
UPDATE field_label_locales SET label = 'Sender Name' WHERE locale_id = 1;
So; if field_label_id = NULL, I need to insert a record into
field_labels, else I need to update the record referenced in field_labels.
Can I do this with a rule on the view?
I've created a trigger which works just fine, but I can seem to fire a
trigger with a rule?
I could implement it some other way, but lazy as I am I thought I'd fire
off a mail to the list to see if I am missing something obvious.
Regards,
Rob
====
the relevant bits of the thre tables:
CREATE TABLE locales (
iso639 varchar(2) NOT NULL, -- two character iso639
language code
iso3166 varchar(2), -- two character iso3166
country code
lang_native varchar(80) NOT NULL, -- language name in native
language
country_native varchar(80), -- country name in native language
fallback boolean DEFAULT false, -- If set to true, this locale is
id bigserial,
PRIMARY KEY(id),
UNIQUE(iso639, iso3166)
);
CREATE TABLE fields (
identifier varchar(30),
id bigserial,
PRIMARY KEY(id)
);
CREATE TABLE field_labels (
label varchar(100) NOT NULL,
locale_id bigint NOT NULL,
field_id bigint NOT NULL,
FOREIGN KEY(locale_id) REFERENCES locales ON DELETE RESTRICT ON
UPDATE CASCADE,
FOREIGN KEY(field_id) REFERENCES fields ON DELETE RESTRICT ON UPDATE
CASCADE
);
Rob Hoopman <rob@tuna.nl> writes:
> So; if field_label_id = NULL, I need to insert a record into
> field_labels, else I need to update the record referenced in field_labels.
> Can I do this with a rule on the view?
There is no concept of conditional execution in rules, but sometimes you
can fake it with suitably conditionalized individual actions. In this
case it might work to do a two-action rule. Very schematically:
ON UPDATE TO view DO INSTEAD
(
UPDATE base-table WHERE key = whatever;
INSERT INTO base-table SELECT list-of-values WHERE
NOT EXISTS (SELECT 1 FROM base-table WHERE key = whatever);
)
The first action gets the job done if there are existing rows, and does
nothing if not. The second action, vice versa.
> I've created a trigger which works just fine, but I can seem to fire a
> trigger with a rule?
Sure, triggers are fired by rule actions. However, triggers only fire
on insertions/deletions of real tuples --- and there are none in a view.
So you can't usefully attach a trigger to a view, only to a base table.
If the above approach seems too complex, another possibility is to write
the rule attached to the view as something simple that invokes an action
you know will fire a trigger. For example,
ON UPDATE TO view DO INSTEAD INSERT INTO base-table VALUES(needed-values)
and then the insert trigger on base-table is set up to first look for a
conflicting extant row; if found, UPDATE it and suppress the insert;
else allow the insert to proceed. A difficulty with this approach is
that the insert-trigger will also apply to direct inserts into the base
table, but you may find that that's not a problem for you.
regards, tom lane
I wrote:
> If the above approach seems too complex, another possibility is to write
> the rule attached to the view as something simple that invokes an action
> you know will fire a trigger. For example,
> ON UPDATE TO view DO INSTEAD INSERT INTO base-table VALUES(needed-values)
This approach may not work well if your view is a join and so you might
need to update multiple base tables. There is a sneaky way to get
around that, which is to use a dummy table that has no other purpose
than to be a place where you can fire a trigger. For example,
ON UPDATE TO view DO INSTEAD INSERT INTO dummy-table SELECT NEW.*
Here, dummy-table is a real table (not a view), but it will never have
any rows in it, because you'll give it an on-insert trigger that always
suppresses the insert. Now, that trigger has access to a full row of
the "updated view" and so it can go off and do whatever is needed to
the underlying base tables.
If you did it just like this then you'd actually need three dummy
tables, one each for insert, update, and delete actions on the view.
If that seems like overkill, consider
ON INSERT TO view DO INSTEAD INSERT INTO dummy-table SELECT 1, NEW.*
ON UPDATE TO view DO INSTEAD INSERT INTO dummy-table SELECT 2, NEW.*
ON DELETE TO view DO INSTEAD INSERT INTO dummy-table SELECT 3, OLD.*
Now the insert trigger can look at the first column to decide what to
do. (Note you can't do anything useful with update or delete triggers
on the dummy table; it'll never have any rows so they'd never fire.)
In general, the columns of the dummy table are exactly the parameter
list you need to pass to your trigger, and so you can make 'em whatever
you like. For example, to correctly update a view you might need both
the old and new states of the (virtual) view row. No problem:
ON UPDATE TO view DO INSTEAD INSERT INTO dummy-table SELECT OLD.*, NEW.*
where dummy-table is declared with the necessary column set.
I have not actually had occasion to use this approach myself, but
here is a simple example showing that it would work:
regression=# select * from b1;
f1 | f2
-------------+-------------
0 | 0
123456 | 61728
-123456 | -61728
2147483647 | 1073741823
-2147483647 | -1073741823
(5 rows)
regression=# select * from b2;
f1 | f2
-------------+-------------
0 | 2
123456 | 123458
-123456 | -123454
2147483647 | -2147483647
-2147483647 | -2147483645
(5 rows)
regression=# create view vvv as
regression-# select b1.f1, b1.f2, b2.f2 as f22 from b1 join b2 using (f1);
CREATE
regression=# select * from vvv;
f1 | f2 | f22
-------------+-------------+-------------
-2147483647 | -1073741823 | -2147483645
-123456 | -61728 | -123454
0 | 0 | 2
123456 | 61728 | 123458
2147483647 | 1073741823 | -2147483647
(5 rows)
-- Note that this update makes no sense in terms of either base table alone:
regression=# update vvv set f22 = 43 where f2 = 61728 ;
ERROR: Cannot update a view without an appropriate rule
regression=# create table vvv_dummy (f1 int, f2 int, f22 int,
regression(# new_f1 int, new_f2 int, new_f22 int);
CREATE
regression=# create rule vvv_update as on update to vvv do instead
regression-# insert into vvv_dummy select old.*, new.*;
CREATE
regression=# update vvv set f22 = 43 where f2 = 61728 ;
UPDATE 0
regression=# select * from vvv_dummy;
f1 | f2 | f22 | new_f1 | new_f2 | new_f22
--------+-------+--------+--------+--------+---------
123456 | 61728 | 123458 | 123456 | 61728 | 43
(1 row)
I didn't bother to make an ON INSERT trigger for vvv_dummy, but if I had
one, it would have received the data shown here as inserted into vvv_dummy.
In short: by using a dummy table you can get the effect of a trigger
applied to a view.
regards, tom lane