X-Original-To: pgsql-sql@postgresql.org Received: from c001.snv.cp.net (h007.c001.snv.cp.net [209.228.32.121]) by postgresql.org (Postfix) with SMTP id 3E295476241 for ; Wed, 19 Mar 2003 11:18:37 -0500 (EST) Received: (cpmta 5037 invoked from network); 19 Mar 2003 08:18:40 -0800 Received: from 209.228.32.137 (HELO mail.cliffmeyers.com.criticalpath.net) by smtp.register-admin.com (209.228.32.121) with SMTP; 19 Mar 2003 08:18:40 -0800 X-Sent: 19 Mar 2003 16:18:40 GMT Received: from [66.202.79.98] by mail.cliffmeyers.com with HTTP; Wed, 19 Mar 2003 08:18:36 -0800 (PST) Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 7bit MIME-Version: 1.0 To: pgsql-sql@postgresql.org From: cliff@cliffmeyers.com Subject: integrity of column used to order rows X-Sent-From: cliff@cliffmeyers.com Date: Wed, 19 Mar 2003 08:18:36 -0800 (PST) X-Mailer: Web Mail 5.2.3-0_sol28 Message-Id: <20030319081840.6919.h022.c001.wm@mail.cliffmeyers.com.criticalpath.net> X-Archive-Number: 200303/464 X-Sequence-Number: 12503 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