Re: nextval on insert by arbitrary sequence

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(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco