most efficient way to manage ordering - Mailing list pgsql-sql

Sorry if this is confusing, it is somewhat difficult to explain.

I find myself frequently creating solutions to the same problem.  I'm not
yet happy with the way I've done any of them and I'd like to find a purely
SQL way of doing this if possible.

Here's what I have.  For a contrived illustration, let's say we have a
database of photo galleries, each having some number of images.  Our tables
would look like this:

galleries
-------------------------
galleryid    | int4 (pkey)
name        | text


images
-------------------------
imageid    | int4 (pkey)
galleryid    | int4 (fkey)
image        | text
dsply_order    | int4 (index)


Now, the same database holds many different galleries.  Each gallery has
some number of images and the users want the images to show in a certain
order. This is done by inserting the images in the order you want them to
appear.  However, it may be necessary for the user to re-order them.  I
provide a MOVE UP, MOVE DOWN, MOVE TO TOP and MOVE TO BOTTOM option so that
they can change the order.  Also, people occasionally delete images.

If a person maintaining galleryid 1 which has 6 images, a "SELECT * FROM
images WHERE galleryid = 1 ORDER BY dsply_order" might show this:
imageid | galleryid | image      | dsply_order
--------+-----------+----------+------------
4      | 1          | 1/me.gif | 1
7      | 1          | 1/aa.gif | 2
12      | 1          | 1/bb.gif | 3
11      | 1          | 1/cc.gif | 4
15      | 1          | 1/dd.gif | 5
18      | 1          | 1/ee.gif | 6

Now, when a person decide to re-order, it's no problem to do this:
To move imageid 12 to the top:
UPDATE images SET dsply_order = CASE WHEN imageid = 12 THEN 1 ELSE
dsply_order + 1 END WHERE galleryid = 1;

That however leaves a gap at dsply_order 4: 
imageid | galleryid | image      | dsply_order
--------+-----------+----------+------------
12      | 1          | 1/bb.gif | 1
4      | 1          | 1/me.gif | 2
7      | 1          | 1/aa.gif | 3
11      | 1          | 1/cc.gif | 5
15      | 1          | 1/dd.gif | 6
18      | 1          | 1/ee.gif | 7

Similar problem occurs when someone deletes an item.

Ideally, I'd like to figure out a single SQL query that can be run
afterwards to clean up the dsply_order to make sure that each number occurs
only one time and that there are no gaps.

I know I can write a sp for this, but the problem is, I do this very task on
lots of different tables that all have different formats and different types
of data.  I'd rather not have to maintain many different procedures if I can
find an alternate.

Right now, I get the job done in code, but it feels inefficient.

Matthew Nuzum        | ISPs: Make $200 - $5,000 per referral by
www.followers.net        | recomending Elite CMS to your customers!
matt@followers.net    | http://www.followers.net/isp