Re: integrity of column used to order rows

On Wednesday March 19 2003 9:18, cliff(at)cliffmeyers(dot)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