Re: Way to stop recursion?
От | Pierre-Frédéric Caillaud |
---|---|
Тема | Re: Way to stop recursion? |
Дата | |
Msg-id | opsh3e2ph5cq72hf@musicbox обсуждение исходный текст |
Ответ на | Re: Way to stop recursion? (Andrew Sullivan <ajs@crankycanuck.ca>) |
Ответы |
Re: Way to stop recursion?
|
Список | pgsql-sql |
> 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 <ajs@crankycanuck.ca> 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 >
В списке pgsql-sql по дате отправления: