Way to stop recursion?
От | Jonathan Knopp |
---|---|
Тема | Way to stop recursion? |
Дата | |
Msg-id | 41A79A2A.6070606@delegated.net обсуждение исходный текст |
Ответы |
Re: Way to stop recursion?
Re: Way to stop recursion? |
Список | pgsql-sql |
Been banging my head against the wall for days and starting to think there is no way to do what I need. Hoping someone on here can prove me wrong. 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: CREATE TABLE parent (id INT, cola CHAR(1), common CHAR(1)); CREATE TABLE child (id INT, parent_id INT, cola(1), common(1)); INSERT INTO parent VALUES(1, 'adult', 0); INSERT INTO child VALUES(1, 1, 'kid 1', 0); INSERT INTO child VALUES(2, 1, 'kid 2', 0); What I need, is when "common" is changed for a parent, then that new value is reflected in "common" for all the children, ie: UPDATE parent SET cola='something', common=1 WHERE id=1; That in itself is no problem: CREATE RULE update_child_common AS ON UPDATE TO parent WHERE NEW.common!=OLD.common DO UPDATE child SET common=NEW.common WHERE parent_id=OLD.id; Problem is, when "common" is changed for a child, I need the parent and all siblings to reflect that value too, ie: UPDATE child SET cola='some value',common=2 WHERE id=2; If I could force recursion off, I could do that with: CREATE RULE update_common_from_child AS ON UPDATE TO child WHERE NEW.common!=OLD.common DO (UPDATE parent SET common=NEW.common WHERE id=NEW.parent_id;UPDATE child SET common=NEW.common WHERE parent_id=NEW.parent_id) As it stands, I can not find a way to do that. Any variation I try (using "flags", using INSTEAD, triggers) has led to recursion protection kicking in and postgresql refusing to run the query. I want to stay away from triggers if I can as I imagine they must be significantly less efficient when updating large numbers of parents and/or children at once (which happens frequently in the application), assuming a trigger could be made to do what I need at all. Hoping I'm missing something obvious... - Jonathan
В списке pgsql-sql по дате отправления: