Re: Sequence question

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(at)postgresql(dot)org
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>