-- создание триггерной функции

CREATE FUNCTION train_discovery_f () RETURNS trigger  -- '
LANGUAGE 'plpgsql'

AS $BODY$
declare _car_id  character varying ;
_d_date  date;
_car_id_opposite  character varying;
_cars_hash  character varying;
_cars_list  character varying;
_car_id_1  character varying ;
_d_date_1  date;
_car_id_opposite_1  character varying;
_cars_hash_1  character varying;
_cars_list_1  character varying;
_rec_dt  timestamp without time zone ;
_rec_dt_1   timestamp without time zone ;
_cars_count  integer  ;




BEGIN 
-- берем последние две записи по минимальному вагону
    select distinct car_id, car_id_opposite,cars_hash,cars_list,d_date, rec_dt, cars_count
    	into _car_id ,_car_id_opposite,_cars_hash,_cars_list, _d_date, _rec_dt ,_cars_count
           from train_discovery
           where _car_id = (case when NEW.car_id_opposite < NEW.car_id then NEW.car_id_opposite else NEW.car_id end)
           and rec_dt in (select max(rec_dt) 
                          from train_discovery where car_id = case when NEW.car_id_opposite < NEW.car_id then NEW.car_id_opposite else NEW.car_id end)
           ;
   select distinct car_id, car_id_opposite,cars_hash,cars_list,d_date, rec_dt
    	into _car_id_1 ,_car_id_opposite_1,_cars_hash_1,_cars_list_1, _d_date_1, _rec_dt_1
           from train_discovery
           where _car_id = (case when NEW.car_id_opposite < NEW.car_id then NEW.car_id_opposite else NEW.car_id end)
           and rec_dt in (select max(rec_dt) 
                          from train_discovery where car_id = case when NEW.car_id_opposite < NEW.car_id then NEW.car_id_opposite else NEW.car_id end
                         and rec_dt <> _rec_dt)
           ;
-- Первая запись в этом дне, или новый головной вагон, запись добавляем
If 
(case when date_part('hour', NEW.rec_dt) between 0 and 4 then date(NEW.rec_dt- INTERVAL '1 day') else date(NEW.rec_dt) end)
<> _d_date  or _car_id is null
 
 then Insert into train_discovery values (
  NEW.rec_id ,
 		NEW.rec_dt,	
 		case when NEW.car_id_opposite < NEW.car_id then NEW.car_id_opposite else NEW.car_id end ,
        case when NEW.car_id_opposite > NEW.car_id and NEW.car_id_opposite is  null then NEW.car_id_opposite else NEW.car_id end ,
        regexp_split_to_table(NEW.cars_list, E'\\,+') ,
     	NEW.cars_list,
        NEW.cars_count,
        NEW.cars_hash,
        NEW.discovery_status,
        case when date_part('hour', NEW.rec_dt) between 0 and 4 then date(NEW.rec_dt- INTERVAL '1 day') else date(NEW.rec_dt) end );

 end if;

     -- Если даты равны, и последняя запись была кривая, а предыдущая была корректная, тогда удаляем кривую и записываем корректную
 If  (case when date_part('hour', NEW.rec_dt) between 0 and 4 then date(NEW.rec_dt- INTERVAL '1 day') else date(NEW.rec_dt) end) =  _d_date 
  and _cars_hash is null and _cars_hash_1 is not null  and _cars_hash_1 = new.cars_hash 
 
then
     -- Удалили пустую запись
     delete from train_discovery 
     where car_id = _car_id and car_id_opposite = _car_id_opposite and cars_hash = _cars_hash
     and cars_list = _cars_list and d_date = _d_datet and  rec_dt = _rec_dt ;
  -- Вставили корректную запись   
    Insert into train_discovery values (
  NEW.rec_id ,
 		NEW.rec_dt,	
 		case when NEW.car_id_opposite < NEW.car_id then NEW.car_id_opposite else NEW.car_id end  ,
        case when NEW.car_id_opposite > NEW.car_id and NEW.car_id_opposite is  null then NEW.car_id_opposite else NEW.car_id end  ,
        regexp_split_to_table(NEW.cars_list, E'\\,+')  ,
     	NEW.cars_list,
        NEW.cars_count,
        NEW.cars_hash,
        NEW.discovery_status,
        case when date_part('hour', NEW.rec_dt) between 0 and 4 then date(NEW.rec_dt- INTERVAL '1 day') else date(NEW.rec_dt) end ); 
 end if; 

   --   Если последний cars_hash был не пустой, а новая запись осталась пустой, добавляем запись.
        
 If ( case when date_part('hour', NEW.rec_dt) between 0 and 4 then date(NEW.rec_dt- INTERVAL '1 day') else date(NEW.rec_dt) end) =  _d_date 
  and _cars_hash is not null and new.cars_hash is null
 
then
  -- добавляем запись  
    Insert into train_discovery values (
  NEW.rec_id ,
 		NEW.rec_dt,	
 		case when NEW.car_id_opposite < NEW.car_id then NEW.car_id_opposite else NEW.car_id end ,
        case when NEW.car_id_opposite > NEW.car_id and NEW.car_id_opposite is  null then NEW.car_id_opposite else NEW.car_id end ,
        regexp_split_to_table(NEW.cars_list, E'\\,+') ,
     	NEW.cars_list,
        NEW.cars_count,
        NEW.cars_hash,
        NEW.discovery_status,
        case when date_part('hour', NEW.rec_dt) between 0 and 4 then date(NEW.rec_dt- INTERVAL '1 day') else date(NEW.rec_dt) end  ); 
 end if;         
 
        
--   Если последний cars_hash был пустой, и новый cars_hash пустой и списки вагонов разные.
If   (case when date_part('hour', NEW.rec_dt) between 0 and 4 then date(NEW.rec_dt- INTERVAL '1 day') else date(NEW.rec_dt) end) =  _d_date 
and  _cars_hash is null and new.cars_hash is null and 
      (  case when _cars_count < new.cars_count
       then  array[_cars_list] <array[_cars_list]
        when _cars_count > new.cars_count
       then  array[_cars_list] >array[_cars_list]
            else 
         array[_cars_list] = array[_cars_list]
       end ) is false
then  
  
     Insert into train_discovery values (
  NEW.rec_id ,
 		NEW.rec_dt,	
 		case when NEW.car_id_opposite < NEW.car_id then NEW.car_id_opposite else NEW.car_id end ,
        case when NEW.car_id_opposite > NEW.car_id and NEW.car_id_opposite is  null then NEW.car_id_opposite else NEW.car_id end ,
        regexp_split_to_table(NEW.cars_list, E'\\,+') ,
     	NEW.cars_list,
        NEW.cars_count,
        NEW.cars_hash,
        NEW.discovery_status,
        case when date_part('hour', NEW.rec_dt) between 0 and 4 then date(NEW.rec_dt- INTERVAL '1 day') else date(NEW.rec_dt) end ); 
 end if;         
     
 return NEW;
END; 
 -- ' 
$BODY$;

-- создание триггера
CREATE TRIGGER trigger_train_discovery
BEFORE  insert ON train_discovery_logging
FOR EACH ROW
EXECUTE PROCEDURE train_discovery_f ();

where _car_id = (case when NEW.car_id_opposite < NEW.car_id then NEW.car_id_opposite else NEW.car_id end)