Message-ID: <1294.192.168.0.64.1071647844.squirrel@mercury.wardbrook.com>
In-Reply-To: <3FDFAD8F.5080404@digitalflex.net>
References: <3FDFAD8F.5080404@digitalflex.net>
Date: Wed, 17 Dec 2003 07:57:24 -0000 (GMT)
Subject: Re: Sequence question.
From: "John Sidney-Woollett" <johnsw@wardbrook.com>
To: "Anthony Best" <abest@digitalflex.net>
Cc: pgsql-general@postgresql.org
Reply-To: johnsw@wardbrook.com
User-Agent: SquirrelMail/1.4.2 [CVS]
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Importance: Normal

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
>