Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6K3lVa16880 for ; Thu, 19 Jul 2001 23:47:31 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 88529; Thu, 19 Jul 2001 20:47:25 -0700 From: "Josh Berkus" Subject: Re: nextval on insert by arbitrary sequence To: Dado Feigenblatt Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Thu, 19 Jul 2001 20:47:25 -0700 Message-ID: In-Reply-To: <3B5794B0.8060201@wildbrain.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="_===88529====davinci.ethosmedia.com===_" X-Archive-Number: 200107/263 X-Sequence-Number: 4060 This is a multi-part MIME message --_===88529====davinci.ethosmedia.com===_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit Dado, > I'm not sure if I worded the subject right, but my problem is this: > > I have a few entries in one table. Each row is the parent of many > entries in a second table. > In the second table I have a lot of entries referencing the entries > on > the first table. > So far so good. Basic foreign key thing. > The entries on the second table need to be numbered, but instead of a > single sequence for all rows, > I need a sequence per group of rows, according to their parent > record. You *can* do this through PL/pgSQL triggers. *however*, there's a couple of problems with that idea: 1. It would be fairly elaborate for a trigger (i.e. lots of debugging). 2. It would only work for ON INSERT. Deleting one row in the middle could not reasonably be made to make all the rest re-number. 3. None of this makes sense if you intend to re-arrange the rows according to some external criteria. If it were me, I'd do it through interface (or better) middleware code, disabling the user's ability to insert or delete rows directly and forcing them to push inserts and deletes through some kind of function, whether PL/pgSQL or Java-ORB middleware or whatever. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco --_===88529====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===88529====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===88529====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===88529====davinci.ethosmedia.com===_--