Re: INSERT RULE QUERY ORDER

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Tuesday, August 05, 2003 2:13 PM
To: Justin Tocci
Cc: 'pgsql-general(at)postgresql(dot)org'
Subject: Re: [GENERAL] INSERT RULE QUERY ORDER

Justin Tocci <jtocci(at)tlcusa(dot)com> writes:
> When my RULE takes the form of:
> CREATE RULE name AS ON INSERT TO table
> DO INSTEAD ( UPDATE query ; INSERT query);

> The INSERT query doesn't fire and there is no error.

What do you mean by "doesn't fire"? I think you'd better provide a
complete example of what you're doing.

regards, tom lane

------------------------------------------

Thanks for the reply Tom, here's the rule that works:

CREATE RULE tquotehistory_update AS ON UPDATE TO vtquotehistory DO INSTEAD
(

INSERT INTO tquotehistory_log ("ID", "Item", "Quote1", "DemandCost1",
"Quote2", "DemandCost2", "DueDate", "POIntoInmass", "Weeks", "QuoteSent",
"Reference", "Supplier", "TLCProposal", "Counter", "Notes") VALUES
(old."ID", old."Item", old."Quote1", old."DemandCost1", old."Quote2",
old."DemandCost2", old."DueDate", old."POIntoInmass", old."Weeks",
old."QuoteSent", old."Reference", old."Supplier", old."TLCProposal",
old."Counter", old."Notes");

UPDATE tquotehistory SET "Item" = new."Item", "Quote1" = new."Quote1",
"DemandCost1" = new."DemandCost1", "Quote2" = new."Quote2", "DemandCost2" =
new."DemandCost2", "DueDate" = new."DueDate", "POIntoInmass" =
new."POIntoInmass", "Weeks" = new."Weeks", "QuoteSent" = new."QuoteSent",
"Reference" = new."Reference", "Supplier" = new."Supplier", "TLCProposal" =
new."TLCProposal", "Counter" = new."Counter", "Notes" = new."Notes" WHERE
(tquotehistory."ID" = old."ID");

);

Switch the order and the INSERT doesn't insert a record into the log, but
the UPDATE updates and there is no error.

The docs say:
13.4.2. How These Rules Work
...The parse trees found in the actions of the pg_rewrite system catalog are
only templates. Since they can reference the range-table entries for NEW and
OLD, some substitutions have to be made before they can be used. For any
reference to NEW, the target list of the original query is searched for a
corresponding entry. If found, that entry's expression replaces the
reference. Otherwise NEW means the same as OLD (for an UPDATE) or is
replaced by NULL (for an INSERT). Any reference to OLD is replaced by a
reference to the range-table entry which is the result relation.
After we are done applying update rules, we apply view rules to the produced
parse tree(s). Views cannot insert new update actions so there is no need to
apply update rules to the output of view rewriting.

But try as I might I don't understand what exactly this means to me. If it
all boils down to keeping your INSERTs before your UPDATEs that's fine I
guess ,but I'd like to understand it. Plus I'd like to know enough about it
to understand where my DELETEs should go.
-------------------------------------------
jtocci
Fort Wayne, IN