X-Original-To: pgsql-sql@postgresql.org
Received: from mta7.adelphia.net (mta7-0.mail.adelphia.net [64.8.50.193])
	by postgresql.org (Postfix) with ESMTP id 568484758E6
	for <pgsql-sql@postgresql.org>; Wed, 19 Mar 2003 11:50:41 -0500 (EST)
Received: from bingo ([68.70.98.78]) by mta7.adelphia.net
	(InterMail vM.5.01.05.27 201-253-122-126-127-20021220) with ESMTP
	id <20030319165046.VQUP27578.mta7.adelphia.net@bingo>;
	Wed, 19 Mar 2003 11:50:46 -0500
Content-Type: text/plain;
  charset="iso-8859-1"
From: "Ed L." <pgsql@bluepolka.net>
To: cliff@cliffmeyers.com, pgsql-sql@postgresql.org
Subject: Re: integrity of column used to order rows
Date: Wed, 19 Mar 2003 09:50:45 -0700
User-Agent: KMail/1.4.3
References: 
 <20030319081840.6919.h022.c001.wm@mail.cliffmeyers.com.criticalpath.net>
In-Reply-To: 
 <20030319081840.6919.h022.c001.wm@mail.cliffmeyers.com.criticalpath.net>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200303190950.45766.pgsql@bluepolka.net>
X-Archive-Number: 200303/465
X-Sequence-Number: 12504

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,=
=20
whether or not it is feasible w/r/t performance or exactly how you choose=
=20
to set the ranks depends on your context.  If you have appropriate indices=
=20
on the foreign key to the parent table, the overall size of the table is=20
probably irrelevant; use of the index would allow you to avoid traversal of=
=20
the entire table.  You also have the option of writing the function in C to=
=20
get better performance.

Ed