pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feednextval 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>
2001-07-20 03:47 ` Re: nextval on insert by arbitrary sequence Josh Berkus <josh@agliodbs.com>
2001-07-20 07:40 ` Re: nextval on insert by arbitrary sequence Henry House <hajhouse@houseag.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 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 ` Re: nextval on insert by arbitrary sequence 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 02:17 nextval on insert by arbitrary sequence Dado Feigenblatt <dado@wildbrain.com>
2001-07-20 03:47 ` Re: nextval on insert by arbitrary sequence Josh Berkus <josh@agliodbs.com>
@ 2001-07-20 17:10 ` Dado Feigenblatt <dado@wildbrain.com>
2001-07-20 20:38 ` Re: nextval on insert by arbitrary sequence 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 02:17 nextval on insert by arbitrary sequence Dado Feigenblatt <dado@wildbrain.com>
2001-07-20 03:47 ` Re: nextval on insert by arbitrary sequence Josh Berkus <josh@agliodbs.com>
2001-07-20 17:10 ` Re: nextval on insert by arbitrary sequence Dado Feigenblatt <dado@wildbrain.com>
@ 2001-07-20 20:38 ` Josh Berkus <josh@agliodbs.com>
2001-07-21 00:04 ` Re: nextval on insert by arbitrary sequence 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-20 02:17 nextval on insert by arbitrary sequence Dado Feigenblatt <dado@wildbrain.com>
2001-07-20 03:47 ` Re: nextval on insert by arbitrary sequence Josh Berkus <josh@agliodbs.com>
2001-07-20 17:10 ` Re: nextval on insert by arbitrary sequence Dado Feigenblatt <dado@wildbrain.com>
2001-07-20 20:38 ` Re: nextval on insert by arbitrary sequence Josh Berkus <josh@agliodbs.com>
@ 2001-07-21 00:04 ` Dado Feigenblatt <dado@wildbrain.com>
2001-07-21 04:30 ` Re: nextval on insert by arbitrary sequence David Stanaway <david@netventures.com.au>
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-20 02:17 nextval on insert by arbitrary sequence Dado Feigenblatt <dado@wildbrain.com>
2001-07-20 03:47 ` Re: nextval on insert by arbitrary sequence Josh Berkus <josh@agliodbs.com>
2001-07-20 17:10 ` Re: nextval on insert by arbitrary sequence Dado Feigenblatt <dado@wildbrain.com>
2001-07-20 20:38 ` Re: nextval on insert by arbitrary sequence Josh Berkus <josh@agliodbs.com>
2001-07-21 00:04 ` Re: nextval on insert by arbitrary sequence Dado Feigenblatt <dado@wildbrain.com>
@ 2001-07-21 04:30 ` David Stanaway <david@netventures.com.au>
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-20 02:17 nextval on insert by arbitrary sequence Dado Feigenblatt <dado@wildbrain.com>
@ 2001-07-20 07:40 ` Henry House <hajhouse@houseag.com>
2001-07-20 17:13 ` Re: nextval on insert by arbitrary sequence 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 02:17 nextval on insert by arbitrary sequence Dado Feigenblatt <dado@wildbrain.com>
2001-07-20 07:40 ` Re: nextval on insert by arbitrary sequence Henry House <hajhouse@houseag.com>
@ 2001-07-20 17:13 ` Dado Feigenblatt <dado@wildbrain.com>
2001-07-25 05:57 ` Re: nextval on insert by arbitrary sequence 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 02:17 nextval on insert by arbitrary sequence Dado Feigenblatt <dado@wildbrain.com>
2001-07-20 07:40 ` Re: nextval on insert by arbitrary sequence Henry House <hajhouse@houseag.com>
2001-07-20 17:13 ` Re: nextval on insert by arbitrary sequence Dado Feigenblatt <dado@wildbrain.com>
@ 2001-07-25 05:57 ` Henry House <hajhouse@houseag.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