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 [EMAIL PROTECTED] >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]