pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dado Feigenblatt <dado@wildbrain.com>
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
Subject: Re: nextval on insert by arbitrary sequence
Date: Fri, 20 Jul 2001 10:10:01 -0700
Message-ID: <3B5865E9.30708@wildbrain.com> (raw)
References: <web-88529@davinci.ethosmedia.com>

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.







view thread (9+ messages)  latest in thread

Message-ID: <3B5865E9.30708@wildbrain.com>
Permalink:  ../3B5865E9.30708@wildbrain.com/
Also on:    postgresql.org/message-id/3B5865E9.30708@wildbrain.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: dado@wildbrain.com, josh@agliodbs.com
  Subject: Re: nextval on insert by arbitrary sequence
  In-Reply-To: <3B5865E9.30708@wildbrain.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox