- 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...
The "hidden table" method should work just fine. Ingenius idea, thank you!
Just out of curiosity, what is this for ?The actual application has companies instead of parents, employees instead of children, then emails as children of employees and/or companies, and folders as parents of companies and employees. The "common" field (in all 4 layers) are a pair of permissions flags.
May I humbly suggest two possible todo's for postgreSQL: a simple flag to suppress recursion (easier/more powerful way of doing the above), and/or more direct access to query rewriting. Seems right now rules require you to rewrite queries while partially blind to them. Being able to rewrite queries in statement triggers similar to what can be done with row triggers would be very nice too.
---------------------------(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
