-- deal with new content
create or replace function content_insert() returns trigger
security definer
language plpgsql
as $$
begin
insert into content_mimetype_to_index(id)
values(new.sha1);
return new;
end;
$$;
drop trigger if exists after_content_insert_cascade_mimetype on content;
create trigger after_content_insert_cascade_mimetype after insert on content
for each row execute procedure content_insert();
-- deal with new content_mimetype
create or replace function content_mimetype_insert() returns trigger
security definer
language plpgsql
as $$
begin
-- FIXME: Deal with existing data (due to insert on existing data)
insert into content_mimetype_to_index(id, mimetype)
select new.id, new.mimetype
from content_mimetype_to_index cmi inner join content_mimetype cm
on cmi.id = cm.id
values(new.id, new.mimetype);
return new;
end;
$$;
drop trigger if exists after_content_mimetype_insert_cascade_mimetype on content_mimetype;
create trigger after_content_mimetype_insert_cascade_mimetype after insert on content_mimetype
for each row execute procedure content_mimetype_insert();
-- deal with content_mimetype update
create or replace function content_mimetype_update() returns trigger
security definer
language plpgsql
as $$
begin
update content_mimetype_to_index
set mimetype=new.mimetype
where id=new.id;
return new;
end;
$$;
drop trigger if exists after_content_mimetype_update_cascade_mimetype on content_mimetype;
create trigger after_content_mimetype_update_cascade_mimetype after insert on content_mimetype
for each row execute procedure content_mimetype_update();
-- bootstrap values initialization
insert into content_mimetype_to_index(id, mimetype)
select c.sha1 as id, cm.mimetype as mimetype
from content c tablesample system(0.0001) left join content_mimetype cm on c.sha1=cm.id;
-- build index for reading unindexed mimetype
create index content_mimetype_to_index_idx
on content_mimetype_to_index(id)
where mimetype is null;
create unique index content_mimetype_to_index_idx
on content_mimetype_to_index(id, mimetype)