/**
 * MySQL兼容函数：字符串自定义函数
 * 作者：liaosnet@gbasedbt.com 2023-01-11
 */
-- find_in_set(s1,s)函数
drop function if exists find_in_set;
create dba function find_in_set(str varchar(255),strlist varchar(8192))
returns int with (not variant)
  define currp  int; 
  define lastp  int; 
  define sepnum int;
  define strlen int;
  define strtmp varchar(8192);
 
  let lastp  = 1;
  let sepnum = 0;
  let strtmp = strlist || ","; 
  let strlen = length(strtmp);
  FOR currp = 1 TO strlen
    IF substr(strtmp,currp,1) = ',' THEN
      let sepnum = sepnum + 1;
      IF substr(strtmp,lastp,currp - lastp) = str THEN
        RETURN sepnum;
      END IF;
      let lastp = currp + 1;
    END IF;
  END FOR;
  RETURN 0;
end function;

-- INSERT(s1,x,len,s2)函数
drop function if exists INSERT(varchar,int,int,varchar);
create dba function INSERT(s1 varchar(255),x int,len int,s2 varchar(255))
returns varchar(255) with (not variant)
  on exception
    return s1;
  end exception;
  if x < 1 or len < 1 then
    return s1;
  end if;
  return substr(s1,1,x-1) || s2 || substr(s1,x+len-1);
end function;

-- REPEAT(s,n) 字符串复制N次
drop function if exists REPEAT(varchar,int);
create dba function REPEAT(s varchar(255), n int)
returns varchar(255) with (not variant)
  define tmpstr varchar(255);
  define tmppos int;
  on exception
    return s;
  end exception;
  let tmpstr = s;
  let tmppos = 1;
  while tmppos < n
    let tmpstr = tmpstr || s;
	let tmppos = tmppos + 1;
  end while;
  return tmpstr;
end function;

-- STRCMP(str1,str2) 字符串比较函数
drop function if exists STRCMP(varchar,varchar);
create dba function STRCMP(str1 varchar(255), str2 varchar(255))
returns int with (not variant)
  on exception
    return null;
  end exception;
  if str1 = str2 then
    return 0;
  elif str1 > str2 then
    return 1;
  else 
    return -1;
  end if;
end function;

-- FORMAT(x,n,loc)函数：格式数值
drop function if exists format(decimal,int,varchar);
create function format(x decimal(32,12), n int, loc varchar(10) default 'en_us')
returns varchar(40) with (not variant)
  define str varchar(40);
  define lstr varchar(40);
  define rstr varchar(40);
  define llen int;
  on exception
    return null;
  end exception;
  let str = round(x,n);
  let llen = instr(str,'.');
  let lstr = substr(str,1,llen - 1);
  if lower(loc) = 'en_us' then
	let lstr = gbase_to_char(lstr,'###,###,###,###,###,###,###');
  end if;
  
  -- had fraction
  if n > 0 then
	let rstr = substr(str, llen, n + 1);
	let str = lstr || rstr;
  else
    let str = lstr;
  end if;
  return ltrim(str);
end function;

-- concat(s1,s2,...s16)函数：字符串连接函数
drop function if exists concat(varchar,varchar,varchar,varchar,
                               varchar,varchar,varchar,varchar,
							   varchar,varchar,varchar,varchar,
							   varchar,varchar,varchar,varchar);
create function concat(s1 varchar(255) default '',s2 varchar(255) default '',s3 varchar(255) default '',s4 varchar(255) default '',
                       s5 varchar(255) default '',s6 varchar(255) default '',s7 varchar(255) default '',s8 varchar(255) default '',
					   s9 varchar(255) default '',s10 varchar(255) default '',s11 varchar(255) default '',s12 varchar(255) default '',
					   s13 varchar(255) default '',s14 varchar(255) default '',s15 varchar(255) default '',s16 varchar(255) default '')
returns varchar(255) with (not variant)
  on exception
    return null;
  end exception;
  return s1 || s2 || s3 || s4 || s5 || s6 || s7 || s8 || s9 || s10 || s11 || s12 || s13 || s14 || s15 || s16;
end function;


-- concat_ws(del,s1,s2,...s16)函数：带分隔符的字符串连接函数
drop function if exists concat_ws(varchar,varchar,varchar,varchar,varchar,
                                          varchar,varchar,varchar,varchar,
										  varchar,varchar,varchar,varchar,
										  varchar,varchar,varchar,varchar);
create function concat_ws(del varchar(255), s1 varchar(255) default null,s2 varchar(255) default null,s3 varchar(255) default null,s4 varchar(255) default null,
                                            s5 varchar(255) default null,s6 varchar(255) default null,s7 varchar(255) default null,s8 varchar(255) default null,
					                        s9 varchar(255) default null,s10 varchar(255) default null,s11 varchar(255) default null,s12 varchar(255) default null,
					                        s13 varchar(255) default null,s14 varchar(255) default null,s15 varchar(255) default null,s16 varchar(255) default null)
returns varchar(255) with (not variant)
  define str varchar(255);
  on exception
    return null;
  end exception;
  if del is null then
    return null;
  end if;
  if s1 is null then
    let str = nvl(s2,'');
  else
    let str = s1 || del || s2;
  end if;
  let str = str || nvl2(s3,del||s3,'') || nvl2(s4,del||s4,'');
  let str = str || nvl2(s5,del||s5,'') || nvl2(s6,del||s6,'') || nvl2(s7,del||s7,'') || nvl2(s8,del||s8,'');
  let str = str || nvl2(s9,del||s9,'') || nvl2(s10,del||s10,'') || nvl2(s11,del||s11,'') || nvl2(s12,del||s12,'');
  let str = str || nvl2(s13,del||s13,'') || nvl2(s14,del||s14,'') || nvl2(s15,del||s15,'') || nvl(s16,'');
  return str;
end function;

/**
 * MySQL兼容函数：数值函数
 */
-- PI()
drop function if exists PI();
create dba function PI()
returns decimal(32,20) with (not variant)
  return 3.141592653589793116;
end function;

-- LOG2(N)
drop function if exists LOG2(decimal);
create dba function LOG2(N decimal(32,20))
returns decimal(32,20) with (not variant)
  return LOGN(N)/LOGN(2);
end function;

/**
 * MySQL兼容函数：日期时间函数
 */
-- ADDDATE(d,n)
-- 参数为数字，默认为天
drop function if exists ADDDATE(datetime year to second, int);
create dba function ADDDATE(d datetime year to second,n int)
returns datetime year to second with (not variant)
  on exception
    return null;
  end exception;
  return d + n units day;
end function;

-- 参数为interval，注意使用本数据库的写法，例如：interval(10 11:12:13) day to second
drop function if exists ADDDATE(datetime year to second, interval);
create dba function ADDDATE(d datetime year to second, n interval day to second)
returns datetime year to second with (not variant)
  on exception
    return null;
  end exception;
  return d + n;
end function;

-- ADDTIME(t,n)
-- 参数为数字，默认为秒
drop function if exists ADDTIME(datetime year to second, int);
create dba function ADDTIME(t datetime year to second,n int)
returns datetime year to second with (not variant)
  on exception
    return null;
  end exception;
  return t + n units second;
end function;

-- 参数为interval，注意使用本数据库的写法，例如：interval(10 11:12:13) day to second
drop function if exists ADDTIME(datetime year to second, interval);
create dba function ADDTIME(t datetime year to second, n interval day to second)
returns datetime year to second with (not variant)
  on exception
    return null;
  end exception;
  return t + n;
end function;

-- CURDATE()/CURRENT_DATE()返回当前日期
drop function if exists curdate();
create dba function CURDATE()
returns varchar(10) with (not variant)
  return gbase_to_char(today,'%Y-%m-%d');
end function;

drop function if exists CURRENT_DATE();
create dba function CURRENT_DATE()
returns char(10) with (not variant)
  return gbase_to_char(today,'%Y-%m-%d');
end function;

-- CURRENT_TIME()/CURTIME()返回当前时间
drop function if exists CURTIME();
create dba function CURTIME()
returns char(8) with (not variant)
  return gbase_to_char(current year to second, '%H:%M:%S');
end function;

drop function if exists CURRENT_TIME();
create dba function CURRENT_TIME()
returns char(8) with (not variant)
  return gbase_to_char(current year to second, '%H:%M:%S');
end function;

-- CURRENT_TIMESTAMP()返回当前日期和时间
drop function if exists CURRENT_TIMESTAMP();
create dba function CURRENT_TIMESTAMP()
returns datetime year to second with (not variant)
  return current year to second;
end function;

-- NOW()返回当前日期和时间
drop function if exists NOW();
create dba function NOW()
returns datetime year to second with (not variant)
  return current year to second;
end function;

-- DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数
drop function if exists DATEDIFF(datetime year to second, datetime year to second);
create dba function DATEDIFF(d1 datetime year to second, d2 datetime year to second)
returns int with (not variant)
  return substr(d1 - d2,1,10);
end function;

-- DATE_ADD(d,n)
-- 参数为interval，注意使用本数据库的写法，例如：interval(10 11:12:13) day to second
drop function if exists DATE_ADD(datetime year to second, interval);
create dba function DATE_ADD(d datetime year to second, n interval day to second)
returns datetime year to second with (not variant)
  on exception
    return null;
  end exception;
  return d + n;
end function;

-- DATE_FORMAT(d,fmt)
-- fmt有差异需转换
drop function if exists DATE_FORMAT(datetime year to second, varchar);
create dba function DATE_FORMAT(d datetime year to second, fmt varchar(40))
returns varchar(40) with (not variant);
  define tmpfmt varchar(40);
  on exception
    return null;
  end exception;
  let tmpfmt = replace(fmt,'f','F5');
  let tmpfmt = replace(tmpfmt,'h','I');
  let tmpfmt = replace(tmpfmt,'k','H');
  let tmpfmt = replace(tmpfmt,'l','I');
  let tmpfmt = replace(tmpfmt,'M','B');
  let tmpfmt = replace(tmpfmt,'s','S');
  let tmpfmt = replace(tmpfmt,'W','A');
  let tmpfmt = replace(tmpfmt,'i','M');
  return gbase_to_char(d,tmpfmt);
end function;

-- DATE_SUB(d,n)
-- 参数为interval，注意使用本数据库的写法，例如：interval(10 11:12:13) day to second
drop function if exists DATE_SUB(datetime year to second, interval);
create dba function DATE_SUB(d datetime year to second, n interval day to second)
returns datetime year to second with (not variant)
  on exception
    return null;
  end exception;
  return d - n;
end function;

-- DAYNAME(d)返回日期 d 是星期几，如 Monday,Tuesday
drop function if exists DAYNAME(datetime year to second);
create dba function DAYNAME(d datetime year to second)
returns varchar(20) with (not variant)
  on exception
    return null;
  end exception;
  return gbase_to_char(d, '%A');
end function;

-- DAYOFMONTH(d)计算日期 d 是本月的第几天
drop function if exists DAYOFMONTH(datetime year to second);
create dba function DAYOFMONTH(d datetime year to second)
returns int with (not variant)
  return day(d);
end function;

-- FROM_DAYS(n) 返回日期值
drop function if exists FROM_DAYS(int);
create dba function FROM_DAYS(n int)
returns char(10) with (not variant)
  on exception
    return '0000-00-00';
  end exception;
  if n < 366 then
    return '0000-00-00';
  else
    return gbase_to_char(gbase_to_date('0001-01-01','%Y-%m-%d') + (n - 366) units day, '%Y-%m-%d');
  end if;
end function;

-- HOUR(t) 返回当前小时数
drop function if exists HOUR(datetime year to second);
create dba function HOUR(t datetime year to second)
returns int with (not variant)
  on exception
    return null;
  end exception;
  return gbase_to_char(t,'%H');
end function;

-- MAKEDATE(year, day-of-year)
drop function if exists MAKEDATE(int, int);
create dba function MAKEDATE(d int, doy int)
returns char(10) with (not variant)
  on exception
    return null;
  end exception
  if doy < 0 then
    return null;
  else
    return gbase_to_char(gbase_to_date(d || '-01-01','%Y-%m-%d') + doy units day,'%Y-%m-%d');
  end if;
end function;

-- MAKETIME(hh24, mi, ss)
drop function if exists MAKETIME(int, int, int);
create dba function MAKETIME(hh24 int, mi int, ss int)
returns char(8) with (not variant)
  on exception
    return null;
  end exception;
  if hh24 < 0 or hh24 > 23 or mi < 0 or mi > 59 or ss < 0 or ss > 59 then
    return null;
  else
    return lpad(hh24,2,'0') || ':' || lpad(mi,2,'0') || ':' || lpad(ss,2,'0');
  end if;
end function;

-- MICROSECOND(date)
drop function if exists MICROSECOND(datetime year to fraction(5));
create dba function MICROSECOND(d datetime year to fraction(5))
returns int with (not variant)
  return gbase_to_char(d, '%F5') || '0';
end function;

-- MINUTE(t) 返回当前分钟数
drop function if exists MINUTE(datetime year to second);
create dba function MINUTE(t datetime year to second)
returns int with (not variant)
  on exception
    return null;
  end exception;
  return gbase_to_char(t,'%M');
end function;

-- MONTHNAME(t) 返回当前月份名称
drop function if exists MONTHNAME(datetime year to second);
create dba function MONTHNAME(d datetime year to second)
returns varchar(20) with (not variant)
  on exception
    return null;
  end exception;
  return gbase_to_char(d,'%B');
end function;

-- PERIOD_ADD(p,n)
drop function if exists PERIOD_ADD(int, int);
create dba function PERIOD_ADD(p int, n int)
returns int with (not variant)
  define tmpstr char(6);
  on exception
    return null;
  end exception;
  let tmpstr = lpad(p,6,'0');
  if p < 9999 then
    if substr(tmpstr,3,2) > 49 then
	  let tmpstr = '19' || substr(tmpstr,3);
	else
	  let tmpstr = '20' || substr(tmpstr,3);
	end if;
  end if;
  return gbase_to_char(gbase_to_date(tmpstr,'%Y%m') + n units month,'%Y%m');  
end function;

-- PERIOD_DIFF(p1,p2)
drop function if exists PERIOD_DIFF(int, int);
create dba function PERIOD_DIFF(p1 int, p2 int)
returns int with (not variant)
  define tmpstr1 char(6);
  define tmpstr2 char(6);
  on exception
    return null;
  end exception;
  let tmpstr1 = lpad(p1,6,'0');
  let tmpstr2 = lpad(p2,6,'0');
  if p1 < 9999 then
    if substr(tmpstr1,3,2) > 49 then
	  let tmpstr1 = '19' || substr(tmpstr1,3);
	else
	  let tmpstr1 = '20' || substr(tmpstr1,3);
	end if;
  end if;
  if p2 < 9999 then
    if substr(tmpstr2,3,2) > 49 then
	  let tmpstr2 = '19' || substr(tmpstr2,3);
	else
	  let tmpstr2 = '20' || substr(tmpstr2,3);
	end if;
  end if;
  return (substr(tmpstr1,1,4) - substr(tmpstr2,1,4)) * 12 + (substr(tmpstr1,5) - substr(tmpstr2,5));
end function;

-- SECOND(t) 返回当前秒数
drop function if exists SECOND(datetime year to second);
create dba function SECOND(t datetime year to second)
returns int with (not variant)
  on exception
    return null;
  end exception;
  return gbase_to_char(t,'%S');
end function;

-- SEC_TO_TIME(s)
drop function if exists SEC_TO_TIME(int);
create dba function SEC_TO_TIME(s int)
returns char(8) with (not variant)
  return gbase_to_char(gbase_to_date('00:00:00','%H:%M:%S') + s units second, '%H:%M:%S');
end function;

-- SUBDATE(d,n)
-- 参数为数字，默认为天
drop function if exists SUBDATE(datetime year to second, int);
create dba function SUBDATE(d datetime year to second,n int)
returns datetime year to second with (not variant)
  on exception
    return null;
  end exception;
  return d - n units day;
end function;

-- SUBTIME(t,n)
-- 参数为数字，默认为秒
drop function if exists SUBTIME(datetime year to second, int);
create dba function SUBTIME(t datetime year to second,n int)
returns datetime year to second with (not variant)
  on exception
    return null;
  end exception;
  return t - n units second;
end function;

-- TIME(e)
-- datetime
drop function if exists TIME(datetime year to second);
create dba function TIME(t datetime year to second)
returns char(8) with (not variant)
  on exception
    return '00:00:00';
  end exception;
  if t is null then
    return null;
  end if;
  return gbase_to_char(t,'%T');
end function;

-- 仅time
drop function if exists TIME(datetime hour to second);
create dba function TIME(t datetime hour to second)
returns char(8) with (not variant)
  on exception
    return '00:00:00';
  end exception;
  if t is null then
    return null;
  end if;
  return gbase_to_char(t,'%T');
end function;

-- TIME_FORMAT(d,fmt)
-- fmt有差异需转换
drop function if exists TIME_FORMAT(datetime hour to second, varchar);
create dba function TIME_FORMAT(d datetime hour to second, fmt varchar(40))
returns varchar(40) with (not variant);
  define tmpfmt varchar(40);
  on exception
    return null;
  end exception;
  let tmpfmt = replace(fmt,'f','F5');
  let tmpfmt = replace(tmpfmt,'h','I');
  let tmpfmt = replace(tmpfmt,'k','H');
  let tmpfmt = replace(tmpfmt,'l','I');
  let tmpfmt = replace(tmpfmt,'M','B');
  let tmpfmt = replace(tmpfmt,'s','S');
  let tmpfmt = replace(tmpfmt,'W','A');
  let tmpfmt = replace(tmpfmt,'i','M');
  return gbase_to_char(d,tmpfmt);
end function;

-- TIME_TO_SEC(t)
drop function if exists TIME_TO_SEC(datetime hour to second);
create dba function TIME_TO_SEC(d datetime hour to second)
returns int with (not variant);
  on exception
    return null;
  end exception;
  return gbase_to_char(d,'%H') * 3600 + gbase_to_char(d,'%M') * 60 + gbase_to_char(d,'%S');
end function;

-- TIMEDIFF(t1,t2)
-- 仅time
drop function if exists TIMEDIFF(datetime hour to second,datetime hour to second);
create dba function TIMEDIFF(d1 datetime hour to second, d2 datetime hour to second)
returns varchar(20) with (not variant);
  on exception
    return null;
  end exception;
  return d1 - d2;
end function;

-- datetime
drop function if exists TIMEDIFF(datetime year to second,datetime year to second);
create dba function TIMEDIFF(d1 datetime year to second, d2 datetime year to second)
returns varchar(20) with (not variant);
  define tmpitv varchar(20);
  define tmpstr varchar(20);
  define tmp_d varchar(20);
  on exception
    return null;
  end exception;
  let tmpitv = d1 - d2;
  let tmp_d = substr(tmpitv,1,10);
  let tmpstr = (abs(substr(tmpitv,1,10))*24 + substr(tmpitv,11,2))::int || substr(tmpitv,13,6);
  if instr(tmp_d,'-') > 0 then
    return '-' || tmpstr;
  else
    return tmpstr;
  end if;
end function;

-- TO_DAYS(d) 返回日期值
drop function if exists TO_DAYS(datetime year to day);
create dba function TO_DAYS(d datetime year to day)
returns int with (not variant)
  on exception
    return null;
  end exception;
  return d::date::int + 693960;
end function;

-- WEEK(d,mode)
drop function if exists WEEK(datetime year to second, int);
create dba function WEEK(d datetime year to second, mode int default 0)
returns int with (not variant)
  on exception
    return null;
  end exception;
  return trunc(1 + (d - TRUNC(d, 'YEAR')) / 7);
end function;

-- WEEKOFYEAR(d)
drop function if exists WEEKOFYEAR(datetime year to second);
-- day of year, first week is 01-01 ~ 01-07
create dba function WEEKOFYEAR(d datetime year to second) 
returns int 
with (not variant)
  return trunc(1 + (d - TRUNC(d, 'YEAR')) / 7);
end function;

-- YEARWEEK(d,mode)
drop function if exists YEARWEEK(datetime year to second, int);
create dba function YEARWEEK(d datetime year to second, mode int default 0)
returns int with (not variant)
  on exception
    return null;
  end exception;
  return year(d)*100 + trunc(1 + (d - TRUNC(d, 'YEAR')) / 7);
end function;

-- WEEKOFMONTH(d)函数：指定日期时间d为当月的第几周
-- WEEKOFMONTH(d) 计算当月第几周
drop function if exists weekofmonth(datetime year to second);
-- day of month, first week is 01~07
create dba function weekofmonth(d1 datetime year to second) 
returns int with (not variant)
  return trunc(1 + (d1 - TRUNC(d1, 'MONTH')) / 7);
end function;

-- DAYOFWEEK(d)函数：指定日期时间d为当周的第几天，字符型
-- DAYOFWEEK(d)计算日期 d 是本周的第几天
drop function if exists DAYOFWEEK(datetime year to second);
create dba function DAYOFWEEK(d1 datetime year to second)
returns int with (not variant)
  return weekday(d1) + 1;
end function;

-- DAYOFYEAR(d)函数：指定日期时间d为当年的第几天，格式为（000）字符型
-- DAYOFYEAR(d)计算日期 d 是本年的第几天
drop function if exists DAYOFYEAR(datetime year to second);
create dba function DAYOFYEAR(d1 datetime year to second)
returns char(3) with (not variant)
  return lpad(trunc(1 + (d1 - TRUNC(d1, 'YEAR'))),3, '0');
end function;

-- UNIX_TIMESTAMP(dt)函数：将日期时间dt转换为自1970-01-01 08:00:00开始的秒数
drop function if exists UNIX_TIMESTAMP(datetime year to second);
-- unix_timestamp
create dba function UNIX_TIMESTAMP(datestr datetime year to second) 
returns bigint with (not variant);
  define rc_char varchar(30);
  if datestr < datetime(1970-01-01 08:00:00) year to second then
    let rc_char = datetime(1970-01-01 08:00:00) year to second - datestr;
    return 0 - ((replace(substr(rc_char,1,9),'-')*86400)::bigint + substr(rc_char,17,2)*1 + substr(rc_char,14,2)*60 + substr(rc_char,11,2)*3600);
  else
    let rc_char = datestr - datetime(1970-01-01 08:00:00) year to second;
    return ((substr(rc_char,1,9)*86400)::bigint + substr(rc_char,17,2)*1 + substr(rc_char,14,2)*60 + substr(rc_char,11,2)*3600);
  end if;
end function;

-- FROM_UNIXTIME(P)函数：返回自1970-01-01 08:00:00开始p秒的日期时间值
drop function if exists from_unixtime;
-- from_unixtime
create dba function from_unixtime(p bigint) 
returns varchar(20) with (not variant);
  define v_day int;
  define v_hour int;
  define v_min int;
  define v_sec int;
  define v_dt datetime year to second;
  on exception
    return null;
  end exception;
   
  let v_day = p / 86400;
  let v_dt = datetime(1970-01-01 08:00:00) year to second + v_day units day;
   
  let v_hour = mod(p,86400) / 3600;
  let v_dt = v_dt + v_hour units hour;
   
  let v_min = mod(p,3600) / 60;
  let v_dt = v_dt + v_min units minute;
   
  let v_sec = mod(p,60);
  let v_dt = v_dt + v_sec units second;
   
  return to_char(v_dt,'yyyy-mm-dd hh24:mi:ss');
end function;

-- TIMESTAMPDIFF(unit, d1, d2)
drop function if exists TIMESTAMPDIFF(varchar, datetime year to second, datetime year to second, boolean);
-- SECOND,MINUTE,HOUR,DAY；d2>d1为正数
create dba function TIMESTAMPDIFF(unit varchar(10), d1 datetime year to second, d2 datetime year to second, isabs boolean default 'f')
returns int8 with (not variant)
  define tmp_num int8;
  define tmp_str varchar(20);
  define tmp_flag char(1);
  define tmp_y int;
  define tmp_m int;
  define tmp_d int;
  
  let tmp_str = d2 - d1;
  let tmp_num = substr(tmp_str,1,9)::int8; 
  let tmp_flag = 'f';
  if tmp_num < 0 then 
	let tmp_flag = 't';
  end if;
  case lower(unit)	  
    when 'day' then
	  let tmp_num = tmp_num;
	when 'hour' then
	  let tmp_num = abs(tmp_num) * 24 + substr(tmp_str,11,2)::int8;
	when 'minute' then 
	  let tmp_num = (abs(tmp_num) * 24 + substr(tmp_str,11,2)::int8) * 60 + substr(tmp_str,14,2);
	when 'second' then 
	  let tmp_num = ((abs(tmp_num) * 24 + substr(tmp_str,11,2)::int8) * 60 + substr(tmp_str,14,2)) * 60 + substr(tmp_str,17,2);
	else
	  let tmp_num = 0;
  end case;
 
  if tmp_flag = 't' and isabs = 'f' then
    return '-' || tmp_num;
  else
    return tmp_num;
  end if;
end function;