> I'm experimenting with a set of triggers to automagically maintain
> ltrees-organized tables. I almost have it working, except for a pesky
> problem with re-ordering groups.
> Currently I'm doing this by only cascade-updating the row adjacent to
the
> one I'm moving. However, this is resulting in a cycle, and I don't
see
> how to break it. Namely:
> So I'm trying to come up with a way to ensure that each row is visited only
> once, but it doesn't seem to be possible. Ideas?
I've played this game. Not elegant, but workable. Don't use an update
trigger.
Have an Insert trigger. From the client do a DELETE and INSERT to move A
to 3 instead of an update.
Within that trigger use updates -- thus no cascade.
Option #2 is equally un-elegant and works best for a 'session' flag. Use
a sequences state as a boolean value.
Have trigger #1 grab a value from the sequence and fix all of the data.
Have the cascaded triggers use a PG_TRY {} to determine if it can
successfully call currval() or not. If it can, then the trigger has
already run. If not, then it should do the work.
Option #3, probably better than #2 but I've not used it before: declare
a standard named cursor.
If the cursor exists then your cascaded triggers can read it for the
work that they should do (nothing in this case) (test with PG_TRY{}).
If the cursor does not exist then the trigger should make a cursor with
instructions, do the work (cascades to sub-triggers), and remove the
cursor.
Named cursors are better than temporary tables because they don't cause
system table bloat.
--