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.
