Thread: integrity of column used to order rows

hello,


I was hoping someone might be able to help me with this problem...

I have a table that is essentially joined back to a parent table.  I have a
column in the child table called "rank" that is a simple integer data type, used
to indicate the order that the child elements should be displayed in.

What I want to be able to do is make sure that at all times the child records
linked to a parent record have values for the "rank" field that are consecutive
starting at 1, ie (1,2,4,5,6) not (1,2,4,5,7).

Can someone offer the best suggestion to do this?  

Should I write a PL/pgsql function and add it as a column constraint to check to
make sure the numbers are consecutive?

Can I use some kind of trigger that will execute a function and "automatically"
give the fields the correct number?  Would this seriously impact the performance
since it would have to go through what might become a large table only to work on
a small part of it (ie, records with the same parent_id)?

I have a lot of experience with mySQL but a lot of these more sophisticated pgSQL
features are a little tough for me to get a handle on immediately... thanks very
much.


-Cliff

On Wednesday March 19 2003 9:18, cliff@cliffmeyers.com wrote:
>
> What I want to be able to do is make sure that at all times the child
> records linked to a parent record have values for the "rank" field that
> are consecutive starting at 1, ie (1,2,4,5,6) not (1,2,4,5,7).
>
> Can someone offer the best suggestion to do this?

This is certainly possible via a triggered PL/pgSQL function.  Of course,
whether or not it is feasible w/r/t performance or exactly how you choose
to set the ranks depends on your context.  If you have appropriate indices
on the foreign key to the parent table, the overall size of the table is
probably irrelevant; use of the index would allow you to avoid traversal of
the entire table.  You also have the option of writing the function in C to
get better performance.

Ed