Adding logic to a PostgreSQL stored procedure -

i'm using postgresql (8.3+) , have defined enum , table follows:

create type "viewer_action" enum ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h');  create table "preferences"  (      "user_id"       integer not null,     "item_id"       integer not null,     "rating"        viewer_action not null,     "time_created"  timestamp not null default current_timestamp,     primary key ("user_id","video_id") ); 

i've created stored procedure upsert new rows preferences table, using example http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#plpgsql-upsert-example:

create or replace function add_preference(u int, int, r viewer_action) returns void $add_preference$     begin         loop             -- first try update key             update preferences set rating = r user_id = u , item_id = i;             if found                 return;             end if;             -- not there, try insert key             -- if else inserts same key concurrently,             -- unique-key failure             begin                 insert preferences(user_id,item_id,rating) values (u,i,r);                 return;             exception when unique_violation                 -- nothing, , loop try update again             end;         end loop;     end; $add_preference$ language plpgsql; 

i need add additional logic upsert prevent values overwriting other values. specifically:

• a can overwritten b, can overwritten c, can overwritten d, , on through f. b cannot overwritten a, nor c overwritten b, etc.
• f, g, or h can overwrite value, regardless if existing value lower or higher.

in pseudocode, might like:

if (rating >= f) {     insert; } else if (rating > existing_rating) {     insert; } else {     return; } 

create trigger on before insert or update on table. use function, small changes changing return type trigger.

the before triggers fired before inserting rows tables, can check data before writing them.

more information on triggers can find here:
http://www.postgresql.org/docs/9.0/interactive/plpgsql-trigger.html