Re: nextval on insert by arbitrary sequence

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.