pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
nextval on insert by arbitrary sequence
9+ messages / 4 participants
[nested] [flat]

* nextval on insert by arbitrary sequence
@ 2001-07-20 02:17  Dado Feigenblatt <dado@wildbrain.com>
  0 siblings, 2 replies; 9+ messages in thread

From: Dado Feigenblatt @ 2001-07-20 02:17 UTC (permalink / raw)
  To: pgsql-sql


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.






^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: nextval on insert by arbitrary sequence
@ 2001-07-20 03:47  Josh Berkus <josh@agliodbs.com>
  parent: Dado Feigenblatt <dado@wildbrain.com>
  1 sibling, 1 reply; 9+ messages in thread

From: Josh Berkus @ 2001-07-20 03:47 UTC (permalink / raw)
  To: Dado Feigenblatt <dado@wildbrain.com>; +Cc: pgsql-sql

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







^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: nextval on insert by arbitrary sequence
@ 2001-07-20 07:40  Henry House <hajhouse@houseag.com>
  parent: Dado Feigenblatt <dado@wildbrain.com>
  1 sibling, 1 reply; 9+ messages in thread

From: Henry House @ 2001-07-20 07:40 UTC (permalink / raw)
  To: Dado Feigenblatt <dado@wildbrain.com>; +Cc: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: nextval on insert by arbitrary sequence
@ 2001-07-20 17:10  Dado Feigenblatt <dado@wildbrain.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Dado Feigenblatt @ 2001-07-20 17:10 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-sql

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.







^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: nextval on insert by arbitrary sequence
@ 2001-07-20 17:13  Dado Feigenblatt <dado@wildbrain.com>
  parent: Henry House <hajhouse@houseag.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Dado Feigenblatt @ 2001-07-20 17:13 UTC (permalink / raw)
  To: hajhouse@houseag.com; +Cc: pgsql-sql

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.







^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: nextval on insert by arbitrary sequence
@ 2001-07-20 20:38  Josh Berkus <josh@agliodbs.com>
  parent: Dado Feigenblatt <dado@wildbrain.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Josh Berkus @ 2001-07-20 20:38 UTC (permalink / raw)
  To: Dado Feigenblatt <dado@wildbrain.com>; josh@agliodbs.com; +Cc: pgsql-sql

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 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



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: nextval on insert by arbitrary sequence
@ 2001-07-21 00:04  Dado Feigenblatt <dado@wildbrain.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Dado Feigenblatt @ 2001-07-21 00:04 UTC (permalink / raw)
  To: pgsql-sql; josh berkus <josh@agliodbs.com>

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.







^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: nextval on insert by arbitrary sequence
@ 2001-07-21 04:30  David Stanaway <david@netventures.com.au>
  parent: Dado Feigenblatt <dado@wildbrain.com>
  0 siblings, 0 replies; 9+ messages in thread

From: David Stanaway @ 2001-07-21 04:30 UTC (permalink / raw)
  To: Dado Feigenblatt <dado@wildbrain.com>; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: nextval on insert by arbitrary sequence
@ 2001-07-25 05:57  Henry House <hajhouse@houseag.com>
  parent: Dado Feigenblatt <dado@wildbrain.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Henry House @ 2001-07-25 05:57 UTC (permalink / raw)
  To: Dado Feigenblatt <dado@wildbrain.com>; +Cc: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2001-07-25 05:57 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-20 02:17 nextval on insert by arbitrary sequence Dado Feigenblatt <dado@wildbrain.com>
2001-07-20 03:47 ` Josh Berkus <josh@agliodbs.com>
2001-07-20 17:10   ` Dado Feigenblatt <dado@wildbrain.com>
2001-07-20 20:38     ` Josh Berkus <josh@agliodbs.com>
2001-07-21 00:04       ` Dado Feigenblatt <dado@wildbrain.com>
2001-07-21 04:30         ` David Stanaway <david@netventures.com.au>
2001-07-20 07:40 ` Henry House <hajhouse@houseag.com>
2001-07-20 17:13   ` Dado Feigenblatt <dado@wildbrain.com>
2001-07-25 05:57     ` Henry House <hajhouse@houseag.com>

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