Message-Id: <6.0.0.22.0.20031124155114.01ba5008@localhost>
Date: Mon, 24 Nov 2003 15:51:37 +0100
To: pgsql-sql@postgresql.org
From: Bruno De Fraine <defraine@pi.be>
Subject: Maintaining an order for a group of records
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

Hello group,

I'm looking for a neat way to accomplish the following with PostgreSQL. In 
a table, I have a group of records for which I want to store an order. The 
order has nothing to do with the data of the records; on the contrary, the 
order of the records might change without the (other) data getting updated. 
It should be easy to rearrange the order of the records, cover add/delete 
of new records, etc. And of course, I should be able to retrieve the 
records in order.

The first way I think of, is adding an extra column to the table, to 
contain an integer attribute "position" (+unique constraint). This makes 
retrieving the records in order as easy as adding "ORDER BY position" to 
the query. However, it can be fairly tedious to maintain from my 
application when the order is changed, unless... some triggers would do 
that automatically. For example, if an update is issued to change the 
position of a record to X, a trigger could check if there is already a 
record in this position, and if so, move this other record to position X+1, 
and so one.

To declare what I want a little more: if there are N records in the group, 
after every UPDATE / DELETE / INSERT statement, triggers should make sure 
there is an order assigned through "position" values 1 up to N, giving 
preference to:
1) The new position given in the UPDATE / INSERT statement
2) The old position that was already present in the table before the 
statement was issued

As I'm quite new to PL/pgSQL and trigger programming, I would like to know 
if anyone has ever seen something like this implemented. I found a start at 
http://prosodic.ods.org/?v=blog&d=2003.11.07&item=3, but I'd like to do 
more than that.

Moreover, I'd like to know better or easier ways to do this, if they exist.

Thanks!
Bruno.