On Thu, 2006-02-02 at 10:16 -0500, Sven Willenberger wrote: > On Thu, 2006-02-02 at 08:58 -0600, Justin Pasher wrote: > > > -----Original Message----- > > > From: Sven Willenberger [mailto:sven@xxxxxxx] > > > Sent: Wednesday, February 01, 2006 2:13 PM > > > To: Justin Pasher > > > Cc: pgsql-general@xxxxxxxxxxxxxx > > > Subject: Re: [GENERAL] Best way to handle table trigger on update > > > > > > > > > On Tue, 2006-01-31 at 13:45 -0600, Justin Pasher wrote: > > > > Postgres 7.4.7 (I know, a little old, but we haven't had a chance to > > > > upgrade) > > > > > > > > I have a table that stores menu items for a side navigation > > > menu for a web > > > > site. Each menu item has a "position" column set that > > > determines where to > > > > put the menu item in the display. At any given time, the > > > menu items should > > > > not have any conflicting positions and should be > > > sequential. For example > > > > > > > > id | name | position > > > > -----+-------------------+---------- > > > > 1 | About Us | 1 > > > > 2 | History | 2 > > > > 3 | Support | 3 > > > > 4 | Job Opportunities | 4 > > > > 5 | Sitemap | 5 > > > > > > > > ... > > > > > > > > I have an UPDATE trigger defined on the table to handle keeping the > > > > positions correct. > > > > > > > > CREATE TRIGGER "update_menu_item" BEFORE UPDATE ON > > > "menu_items" FOR EACH ROW > > > > EXECUTE PROCEDURE update_menu_item(); > > > > > > > > When I update an existing row (say ID 3) with a new > > > position (let's say 1), > > > > the trigger will bump the menu items with a lower position up by one > > > > (position 2 becomes 3, 1 becomes 2) and everything is back > > > to normal. The > > > > catch is the trigger performs this position bumping by > > > making an update on > > > > the menu items table, thus firing the trigger again for > > > each updated row > > > > (and leading to chaos). Currently, the only workaround I > > > have found is to > > > > drop the trigger at the start of the stored procedure, make > > > the updates, > > > > then recreate the trigger. > > > > > > Rather than using a trigger why not create a function to do > > > the update? > > > The following will do the trick with the only modification needed to > > > your table is the addition of the boolean column "isupdate" > > > which should > > > default to false. The two arguments taken by the function are the > > > current position of the intended menu item and its new target > > > position: > > > > > > create or replace function update_menu_item(int,int) returns void as ' > > > update menu_items set isupdate = true where position = $1; > > > update menu_items set position = case when $1 > $2 THEN > > > position +1 when > > > $2 > $1 then position - 1 else position end > > > where position <= case when $1 > $2 then $1 else $2 end and > > > position >= > > > case when $1 > $2 then $2 else $1 end and isupdate = false; > > > update menu_items set position = $2 where position = $1 and isupdate; > > > update menu_items set isupdate = false where isupdate = true; > > > ' > > > LANGUAGE sql volatile; > > > > > > Then if you want to move Job Opportunities from position 4 to position > > > 2, just call the function: > > > select update_menu_item(4,2); > > > > > > HTH, > > > > > > Sven > > > > > > This would work, but my goal is to create something that is transparent to > > the user that is inserting the data (i.e. they perform a normal > > INSERT/UPDATE on the table and "It Just Works"). Thanks for the suggestion. > > > > > > Justin Pasher > > > In that case you could create a rule: ON INSERT ... DO INSTEAD ... (and > ON UPDATE ... DO INSTEAD ...) and invoke the function that way. Barring > that, I think that dropping the trigger and re-adding it the way you > have done is about the only way to avoid all that recursion. > > Sven I just realize that this won't work as it suffers the same recursion problem that the trigger does. Interesting puzzle for which I believe you have already found the optimal solution.
