Untitled

CREATE OR REPLACE FUNCTION public.uprav_tgr2()

 RETURNS integer

 LANGUAGE plpgsql

AS $function$

  begin  

    update tgr2 set namefrom_kratky=(select case when cm_skr<>'' then cm_skr else cm_nao end  from ( select a,  trim(split_part(namefrom,', ',a)) as _1 from generate_series(1,10) s(a)) t1,(select cm_nao,cm_skr from acm where acm._podnik=1 and cm_nao<>'' ) t2 where trim(cm_nao)=_1 order by a,case when cm_skr='' then 1 else 0 end  limit 1),nameto_kratky=(select case when cm_skr<>'' then cm_skr else cm_nao end  from ( select a,  trim(split_part(nameto,',',a)) as _1 from generate_series(1,10) s(a)) t1,(select cm_nao,cm_skr from acm where acm._podnik=1  and cm_nao<>'') t2 where cm_nao=trim(_1) order by a,case when cm_skr='' then 1 else 0 end  limit 1)    where (_deleted='f' or _deleted is null ) and latfrom<>0 and timefrom>'2024-01-01' and (trim(namefrom_kratky)='' or trim(nameto_kratky)='' or namefrom_kratky is null or nameto_kratky is null   ) and carname in (select gh_ozn from tgh where _podnik=1 and _deleted='f' and _rok=2024) ;

    update tgr2 set namefrom_kratky=(select case when cm_skr<>'' then cm_skr else cm_nao end  from ( select a,  trim(split_part(namefrom,', ',a)) as _1 from generate_series(1,10) s(a)) t1,(select cm_nao,cm_skr from acm where acm._podnik=3 and cm_nao<>'' ) t2 where trim(cm_nao)=_1 order by a,case when cm_skr='' then 1 else 0 end  limit 1),nameto_kratky=(select case when cm_skr<>'' then cm_skr else cm_nao end  from ( select a,  trim(split_part(nameto,',',a)) as _1 from generate_series(1,10) s(a)) t1,(select cm_nao,cm_skr from acm where acm._podnik=3  and cm_nao<>'') t2 where cm_nao=trim(_1) order by a,case when cm_skr='' then 1 else 0 end  limit 1)    where (_deleted='f' or _deleted is null ) and latfrom<>0 and timefrom>'2024-01-01' and (trim(namefrom_kratky)='' or trim(nameto_kratky)='' or namefrom_kratky is null or nameto_kratky is null   ) and carname in (select gh_ozn from tgh where _podnik=3 and _deleted='f' and _rok=2024) ;

    update tgr2 set namefrom_kratky=case when namefrom_kratky='' or namefrom_kratky is null then 'XXX' else namefrom_kratky end,

      nameto_kratky=case when nameto_kratky='' or nameto_kratky is null then 'XXX' else nameto_kratky end

      where (_deleted='f' or _deleted is null ) and latfrom<>0 and timefrom>'2024-01-01' and (trim(namefrom_kratky)='' or trim(nameto_kratky)='' or namefrom_kratky is null or nameto_kratky is null   );

    return 1;

  end

$function$