From: Bruno De Fraine <defraine@pi.be>
To: pgsql-sql@postgresql.org
Subject: Maintaining an order for a group of records
Date: Fri, 21 Nov 2003 21:54:29 +0100
Message-ID: <6.0.0.22.0.20031121212703.01b6d490@localhost> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: defraine@pi.be
Subject: Re: Maintaining an order for a group of records
In-Reply-To: <6.0.0.22.0.20031121212703.01b6d490@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox