CREATE OR REPLACE FUNCTION public.update_many_unit_has_many_person_func()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  many_unit_has_many_person_table text;
BEGIN
  IF (NEW.filter_type = 'person') THEN
    many_unit_has_many_person_table = TG_TABLE_SCHEMA || '.many_unit_has_many_person';
    IF (TG_OP = 'INSERT') THEN
      EXECUTE format('INSERT INTO %s (id_unit, id_person, id_user) VALUES (%L, %L, %L) ON CONFLICT (id_unit, id_person) DO NOTHING', many_unit_has_many_person_table, NEW.id_unit, NEW.id_filter, NEW.id_user);
    ELSIF (TG_OP = 'DELETE') THEN
      EXECUTE format('DELETE FROM %s WHERE id_unit = %L and id_person = %L', many_unit_has_many_person_table, OLD.id_unit, OLD.id_filter);
    ELSIF (TG_OP = 'UPDATE') THEN
      EXECUTE format('UPDATE %s SET id_person = %L WHERE id_unit = %L AND id_user = %L', many_unit_has_many_person_table, NEW.id_filter, NEW.id_unit, NEW.id_user);
    END IF;
  END IF;
  RETURN NULL;
END;
$$;
CREATE OR REPLACE FUNCTION public.update_person_item_count_func()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
BEGIN
  INSERT INTO public.person_item_count
    SELECT face.id_person, face.id_user, count(DISTINCT unit.id_item) AS item_count
        FROM face, unit
      WHERE unit.id = face.id_unit AND unit.id_user = face.id_user AND unit.is_major = true AND
        face.id_person IN (SELECT id_person FROM new_face UNION ALL SELECT id_person FROM old_face)
    GROUP BY face.id_person, face.id_user
  ON CONFLICT (id_person, id_user) DO UPDATE SET item_count = excluded.item_count;
  DELETE FROM public.person_item_count WHERE id_person IN (
    SELECT id_person FROM old_face WHERE old_face.id_unit > 0 EXCEPT (
      SELECT id_person FROM face WHERE face.id_unit > 0
    )
  );
  RETURN NULL;
END;
$$;