set_keywords.sql

CREATE OR REPLACE FUNCTION posting_set_keywords(postingid integer, kw_array text[])
  RETURNS void AS
$BODY$
begin
	-- remove keywords which do not anymore exist
	delete from test.posting_keyword where 
		posting_id=postingid and 
		keyword_id in (
			select pl.keyword_id 
				from test.posting_keyword lk
				join test.keyword k on k.keyword_id = pl.keyword_id
				where pl.posting_id=postingid
				and k.name_lower not in (
					select lower(kw_array[generate_series(array_lower(kw_array, 1), array_upper(kw_array, 1))]) )  ) ;

	-- add keywords which are not in the keyword-table
	insert into test.keyword ("name") (
		select k_name from 
			( select lower(kw_array[generate_series(array_lower(kw_array, 1), array_upper(kw_array, 1))]) as k_name) foo
			where lower(k_name) not in (select name_lower from test.keyword) );

	-- connect the new keywords to this posting
	insert into test.posting_keyword (posting_id, keyword_id)
		(select postingid as posting_id, keyword_id 
			from test.keyword
			where name_lower in 
				(select lower(kw_array[generate_series(array_lower(kw_array, 1), array_upper(kw_array, 1))]) as k_name) 
			and name_lower not in
				(select k2.name_lower from test.keyword k2 
					join test.posting_keyword lk2 on pl2.keyword_id=k2.keyword_id
					where pl2.posting_id=postingid) );
end
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;