From: John Sidney-Woollett <johnsw@wardbrook.com>
To: johnsw@wardbrook.com
Cc: Anthony Best <abest@digitalflex.net>
Cc: pgsql-general@postgresql.org
Subject: Re: Sequence question
Date: Wed, 17 Dec 2003 08:08:05 -0000 (GMT)
Message-ID: <1319.192.168.0.64.1071648485.squirrel@mercury.wardbrook.com> (raw)
In-Reply-To: <1294.192.168.0.64.1071647844.squirrel@mercury.wardbrook.com>
References: <3FDFAD8F.5080404@digitalflex.net>
<1294.192.168.0.64.1071647844.squirrel@mercury.wardbrook.com>
Sorry I should have added that the trigger needs to create a new keyword
record if the join in the trigger fails to locate the keyword in the
keyword table.
Hopefully you can create the trigger yourself.
The keyword table is effectively a distinct list of all keywords inserted
into the data table with the associated last allocated sequence number for
the keyword.
John
John Sidney-Woollett said:
> 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
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
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: <1319.192.168.0.64.1071648485.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