From: John Sidney-Woollett <johnsw@wardbrook.com>
To: Anthony Best <abest@digitalflex.net>
Cc: pgsql-general@postgresql.org
Subject: Re: Sequence question.
Date: Wed, 17 Dec 2003 07:57:24 -0000 (GMT)
Message-ID: <1294.192.168.0.64.1071647844.squirrel@mercury.wardbrook.com> (raw)
In-Reply-To: <3FDFAD8F.5080404@digitalflex.net>
References: <3FDFAD8F.5080404@digitalflex.net>
How about using two tables; one to hold the keyword and its (last
allocated) sequence value, and the second to store your data as below.
create table Keyword (
keyword varchar(32),
sequence integer,
primary key(keyword)
)
create table Data (
id serial,
sequence int,
keyword varchar(32),
text text
)
Add a trigger to the Data table for Insert so that it joins to the
(parent) keyword table and increments the keyword.sequence value, and
places that into the Data.sequence value.
You will get 'holes' in the keyword sequencing when you delete data from
the Data table. If that's a problem then you will need an alternative
design.
Hope that helps.
John Sidney-Woollett
Anthony Best said:
> I'm working on an idea that uses sequences.
>
> I'm going to create a table like this:
>
> id serial,
> sequence int,
> keyword varchar(32),
> text text
>
> for every keyword there will be a uniq sequence for it eg:
>
> id, sequence, keyword
> 1, 1, foo, ver1
> 2, 1, bar, bar ver1
> 3, 2, foo, ver2
> 4, 2, bar, bar ver2
> etc...
>
> I could have one sequence for all keyword which would be 1,3, etc... I
> would be prefer to have them in sequence. I'm sure someone has ran into
> this before, any ideas?
>
> Anthony.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
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-general@postgresql.org
Cc: johnsw@wardbrook.com, abest@digitalflex.net
Subject: Re: Sequence question.
In-Reply-To: <1294.192.168.0.64.1071647844.squirrel@mercury.wardbrook.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