Re: 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

P.S. The function is pretty rough and I am sure could be better
optimized, but you get the idea of the logic flow there.