Received: from flip.wildbrain.com ([206.54.55.32]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6KHBpa04692 for ; Fri, 20 Jul 2001 13:11:51 -0400 (EDT) (envelope-from dado@wildbrain.com) Received: from wildbrain.com (daedelus.wildbrain.com [192.168.1.38]) by flip.wildbrain.com (8.9.3/8.9.3) with ESMTP id JAA05566; Fri, 20 Jul 2001 09:09:41 -0700 Message-ID: <3B5865E9.30708@wildbrain.com> Date: Fri, 20 Jul 2001 10:10:01 -0700 From: Dado Feigenblatt User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.2) Gecko/20010628 X-Accept-Language: en-us MIME-Version: 1.0 To: josh@agliodbs.com CC: pgsql-sql@postgresql.org Subject: Re: nextval on insert by arbitrary sequence References: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200107/279 X-Sequence-Number: 4082 Josh Berkus wrote: >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). > Although I haven't written any PL/pgSQL function, I think that wouldn't be the hard part here. I don't know how to integrate that with a single INSERT SQL statement. I mean, I always want to use unix backticks ( `sql query`). I wish that was an option. I haven't understood yet the SQL multiple query or subquery thing. >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. > That's not an issue. Rows won't be deleted and once a number is assigned, it's written in stone. Well, if a row was inserted by mistake, I could lock the sequence and, if no other number was picked, reset the counter and throw the bad row away, but that is unlikely to be necessary. >3. None of this makes sense if you intend to re-arrange the rows >according to some external criteria. > Not sure of what you mean here. Reordering? >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. > I might do that if implementation on the sever turns out to be a drag. But I'd like to avoid that as much as possible. I wan't to keep the clients clean so it's easier for people here to hack them. On the other hand, the more obscure the code is, the safer my position here :) -- Dado Feigenblatt Wild Brain, Inc. Technical Director (415) 553-8000 x??? dado@wildbrain.com San Francisco, CA.