CREATE OR REPLACE FUNCTION public.delete_item_to_update_normal_album_trigger_func()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
    album_id integer;
    item_id integer;
    album_start_time bigint;
    album_end_time bigint;
    unit_time bigint;
    unit_table text;
    normal_album_table text;
    album_takentime_max bigint;
    album_takentime_min bigint;
BEGIN
  unit_table = TG_TABLE_SCHEMA || '.unit';
  normal_album_table = TG_TABLE_SCHEMA || '.normal_album';
  album_id = OLD.id_normal_album;
  item_id  = OLD.id_item;
  EXECUTE 'UPDATE ' || normal_album_table || ' SET item_count = COALESCE(item_count, 0) - 1 WHERE id =' || album_id;
  EXECUTE 'SELECT takentime FROM ' || unit_table || ' WHERE id_item = ' || item_id INTO unit_time;
  EXECUTE 'SELECT start_time, end_time FROM ' || normal_album_table ||
          ' WHERE id =' || album_id INTO album_start_time, album_end_time;
  IF (unit_time IS NULL OR unit_time = album_start_time OR unit_time = album_end_time) THEN
    EXECUTE format('SELECT max, min FROM public.get_unit_takentime_by_album(%L, %L)', album_id, TG_TABLE_SCHEMA) INTO album_takentime_max, album_takentime_min;
    EXECUTE 'UPDATE ' || normal_album_table || COALESCE(' SET start_time = ' || album_takentime_min, ' SET start_time = NULL') || COALESCE(', end_time = ' || album_takentime_max, ', end_time = NULL') || ' WHERE id = ' || album_id;
  END IF;
  RETURN NULL;
END
$$;
CREATE OR REPLACE FUNCTION public.insert_item_to_update_normal_album_func(id_album int4)
  RETURNS void
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  many_table text;
  normal_album_table text;
  max_time bigint;
  min_time bigint;
  count integer;
BEGIN
  normal_album_table = 'public.normal_album';
  many_table = 'public.many_item_has_many_normal_album';
  EXECUTE 'SELECT count(*) FROM ' || many_table || ' WHERE id_normal_album = ' || id_album INTO count;
  EXECUTE format('SELECT max, min FROM public.get_unit_takentime_by_album(%L, %L)', id_album, 'public') INTO max_time, min_time;
  EXECUTE 'UPDATE ' || normal_album_table || COALESCE(' SET start_time = ' || min_time, ' SET start_time = NULL') || COALESCE(', end_time = ' || max_time, ', end_time = NULL') || ', item_count = ' || count || ' WHERE id = ' || id_album;
END;
$$;
CREATE OR REPLACE FUNCTION public.update_normal_album_time_trigger_func()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  id_item integer;
  many_table text;
  normal_album_table text;
  max_time bigint;
  min_time bigint;
  record RECORD;
BEGIN
  id_item  = OLD.id_item;
  normal_album_table = TG_TABLE_SCHEMA || '.normal_album';
  many_table = TG_TABLE_SCHEMA || '.many_item_has_many_normal_album';
  FOR record IN EXECUTE format('SELECT * FROM %s WHERE id_item = %L', many_table, id_item) LOOP
    EXECUTE format('SELECT max, min FROM public.get_unit_takentime_by_album(%L, %L)', record.id_normal_album, TG_TABLE_SCHEMA) INTO max_time, min_time;
    EXECUTE 'UPDATE ' || normal_album_table || COALESCE(' SET start_time = ' || min_time, ' SET start_time = NULL') || COALESCE(', end_time = ' || max_time, ', end_time = NULL') || ' WHERE id = ' || record.id_normal_album;
  END LOOP;
  RETURN NULL;
END;
$$;
CREATE OR REPLACE FUNCTION public.update_condition_album_time_trigger_func()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  unit_time bigint;
  condition_album_table text;
  max_time bigint;
  min_time bigint;
  record RECORD;
BEGIN
  unit_time = OLD.takentime;
  condition_album_table = TG_TABLE_SCHEMA || '.condition_album';
  FOR record IN EXECUTE format('SELECT * FROM %s WHERE start_time = %L OR end_time = %L', condition_album_table, unit_time, unit_time) LOOP
    EXECUTE format('SELECT max, min FROM public.get_unit_takentime_by_condition_album(%L)', record.id) INTO max_time, min_time;
    EXECUTE 'UPDATE ' || condition_album_table || COALESCE(' SET start_time = ' || min_time, ' SET start_time = NULL') || COALESCE(', end_time = ' || max_time, ', end_time = NULL') || ' WHERE id = ' || record.id;
  END LOOP;
  RETURN NULL;
END;
$$;
CREATE OR REPLACE FUNCTION public.insert_item_to_update_condition_album_func(id_album integer)
  RETURNS void
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  item_table text;
  condition_album_table text;
  max_time bigint;
  min_time bigint;
  count integer;
BEGIN
  condition_album_table = 'public.condition_album';
  item_table = 'public.condition_album_item_' || id_album;
  EXECUTE 'SELECT count(*) FROM ' || item_table INTO count;
  EXECUTE format('SELECT max, min FROM public.get_unit_takentime_by_condition_album(%L)', id_album) INTO max_time, min_time;
  EXECUTE 'UPDATE ' || condition_album_table || COALESCE(' SET start_time = ' || min_time, ' SET start_time = NULL') || COALESCE(', end_time = ' || max_time, ', end_time = NULL') || ', item_count = ' || count || ' WHERE id = ' || id_album;
END;
$$;