You have to do this with a trigger. The problem is that the rule is expanded inline like a macro, so you can't prevent the behaviour you're seeing.
True, but you can get out of the hole in another way :
- Change the name of your table to "hidden_table"
- Create a view which is a duplicate of your table : CREATE VIEW visible_table AS SELECT * FROM hidden_table;
-> Your application now accesses its data without realizing it goes through a view.
Now create a rule on this view, to make it update the real hidden_table. As the rule does not apply to hidden_table, it won't recurse.
Other solution (this similar to what Tom Lane proposed I think) :
Create a field common_id in your table, with
- an insert trigger which puts a SERIAL default value if there is no parent, or copies the parent's value if there is one
- an update trigger to copy the new parent's common_id whenever a child changes parent (if this ever occurs in your design)
Now create another table linking common_id to the 'common' value.
Create a view which joins the two, which emulates your current behaviour.
Create an ON UPDATE rule to the view which just changes one row in the link table.
If you do a lot of selects, solution #1 will be faster, if you do a lot of updates, #2 will win...
Just out of curiosity, what is this for ?
On Fri, 26 Nov 2004 16:34:48 -0500, Andrew Sullivan <[EMAIL PROTECTED]> wrote:
On Fri, Nov 26, 2004 at 01:03:38PM -0800, Jonathan Knopp wrote:UPDATE rules work perfectly for what I need to do except I need them to only run once, not try and recurse (which of course isn't allowedby postgresql anyway). Triggers seem a less efficient way to do the same thing, though I understand they would run recursively too. Here's the table structure in question:
You have to do this with a trigger. The problem is that the rule is expanded inline like a macro, so you can't prevent the behaviour you're seeing.
A
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
