Re: Why should my rule be conditional?

Thiemo Kellner <thiemo(at)thiam(dot)ch> writes:
>> The above looks like a dead end to me; you can't make it work, and the
>> reason is that OLD and NEW are defined with reference to the view. Once
>> you do the UPDATE, that row is no longer visible in the view (correct?)
>> and so there is no NEW row and the INSERT doesn't do anything.

> Thats right, I didn't take this into account assuming that NEW and OLD woul=
> d=20
> be unchangeable for the execution of the rule. Then again, why does PostgrS=
> QL=20
> not complain about not being able to insert null (NEW.song_id) into song_id=
> =20
> (this is the pimary key of t_songs)?

It's not trying to insert a row of nulls; it's simply not inserting any
row at all. The transformed rule query looks like
INSERT INTO t_songs SELECT ... FROM v_songs WHERE ...
and the WHERE condition is such that no rows will be selected.

> Or if not, I think it ought to complain that NEW.* is n=
> ot=20
> valid any longer.

No more than selecting from an empty table is invalid, or selecting with
a WHERE condition that matches no rows is invalid.

> Would it be a great loss to depracate rules?

The fact they don't do what you want doesn't make them worthless ...

There is some discussion of rules vs triggers in the docs:
http://www.postgresql.org/docs/7.4/static/rules-triggers.html
though I agree that this page is probably fairly unhelpful for
novices, and could stand to be rewritten. In particular it focuses
too much on the question of efficiency and doesn't really explain the
very fundamental semantic differences.

regards, tom lane