Re: RULE with conditional behaviour?

Rob Hoopman <rob(at)tuna(dot)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