Re: Best way to handle table trigger on update - Mailing list pgsql-general

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@dmv.com]
> > > Sent: Wednesday, February 01, 2006 2:13 PM
> > > To: Justin Pasher
> > > Cc: pgsql-general@postgresql.org
> > > 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.