Re: rules on INSERT can't UPDATE new instance?

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I thought an INSERT rule with an UPDATE action would work on the same
> table, but that fails.  Seems the rule is firing before the INSERT
> happens.

Yes, a trigger is the right way to do surgery on a tuple before it is
stored.  Rules are good for generating additional SQL queries that will
insert/update/delete other tuples (usually, but not necessarily, in
other tables).  Even if it worked, a rule would be a horribly
inefficient way to handle modification of the about-to-be-inserted
tuple, because (being an independent query) it'd have to scan the table
to find the tuple you are talking about!

The reason the additional queries are done before the original command
is explained thus in the source code:

     * The original query is appended last if not instead
     * because update and delete rule actions might not do
     * anything if they are invoked after the update or
     * delete is performed. The command counter increment
     * between the query execution makes the deleted (and
     * maybe the updated) tuples disappear so the scans
     * for them in the rule actions cannot find them.

This seems to make sense for UPDATE/DELETE, but I wonder whether
the ordering should be different for the INSERT case: perhaps it
should be original-query-first in that case.

            regards, tom lane