CREATE OR REPLACE FUNCTION UYUMSOFT.GET_HRM_REG_EXPERIENCE 
(
   pregisterId integer   
)

RETURNS varchar(100) AS
$body$
DECLARE

   totalDate date;
   totalDay integer;
   ay integer;
   gun integer;
   yil integer;
   res varchar(100);

BEGIN
   res:='';

   SELECT               
   SUM((CASE WHEN TO_DATE(EMP.QUIT_DATE,'DD.MM.YYYY')=TO_DATE('01.01.0001','DD.MM.YYYY') 
      THEN TO_DATE(CURRENT_DATE,'DD.MM.YYYY') 
      ELSE TO_DATE(EMP.QUIT_DATE,'DD.MM.YYYY') END)-TO_DATE(EMP.EMP_DATE,'DD.MM.YYYY')+1) into totalDay
   FROM HRMD_EMPLOYEE EMP
   WHERE EMP.REGISTER_ID = pregisterId;

   totalDate:=TO_DATE('01.01.1900','DD.MM.YYYY') + interval '1 day' * totalDay;          
   yil:= extract( year  from totalDate)-1900;
   ay:= extract( month  from totalDate)-1;
   gun:= extract( day  from totalDate)-1;
   if(yil>0) then
   res:=to_char(yil)||' Yıl ' ;   
   end if;
   if(ay>0) then
   res:=res||to_char(ay)||' Ay ' ;   
   end if;
    if(gun>0) then
   res:=res||to_char(gun)||' Gün' ;   
   end if;

RETURN res;

END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
PARALLEL UNSAFE
COST 100;