Thread: nextval on insert by arbitrary sequence
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. I have some ideas but they are not coming together. I thought of creating individual sequences for each new parent row, and maybe store its name in the parent row itself, so it can be accessed by it's children row. But then, how do I get a field on the second table to DEFAULT to nextval() on it's parent's sequence? INSERT using SELECT? TRIGGER? RULE? FUNCTION? Is it best to handle this things entirely on the client ? What is the approach for this problem? Thanks. PS: Hmmm... It doesn't look like I got my terminology right. Sorry for that. -- Dado Feigenblatt Wild Brain, Inc. Technical Director (415) 553-8000 x??? dado@wildbrain.com San Francisco, CA.
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
Attachment
On Thu, Jul 19, 2001 at 07:17:20PM -0700, Dado Feigenblatt wrote:
> 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.
>
> I have some ideas but they are not coming together.
> I thought of creating individual sequences for each new parent row, and
> maybe store its name in the parent row itself, so it can be accessed by
> it's children row.
> But then, how do I get a field on the second table to DEFAULT to
> nextval() on it's parent's sequence?
I have done this manally, by creating a sequence and the grabbing a unique
number from the sequence using 'SELECT nextval('seq_name'). I then use this
number in all the INSERTs.
Even though it takes one extra query, it is a simple approach and PostgreSQL
guarantees that the sequence will return unique, sequential numbers as long
as it is used consistently.
--
Henry House
OpenPGP key available from http://romana.hajhouse.org/hajhouse.asc
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.
Henry House wrote:
>On Thu, Jul 19, 2001 at 07:17:20PM -0700, Dado Feigenblatt wrote:
>
>>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.
>>
>>I have some ideas but they are not coming together.
>>I thought of creating individual sequences for each new parent row, and
>>maybe store its name in the parent row itself, so it can be accessed by
>>it's children row.
>>But then, how do I get a field on the second table to DEFAULT to
>>nextval() on it's parent's sequence?
>>
>
>I have done this manally, by creating a sequence and the grabbing a unique
>number from the sequence using 'SELECT nextval('seq_name'). I then use this
>number in all the INSERTs.
>
>Even though it takes one extra query, it is a simple approach and PostgreSQL
>guarantees that the sequence will return unique, sequential numbers as long
>as it is used consistently.
>
You kind of implied the answer to my question.
You just does it in the client instead of using back stored
functions/triggers or contrived SQL to handle that, right?
Thanks
--
Dado Feigenblatt Wild Brain, Inc.
Technical Director (415) 553-8000 x???
dado@wildbrain.com San Francisco, CA.
Dado, Maybe we're looking at this the hard way. Have you thought of simply putting in a DATETIME column and sorting by that? -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Josh Berkus wrote: > Dado, > > Maybe we're looking at this the hard way. Have you thought of simply > putting in a DATETIME column and sorting by that? > > -Josh > Sorting? I might have expressed myself wrong. I'm not concerned about sorting. I'm concerned about giving rows in a single table an unique, sequential numbered ID based on a sequence per project (as in a serial counter, as in 'create sequence specific_project_sequence;') e.g. rows related to project A get a number from sequence A rows related to project B get a number from sequence B Is it clear now, or is it me who's not understanding what you're saying? Sorry for the confusion. -- Dado Feigenblatt Wild Brain, Inc. Technical Director (415) 553-8000 x??? dado@wildbrain.com San Francisco, CA.
Have you looked at the serial type?
This type creates an explicity sequence with a predictable name:
tblname_rowname_seq
and has a default value that selects the next val from that sequence.
You can get the value of the most recently inserted row in your session
with
CurrVal('tblname_rowname_seq')
Hope this helps :)
On Saturday, July 21, 2001, at 10:04 AM, Dado Feigenblatt wrote:
> Josh Berkus wrote:
>
>> Dado,
>>
>> Maybe we're looking at this the hard way. Have you thought of simply
>> putting in a DATETIME column and sorting by that?
>>
>> -Josh
>>
> Sorting? I might have expressed myself wrong.
> I'm not concerned about sorting.
> I'm concerned about giving rows in a single table an unique, sequential
> numbered ID
> based on a sequence per project (as in a serial counter, as in 'create
> sequence specific_project_sequence;')
> e.g.
> rows related to project A get a number from sequence A
> rows related to project B get a number from sequence B
>
> Is it clear now, or is it me who's not understanding what you're saying?
>
> Sorry for the confusion.
>
>
>
>
>
> -- Dado Feigenblatt Wild Brain, Inc.
> Technical Director (415) 553-8000 x???
> dado@wildbrain.com San Francisco, CA.
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
--
Best Regards
David Stanaway
================================
Technology Manager
Australia's Premier Internet Broadcasters
Phone: +612 9357 1699
Fax: +612 9357 1169
Web: http://www.netventures.com.au
Support: support@netventures.com.au
================================
The Inspire Foundation is proudly supported by Net Ventures through the
provision of streaming solutions for it's national centres. The Inspire
Foundation is an Internet-based foundation that inspires young people to
help themselves, get involved and get online. Please visit Inspire at
http://www.inspire.org.au
On Fri, Jul 20, 2001 at 10:13:04AM -0700, Dado Feigenblatt wrote: [...] > You kind of implied the answer to my question. > You just does it in the client instead of using back stored > functions/triggers or contrived SQL to handle that, right? That's correct. -- Henry House OpenPGP key available from http://romana.hajhouse.org/hajhouse.asc