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.
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
