-- This scripts contains following function's definition:
-- =============================================================================
-- NULL 相关函数
-- 注意： ifnull 效果等同于通用函数 coalesce，在条件允许的情况下，建议修改 SQL 使用 coalesce 函数，该函数几乎在各个数据库中均支持
-- ifnull(text,text)
-- ifnull(numeric,numeric)
-- ifnull(timestamp,timestamp)
-- ifnull(timestamptz,timestamptz)
-- isnull(text)
-- isnull(numeric)
-- isnull(timestamptz)

-- 条件控制函数
-- if(bool,bool,bool)
-- if(bool,text,text)
-- if(bool,numeric,numeric)
-- if(bool,timestamptz,timestamptz)

-- 字符函数
-- find_in_set(text,text)
-- find_in_set(text,text[])
-- field(text,text[])
-- elt(int4,text[])
-- strcmp(text,text)
-- insert(text,int8,int8,text)
-- lcase(text)
-- ucase(text)
-- space(int4)
-- mid(text,int8,int8)
-- locate(text,text,int4)
-- to_base64(text)
-- from_base64(text)

-- 数字函数
-- field(numeric,numeric[])
-- log10(numeric)
-- log10(float8)
-- rand(int4)

-- 时间函数
-- unix_timestamp(timestamp)
-- unix_timestamp(timestamptz)
-- from_unixtime(int8)
-- from_unixtime(numeric)
-- from_unixtime(numeric,text)
-- to_days(timestamp)
-- to_days(timestamptz)
-- to_days(text)
-- to_days(text,text)
-- to_days(int8)
-- to_seconds(timestamp)
-- to_seconds(timestamptz)
-- timediff(timestamptz,timestamptz)
-- time_to_sec(time)
-- sec_to_time(int4)
-- date_format(timestamp,text)
-- date_format(timestamptz,text)
-- timestampdiff(text,timestamptz,timestamptz)
-- str_to_date(text,text)
-- curdate()
-- yearweek(timestamp,int4)
-- yearweek(timestamptz,int4)
-- yearweek(text,int4)
-- date_add(timestamp,interval)
-- date_add(timestamptz,interval)

-- 其他函数
-- uuid()
-- uuid_to_bin(uuid,int4)
-- bin_to_uuid(bytea,int4)
-- conv(text,int4,int4)
-- json_extract(json,text)
-- json_extract(json,text[])


set client_min_messages='warning';
set time zone 'UTC';
set statement_timeout = 60000;
set behavior_compat_options = '';


-- =============================================================================
-- Create Schema
-- =============================================================================
do $$
declare
    l_cnt       bigint;
    l_version   varchar(10);
begin
    set client_min_messages='warning';
    select count(*) into l_cnt from pg_catalog.pg_namespace where nspname = 'compat_tools';
    if l_cnt = 0
    then
        create schema compat_tools;
    end if;
end;
$$ language plpgsql;


-- =============================================================================
-- Version Table
-- =============================================================================
create table if not exists compat_tools.compat_version
(
    compat_type     varchar(10),    -- VIEW, FUNCTION, PROCEDURE
    object_name     varchar(128),   -- Compat object name
    object_version  varchar(10),    -- Compat object version
    constraint pk_compat_version primary key(compat_type, object_name)
);


-- =============================================================================
-- Table: compat_tools.pg_function_list
-- =============================================================================
drop table if exists compat_tools.pg_function_list;
create table compat_tools.pg_function_list as
select p.oid
     , l.lanname as language
     , n.nspname as schema_name
     , p.proname || '(' || string_agg(case when a.typname is not null then a.typname||'[]' else t.typname end, ',' order by p.id) || ')' as function_name
  from (select oid
             , pronamespace
             , proname
             , prolang
             , case when proallargtypes is null then proargtypes else proallargtypes end as proallargtypes
             , generate_series(1, array_length(case when proallargtypes is null then proargtypes else proallargtypes end, 1)) as id
             , unnest(case when proallargtypes is null then proargtypes else proallargtypes end) as protype
          from pg_catalog.pg_proc
       ) as p
  join pg_catalog.pg_namespace as n on p.pronamespace = n.oid
  join pg_catalog.pg_language as l on p.prolang = l.oid
  join pg_catalog.pg_type as t on p.protype = t.oid
  left join pg_catalog.pg_type as a on t.typcategory = 'A' and t.typelem = a.oid   -- for array type
 group by p.proname, p.proallargtypes, l.lanname, p.oid, n.nspname
 union all
select p.oid, l.lanname, n.nspname, p.proname||'()'
  from pg_catalog.pg_proc as p
  join pg_catalog.pg_namespace as n on p.pronamespace = n.oid
  join pg_catalog.pg_language as l on p.prolang = l.oid
 where (oidvectortypes(p.proargtypes) is null or oidvectortypes(p.proargtypes) = '');


-- =============================================================================
-- Table: compat_tools.pg_depend_list
-- =============================================================================
drop table if exists compat_tools.pg_depend_list;
create table compat_tools.pg_depend_list as
select distinct dep.classid::regclass::text as object_type
     , coalesce(typ_n.nspname, coalesce(cls_n.nspname, dep.objid::text)) as schema_name
     , coalesce(typ.typname, coalesce(cls.relname, dep.objid::text)) as object_name
     , dep.refclassid::regclass::text as ref_object_type
     , cpt.object_name as ref_object_name
     , cpt.compat_type
     , case dep.deptype when 'n' then 'NORMAL' when 'a' then 'AUTO' when 'i' then 'INTERNAL' when 'e' then 'EXTENSION' when 'p' then 'PIN' when 'x' then 'AUTO_EXTENSION' when 'I' then 'INTERNAL_AUTO' else dep.deptype::text end as DEPENDENCY_TYPE
  from pg_depend as dep
  join (select v.compat_type, v.object_name, l.language, coalesce(l.oid, c.oid) as oid
          from compat_tools.compat_version as v
          left join compat_tools.pg_function_list as l on v.object_name = l.schema_name||'.'||l.function_name
          left join (select cls.oid, nsp.nspname||'.'||cls.relname object_name
                       from pg_catalog.pg_class as cls
                       join pg_catalog.pg_namespace as nsp on cls.relnamespace = nsp.oid
                    ) as c on v.object_name = c.object_name
         where v.compat_type in ('aggregate', 'procedure', 'function', 'view')) as cpt on dep.refobjid = cpt.oid
  left join pg_type as typ on dep.classid = 'pg_type'::regclass and dep.objid = typ.oid
  left join pg_namespace as typ_n on typ_n.oid = typ.typnamespace
  left join pg_rewrite as rwt on dep.classid = 'pg_rewrite'::regclass and dep.objid = rwt.oid
  left join pg_class as cls on rwt.ev_class = cls.oid
  left join pg_namespace as cls_n on cls_n.oid = cls.relnamespace
 where coalesce(typ_n.nspname, coalesce(cls_n.nspname, dep.objid::text)) != 'compat_tools';


-- =============================================================================
-- Version Function
-- =============================================================================
drop function if exists compat_tools.drop_compat_function(varchar, varchar, varchar, varchar, varchar);
create or replace function compat_tools.drop_compat_function( p_object_type    varchar(10)
                                                            , p_object_name    varchar(128)
                                                            , p_object_version varchar(10)
                                                            , p_function_lang  varchar(16)  default 'sql'
                                                            , p_object_schema  varchar(128) default 'pg_catalog')
returns boolean
as $$
declare
    l_version    varchar(10);
    l_language   varchar(16);
    l_result     boolean       := true;
    l_operation  varchar(128);
    l_depend_cnt bigint;
    l_app_name   varchar(128)  := current_setting('application_name');
begin
    CREATE temporary table if not exists temp_result
    (
        object_type     varchar(10),
        object_name     varchar(128),
        local_version   varchar(10),
        script_version  varchar(10),
        local_language  varchar(10),
        script_language varchar(10),
        operation       varchar(128)
    );

    -- 字符串参数统一转小写
    p_object_name := lower(p_object_name);
    p_object_type := lower(p_object_type);
    p_object_schema := lower(p_object_schema);

    select max(object_version) into l_version
      from compat_tools.compat_version
     where object_name = p_object_schema||'.'||p_object_name
       and compat_type = p_object_type;

    -- 获取已有同名同参数函数/存储过程的语言，存入 l_language 变量
    select max(language) into l_language
      from compat_tools.pg_function_list
     where schema_name = p_object_schema
       and function_name = p_object_name;

    -- 获取非 Compat Tools 依赖对象数量
    select count(*) into l_depend_cnt
      from compat_tools.pg_depend_list
     where ref_object_name = p_object_schema||'.'||p_object_name;

    if l_language is null
    then
        l_operation := 'Initial creation';
    elsif l_language != p_function_lang
    then
        l_result = 'false';
        l_operation := 'Skip due to language';
    elsif l_version is null
    then
        l_operation := 'Initial creation (Ver)';
    elsif l_version < p_object_version
    then
        l_operation := 'Upgrade';
    else
        l_result = 'false';
        l_operation := 'Skip due to version';
    end if;

    if l_app_name != 'checkMe'
    then
        if l_result
        then
            begin
                -- 若系统中存在非 compat_tools 对象依赖本对象，无法删除，可尝试直接创建
                if l_depend_cnt = 0
                then
                    if instr(p_object_name, '(') > 0
                    then
                        execute 'drop '||p_object_type||' if exists "'||p_object_schema||'"."'||replace(p_object_name, '(', '"(')||' cascade';
                    else
                        execute 'drop '||p_object_type||' if exists "'||p_object_schema||'"."'||p_object_name||'" cascade';
                    end if;

                    insert into compat_tools.compat_version
                    values (p_object_type, p_object_schema||'.'||p_object_name, p_object_version)
                    ON DUPLICATE KEY UPDATE object_version = p_object_version;
                else
                    l_operation := l_operation||' - dependence';
                end if;
            exception
                when others then
                    l_result := 'false';
                    get stacked diagnostics l_operation = message_text;
                    l_operation = substr(l_operation, 1, 32);
            end;
        else
            insert into compat_tools.compat_version
            values (p_object_type, p_object_schema||'.'||p_object_name, p_object_version)
            ON DUPLICATE KEY UPDATE NOTHING;
        end if;
    end if;

    -- 插入本次临时结果表
    insert into temp_result values ( p_object_type
                                   , p_object_schema||'.'||p_object_name
                                   , l_version
                                   , p_object_version
                                   , l_language
                                   , p_function_lang
                                   , l_operation);

    -- 返回函数结果
    if l_app_name = 'checkMe'
    then
        return 'false';
    else
        return l_result;
    end if;
end;
$$ language plpgsql;


-- =============================================================================
-- Testing Table/Procedure
-- =============================================================================
create table if not exists compat_tools.compat_testing
(
    test_expr       text,      -- 测试表达式
    test_result     text,      -- 表达式结果
    expect_result   text,      -- 预期结果
    test_ok         bool,      -- 测试是否通过
    test_timestamp  timestamp  default now(), -- 测试时间
    constraint pk_compat_testing_expr primary key(test_expr)
);

drop procedure if exists compat_tools.f_unit_test(text, text, text, text);
create or replace procedure compat_tools.f_unit_test( p_test_expr     text
                                                    , p_expect        text
                                                    , p_compare_type  text  default '='
                                                    , p_db_compat     text  default NULL)
as
declare
    l_compat_valid bool    := 'true'::bool;
    l_error_code   text;
    l_error_mesg   text;
    l_test_result  text;
    l_test_ok      bool;
    l_app_name     varchar(128)  := current_setting('application_name');
begin
    if l_app_name != 'checkMe'
    then
        if p_db_compat is not null
        then
            select count(*)::bool
              into l_compat_valid
              from pg_database
             where datname = current_database()
               and datcompatibility = p_db_compat;
        end if;

        if l_compat_valid
        then
            execute immediate 'select '||p_test_expr||', cast('||p_test_expr||' as text) '||p_compare_type||' '||p_expect
               into l_test_result, l_test_ok;
            insert into compat_tools.compat_testing
            values (p_test_expr, l_test_result, p_compare_type||' '||p_expect, l_test_ok)
            on DUPLICATE KEY UPDATE test_result = EXCLUDED.test_result
                                  , expect_result = EXCLUDED.expect_result
                                  , test_ok = EXCLUDED.test_ok
                                  , test_timestamp = now();
        end if;
    end if;
exception
    when others then
        get stacked diagnostics l_error_code = returned_sqlstate, l_error_mesg = message_text;
        insert into compat_tools.compat_testing
        values (p_test_expr, l_error_code||': '||l_error_mesg, p_compare_type||' '||p_expect, l_error_code||': '||l_error_mesg = trim(p_expect, ''''))
        on DUPLICATE KEY UPDATE test_result = EXCLUDED.test_result
                              , expect_result = EXCLUDED.expect_result
                              , test_ok = EXCLUDED.test_ok
                              , test_timestamp = now();
end;
/



-- =============================================================================
-- Version Comparison Function
-- Result:
--     version_a > version_b  => 1
--     version_a = version_b  => 0
--     version_a < version_b  => -1
-- =============================================================================
create or replace function pg_catalog.f_version_compare ( version_a text
                                                        , version_b text)
returns int IMMUTABLE strict as $$
declare
    l_rec   record;
begin
    if version_a !~ '^\d+(\.\d+)*$' or version_b !~ '^\d+(\.\d+)*$'
    then
        return null;
    end if;
    for l_rec in select coalesce(t1.ver, -1) as ver_1, coalesce(t2.ver, -1) as ver_2
                   from (select rownum as id, t1::int as ver from regexp_split_to_table(version_a, '\.') as t1) as t1
                   full outer join (select rownum as id, t2::int as ver from regexp_split_to_table(version_b, '\.') as t2) as t2
                     on t1.id = t2.id
    loop
        if l_rec.ver_1 > l_rec.ver_2
        then
            return 1;
        elsif l_rec.ver_1 < l_rec.ver_2
        then
            return -1;
        end if;
    end loop;

    return 0;
end;
$$ language plpgsql;
-- =========================================================================
-- 测试用例：
-- ----------
-- select compat_tools.f_unit_test('f_version_compare (''1.1.1'', ''2.0.0'')', '-1');
-- select compat_tools.f_unit_test('f_version_compare (''2.1.1'', ''2.0.0'')', '1');
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3'', ''1.2.3'')', '0');
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3'', ''1.12.3'')', '-1');
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3.4'', ''1.2.3'')', '1');
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3'', ''1.2.3.4'')', '-1');
-- select compat_tools.f_unit_test('f_version_compare (''1.2...3'', ''1.2.3.4'')', 'NULL', 'IS');
-- select compat_tools.f_unit_test('f_version_compare (null, ''1.2.3.4'')', 'NULL', 'IS');
-- select * from compat_tools.compat_testing where test_expr like 'f_version_compare%';
-- =========================================================================



-- =============================================================================
-- All creations are running in ANONYMOUS BLOCK
-- =============================================================================
do $VIEW_CREATION$
declare
    l_cnt    bigint;
    l_result text;
begin

    begin
     execute 'set dolphin.sql_mode = ''sql_mode_full_group,pipes_as_concat,ansi_quotes,pad_char_to_full_length''';
     exception when others then null;
    end;
    -- =========================================================================
    -- 注意： ifnull 效果等同于 coalesce 函数，在条件允许的情况下，建议修改 SQL 使用 coalesce 函数，几乎在各个数据库中均支持
    -- ifnull(text,text)
    -- ifnull(numeric,numeric)
    -- ifnull(timestamp,timestamp)
    -- ifnull(timestamptz,timestamptz)
    -- =========================================================================
     
    if compat_tools.drop_compat_function('function', 'ifnull(text,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.ifnull(p1 text, p2 text)
        RETURNS text IMMUTABLE AS $$
        SELECT coalesce(p1, p2);
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'ifnull(numeric,numeric)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.ifnull(p1 numeric, p2 numeric)
        RETURNS numeric IMMUTABLE AS $$
        SELECT coalesce(p1, p2);
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'ifnull(timestamp,timestamp)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.ifnull(p1 timestamp, p2 timestamp)
        RETURNS timestamp IMMUTABLE AS $$
        SELECT coalesce(p1, p2);
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'ifnull(timestamptz,timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.ifnull(p1 timestamptz, p2 timestamptz)
        RETURNS timestamptz IMMUTABLE AS $$
        SELECT coalesce(p1, p2);
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '3.1.0') = 1 then
    select compat_tools.f_unit_test('pg_typeof(ifnull(123, 1111))', '''numeric''',p_db_compat =>'A') into l_result;
    select compat_tools.f_unit_test('pg_typeof(ifnull(123, 1111))', '''numeric''',p_db_compat =>'PG') into l_result;
    select compat_tools.f_unit_test('pg_typeof(ifnull(123, 1111))', '''integer''',p_db_compat =>'B') into l_result;
    else
    select compat_tools.f_unit_test('pg_typeof(ifnull(123, 1111))', '''numeric''') into l_result;
    end if;

    select compat_tools.f_unit_test('pg_typeof(ifnull(''xyz'', ''abc''))', '''text''') into l_result;
    select compat_tools.f_unit_test('pg_typeof(ifnull(now()::timestamp without time zone, now()::timestamp without time zone))', '''timestamp without time zone''') into l_result;
    select compat_tools.f_unit_test('pg_typeof(ifnull(''xyz''::text, 1111))', '''text''') into l_result;
    select compat_tools.f_unit_test('ifnull(null, null)', 'NULL', 'IS') into l_result;

    select compat_tools.f_unit_test('ifnull(123, 1111)', '123') into l_result;
    select compat_tools.f_unit_test('ifnull(null, 1111)', '1111') into l_result;
    select compat_tools.f_unit_test('ifnull(123, null)', '123') into l_result;

    select compat_tools.f_unit_test('ifnull(''xyz'', ''abc'')', '''xyz''') into l_result;
    select compat_tools.f_unit_test('ifnull(null, ''abc'')', '''abc''') into l_result;
    select compat_tools.f_unit_test('ifnull(''xyz'', null)', '''xyz''') into l_result;

    select compat_tools.f_unit_test('ifnull(''2012-12-12 12:12:12''::timestamp, ''1111-11-11 11:11:11''::timestamp)', '''2012-12-12 12:12:12''') into l_result;
    select compat_tools.f_unit_test('ifnull(null, ''1111-11-11 11:11:11''::timestamp)', '''1111-11-11 11:11:11''') into l_result;
    select compat_tools.f_unit_test('ifnull(''2012-12-12 12:12:12''::timestamp, null)', '''2012-12-12 12:12:12''') into l_result;

    select compat_tools.f_unit_test('ifnull(''xyz''::text, 1111)', '''xyz''') into l_result;
    select compat_tools.f_unit_test('ifnull(null::text, 1111)', '''1111''') into l_result;
    select compat_tools.f_unit_test('ifnull(''xyz''::text, null)', '''xyz''') into l_result;
 
    
    -- =========================================================================


    -- =========================================================================
    -- find_in_set(text,text)
    -- find_in_set(text,text[])
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'find_in_set(text,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.find_in_set(str text, strlist text)
        RETURNS bigint IMMUTABLE AS $$
        select case when str is null or strlist is null then null
                    else (select coalesce(min(id), 0) from (select rownum as id, unnest(string_to_array(strlist, ',')) as elem) as x where elem = str)
               end;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'find_in_set(text,text[])', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.find_in_set(str text, strlist text[])
        RETURNS bigint IMMUTABLE AS $$
        select case when str is null or strlist is null then null
                    else (select coalesce(min(id), 0) from (select rownum as id, unnest(strlist) as elem) as x where elem = str)
               end;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('find_in_set(''b'', ''a,b,c,dx'')', '2') into l_result;
    select compat_tools.f_unit_test('find_in_set(''x'', ''a,b,c,dx'')', '0') into l_result;
    select compat_tools.f_unit_test('find_in_set(''d'', ''a,b,c,dx'')', '0') into l_result;
    select compat_tools.f_unit_test('find_in_set(''dx'', ''a,b,c,dx'')', '4') into l_result;

    select compat_tools.f_unit_test('find_in_set(null,''a,b,c,dx'')', 'NULL', 'IS') into l_result;
    select compat_tools.f_unit_test('find_in_set(''x'',null)', 'NULL', 'IS') into l_result;
    select compat_tools.f_unit_test('find_in_set('''',''a,b,c,dx'')', '0', p_db_compat => 'B') into l_result;            -- Failed in A
    select compat_tools.f_unit_test('find_in_set('''',''a,b,c,dx'')', '0', p_db_compat => 'PG') into l_result;           -- Failed in A
    select compat_tools.f_unit_test('find_in_set(''x'','''')', '0', p_db_compat => 'B') into l_result;                   -- Failed in A
    select compat_tools.f_unit_test('find_in_set(''x'','''')', '0', p_db_compat => 'PG') into l_result;                  -- Failed in A
    select compat_tools.f_unit_test('find_in_set('''',''a,b,c,,dx'')', '4', p_db_compat => 'B') into l_result;           -- Failed in A
    select compat_tools.f_unit_test('find_in_set('''',''a,b,c,,dx'')', '4', p_db_compat => 'PG') into l_result;          -- Failed in A

    select compat_tools.f_unit_test('find_in_set(''b'', string_to_array(''a,b,c,dx'', '',''))', '2') into l_result;
    select compat_tools.f_unit_test('find_in_set(''x'', string_to_array(''a,b,c,dx'', '',''))', '0') into l_result;
    select compat_tools.f_unit_test('find_in_set(''d'', string_to_array(''a,b,c,dx'', '',''))', '0') into l_result;
    select compat_tools.f_unit_test('find_in_set(''dx'', string_to_array(''a,b,c,dx'', '',''))', '4') into l_result;
    select compat_tools.f_unit_test('find_in_set(null, string_to_array(''a,b,c,dx'', '',''))', 'NULL', 'IS') into l_result;
    select compat_tools.f_unit_test('find_in_set('''', string_to_array(''a,b,c,dx'', '',''))', '0', p_db_compat => 'B') into l_result;       -- Failed in A
    select compat_tools.f_unit_test('find_in_set('''', string_to_array(''a,b,c,dx'', '',''))', '0', p_db_compat => 'PG') into l_result;      -- Failed in A
    select compat_tools.f_unit_test('find_in_set(''x'', string_to_array('''', '',''))', '0', p_db_compat => 'B') into l_result;              -- Failed in A
    select compat_tools.f_unit_test('find_in_set(''x'', string_to_array('''', '',''))', '0', p_db_compat => 'PG') into l_result;             -- Failed in A
    select compat_tools.f_unit_test('find_in_set('''', string_to_array(''a,b,c,,dx'', '',''))', '4', p_db_compat => 'B') into l_result;      -- Failed in A
    select compat_tools.f_unit_test('find_in_set('''', string_to_array(''a,b,c,,dx'', '',''))', '4', p_db_compat => 'PG') into l_result;     -- Failed in A
    -- =========================================================================


    -- =========================================================================
    -- field(text,VARIADIC text[])
    -- field(numeric,VARIADIC numeric[])
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'field(text,text[])', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.field(str text, VARIADIC strlist text[])
        RETURNS bigint IMMUTABLE AS $$
        select case when str is null or strlist is null then 0
                    else (select coalesce(min(id), 0) from (select rownum as id, unnest(strlist) as elem) as x where elem = str)
               end;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'field(numeric,numeric[])', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.field(str numeric, VARIADIC strlist numeric[])
        RETURNS bigint IMMUTABLE AS $$
        select case when str is null or strlist is null then 0
                    else (select coalesce(min(id), 0) from (select rownum as id, unnest(strlist) as elem) as x where elem = str)
               end;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('field(''Bb'', ''Aa'', ''Bb'', ''Cc'', ''Dd'', ''Ff'')', '2') into l_result;
    select compat_tools.f_unit_test('field(''Gg'', ''Aa'', ''Bb'', ''Cc'', ''Dd'', ''Ff'')', '0') into l_result;
    select compat_tools.f_unit_test('field(123, 1.23, 12.3, 123, 1234, 0.12)', '3') into l_result;
    select compat_tools.f_unit_test('field(123, 1.23, 12.3,  23, 1234, 0.12)', '0') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- elt(int4,VARIADIC text[])
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'elt(int4,text[])', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.elt(str_pos int, VARIADIC strlist text[])
        RETURNS text IMMUTABLE AS $$
        select min(elem) from (select rownum as id, unnest(strlist) as elem) as x where id = str_pos;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('elt(  2, ''Aa'', ''Bb'', ''Cc'', ''Dd'', ''Ff'')', '''Bb''') into l_result;
    select compat_tools.f_unit_test('elt(1.2, ''Aa'', ''Bb'', ''Cc'', ''Dd'', ''Ff'')', '''Aa''') into l_result;
    select compat_tools.f_unit_test('elt(  0, ''Aa'', ''Bb'', ''Cc'', ''Dd'', ''Ff'')', 'NULL', 'IS') into l_result;
    select compat_tools.f_unit_test('elt( 10, ''Aa'', ''Bb'', ''Cc'', ''Dd'', ''Ff'')', 'NULL', 'IS') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- if(bool,bool,bool)
    -- if(bool,text,text)
    -- if(bool,numeric,numeric)
    -- if(bool,timestamptz,timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'if(bool,bool,bool)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.if(check_condition boolean, true_expr bool, false_expr bool)
        RETURNS bool IMMUTABLE AS $$
        select case when check_condition then true_expr else false_expr end;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'if(bool,text,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.if(check_condition boolean, true_expr text, false_expr text)
        RETURNS text IMMUTABLE AS $$
        select case when check_condition then true_expr else false_expr end;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'if(bool,numeric,numeric)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.if(check_condition boolean, true_expr numeric, false_expr numeric)
        RETURNS numeric IMMUTABLE AS $$
        select case when check_condition then true_expr else false_expr end;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'if(bool,timestamptz,timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.if(check_condition boolean, true_expr timestamptz, false_expr timestamptz)
        RETURNS timestamptz IMMUTABLE AS $$
        select case when check_condition then true_expr else false_expr end;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '3.1.0') = 1 then
    select compat_tools.f_unit_test('if(1=1, ''yes''::bool, ''no''::bool)::text', '''true''',p_db_compat =>'A') into l_result;
    select compat_tools.f_unit_test('if(1=1, ''yes''::bool, ''no''::bool)::text', '''true''',p_db_compat =>'PG') into l_result;
    select compat_tools.f_unit_test('if(1=1, ''yes''::bool, ''no''::bool)::text', '''1''',p_db_compat =>'B') into l_result;
    select compat_tools.f_unit_test('pg_typeof(if(''hongye''!=''debug'', 1, 2))', '''numeric''',p_db_compat =>'A') into l_result;
    select compat_tools.f_unit_test('pg_typeof(if(''hongye''!=''debug'', 1, 2))', '''numeric''',p_db_compat =>'PG') into l_result;
    select compat_tools.f_unit_test('pg_typeof(if(''hongye''!=''debug'', 1, 2))', '''integer''',p_db_compat =>'B') into l_result;
    else 
    select compat_tools.f_unit_test('if(1=1, ''yes''::bool, ''no''::bool)::text', '''true''') into l_result;
    select compat_tools.f_unit_test('pg_typeof(if(''hongye''!=''debug'', 1, 2))', '''numeric''') into l_result;
    end if;

    select compat_tools.f_unit_test('pg_typeof(if(1=1, ''yes''::bool, ''no''::bool))', '''boolean''') into l_result;
    select compat_tools.f_unit_test('pg_typeof(if(1<2, ''yes'', ''no''))', '''text''') into l_result;
    select compat_tools.f_unit_test('pg_typeof(if(instr(''hongye'', ''x''), ''1212-12-12''::timestamp, ''1111-11-11''::timestamp))', '''timestamp with time zone''') into l_result;

    select compat_tools.f_unit_test('if(1<2, ''yes'', ''no'')', '''yes''') into l_result;
    select compat_tools.f_unit_test('if(''hongye''!=''debug'', 1, 2)', '1') into l_result;
    select compat_tools.f_unit_test('if(instr(''hongye'', ''x''), ''1212-12-12''::timestamp, ''1111-11-11''::timestamp)::timestamp', '''1111-11-11 00:00:00''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- strcmp(text,text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'strcmp(text,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.strcmp(p1 text, p2 text)
        RETURNS int IMMUTABLE AS $$
        select case when p1 > p2 then 1 when p1 = p2 then 0 else -1 end;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('strcmp(''text'', ''text2'')', '-1') into l_result;
    select compat_tools.f_unit_test('strcmp(''text2'', ''text'')', '1') into l_result;
    select compat_tools.f_unit_test('strcmp(''text'', ''text'')', '0') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- log10(numeric)
    -- log10(float8)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'log10(numeric)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.log10(p1 numeric)
        RETURNS numeric IMMUTABLE AS $$
        select case when p1 <= 0 then null else pg_catalog.log(p1) end;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'log10(float8)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.log10(p1 double precision)
        RETURNS double precision IMMUTABLE AS $$
        select case when p1 <= 0 then null else pg_catalog.log(p1) end;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('log10(2)', '0.301029995663981') into l_result;
    select compat_tools.f_unit_test('log10(100)', '2') into l_result;
    select compat_tools.f_unit_test('log10(-100)', 'NULL', 'IS') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- insert(text,int8,int8,text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'insert(text,int8,int8,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.insert(p_source text, p_pos bigint, p_len bigint, p_replacement text)
        RETURNS text IMMUTABLE AS $$
        select case when p_source is null or p_pos is null or p_len is null or p_replacement is null then null
                    when p_pos <= 0 or p_pos > length(p_source) then p_source
                    else pg_catalog.substr(p_source, 1, p_pos - 1)||p_replacement||pg_catalog.substr(p_source, p_pos + p_len)
               end;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('insert(''Quadratic'', 3, 4, ''What'')', '''QuWhattic''') into l_result;
    select compat_tools.f_unit_test('insert(''Quadratic'', -1, 4, ''What'')', '''Quadratic''') into l_result;
    select compat_tools.f_unit_test('insert(''Quadratic'', 0, 2, ''What'')', '''Quadratic''') into l_result;
    select compat_tools.f_unit_test('insert(''Quadratic'', 1, 2, ''What'')', '''Whatadratic''') into l_result;
    select compat_tools.f_unit_test('insert(''Quadratic'', 9, 12, ''What'')', '''QuadratiWhat''') into l_result;
    select compat_tools.f_unit_test('insert(''Quadratic'', 3, 100, ''What'')', '''QuWhat''') into l_result;
    select compat_tools.f_unit_test('insert(''Quadratic'', 3, 100, null)', 'NULL', 'IS') into l_result;
    select compat_tools.f_unit_test('insert(''Quadratic'', null, 100, ''What'')', 'NULL', 'IS') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- lcase(text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'lcase(text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.lcase(p_source text)
        RETURNS text IMMUTABLE AS $$
        select pg_catalog.lower(p_source);
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('lcase(''QuadRatic'')', '''quadratic''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- ucase(text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'ucase(text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.ucase(p_source text)
        RETURNS text IMMUTABLE AS $$
        select pg_catalog.upper(p_source);
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('ucase(''QuadRatic'')', '''QUADRATIC''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- space(int4)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'space(int4)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.space(p_num int)
        RETURNS text IMMUTABLE AS $$
        select pg_catalog.repeat(' ', p_num);
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('concat(''|'', space(11), ''|'')', '''|           |''') into l_result;
    select compat_tools.f_unit_test('concat(''|'', space(-11), ''|'')', '''||''') into l_result;
    select compat_tools.f_unit_test('concat(''|'', space(0), ''|'')', '''||''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- mid(text,int8,int8)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'mid(text,int8,int8)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.mid(p_source text, p_pos bigint, p_len bigint)
        RETURNS text IMMUTABLE AS $$
        select pg_catalog.substr(p_source, p_pos, p_len);
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('mid(''Hongye'', 2, 4)', '''ongy''') into l_result;
    select compat_tools.f_unit_test('mid(''Hongye'', -2, 4)', '''ye''') into l_result;
    select compat_tools.f_unit_test('coalesce(mid(''Hongye'', 12, 4), ''NULL'')', 'coalesce('''', ''NULL'')') into l_result;    -- 数据库兼容模式 A/B
    select compat_tools.f_unit_test('coalesce(mid(''Hongye'', 2, -4), ''NULL'')', 'coalesce('''', ''NULL'')') into l_result;    -- 数据库兼容模式 A/B
    -- =========================================================================

    -- =========================================================================
    -- locate(text,text,int4)
    -- locate(text,text,int4,text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'locate(text,text,int4)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.locate( p_sub     text
                                                    , p_source  text
                                                    , p_pos     int     default 1)
        RETURNS int IMMUTABLE strict AS $$
        select pg_catalog.instr(p_source, p_sub, p_pos);
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'locate(text,text,int4,text)', '2.0')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.locate( p_sub text ,
                                              p_source text ,
                                              p_pos INT ,
                                              p_charset text ) RETURNS INT IMMUTABLE strict
        AS
            $$ 
        declare l_return INT;
        BEGIN
            IF p_charset is null or p_charset='' or p_charset='CODEUNITS32' THEN
                l_return:= pg_catalog.instr(p_source, p_sub, p_pos);
            ELSIF p_charset='OCTETS' THEN
                l_return:= pg_catalog.instrb(p_source, p_sub, p_pos);
            ELSEIF p_charset='CODEUNITS16' THEN
                raise 'CODEUNITS16 isn''t unsupport!';
            else 
                raise 'parameter p_charset error!';
            END IF;
            RETURN l_return;
        END;
        $$ LANGUAGE plpgsql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('locate(''bar'', ''foobarbar'')', '4') into l_result;
    select compat_tools.f_unit_test('locate(''xbar'', ''foobar'')', '0') into l_result;
    select compat_tools.f_unit_test('locate(''bar'', ''foobarbar'', 5)', '7') into l_result;
    select compat_tools.f_unit_test('locate(''bar'', ''foobarbar'', 7)', '7') into l_result;
    select compat_tools.f_unit_test('locate(null, ''foobarbar'', 7)', 'NULL', 'IS') into l_result;
    select compat_tools.f_unit_test('locate(''ba'', ''foobarbar'', null)', 'NULL', 'IS') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- unix_timestamp(timestamp)
    -- unix_timestamp(timestamptz)
    -- 
    -- =========================================================================
    select count(1) into l_cnt from pg_proc where proname='unix_timestamp' 
     and prosrc in ('unix_timestamp_no_args','unix_timestamp');
    if l_cnt<2 then 
    if compat_tools.drop_compat_function('function', 'unix_timestamp(timestamp)', '2.1')
    then
        CREATE or replace FUNCTION pg_catalog.unix_timestamp(p_date timestamp without time zone default date_trunc('second', now()))
        RETURNS double precision IMMUTABLE AS $$
        select greatest(extract(epoch from p_date),0)::float8;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'unix_timestamp(timestamptz)', '2.1')
    then
        CREATE or replace FUNCTION pg_catalog.unix_timestamp(p_date timestamptz)
        RETURNS double precision IMMUTABLE AS $$
        select greatest(extract(epoch from p_date),0)::float8;
        $$ LANGUAGE sql;
    end if;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('unix_timestamp()', '0', '>') into l_result;

    if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '3.1.0') = 1 then
    select compat_tools.f_unit_test('unix_timestamp(''1111-11-11 11:11:11''::timestamp)', '0',p_db_compat =>'A') into l_result;
    select compat_tools.f_unit_test('unix_timestamp(''1111-11-11 11:11:11''::timestamp)', '0',p_db_compat =>'PG') into l_result;
    select compat_tools.f_unit_test('unix_timestamp(''1111-11-11 11:11:11''::timestamp)', '0',p_db_compat =>'B') into l_result;
    select compat_tools.f_unit_test('unix_timestamp(''1212-12-12 12:12:12.123456''::timestamp)', '0',p_db_compat =>'A') into l_result;
    select compat_tools.f_unit_test('unix_timestamp(''1212-12-12 12:12:12.123456''::timestamp)', '0',p_db_compat =>'PG') into l_result;
    select compat_tools.f_unit_test('unix_timestamp(''1212-12-12 12:12:12.123456''::timestamp)', '0',p_db_compat =>'B') into l_result;
    else
    select compat_tools.f_unit_test('unix_timestamp(''1111-11-11 11:11:11''::timestamp)', '0') into l_result;
    select compat_tools.f_unit_test('unix_timestamp(''1212-12-12 12:12:12.123456''::timestamp)', '0') into l_result;
    end if;

    -- =========================================================================


    -- =========================================================================
    -- to_days(timestamp)
    -- to_days(timestamptz)
    -- to_days(text)
    -- to_days(text,text)
    -- to_days(int8)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'to_days(timestamp)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.to_days(p_date timestamp without time zone)
        RETURNS bigint IMMUTABLE AS $$
        SELECT floor(extract(epoch from p_date)/86400)::bigint + 719528;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'to_days(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.to_days(p_date timestamp with time zone)
        RETURNS bigint IMMUTABLE AS $$
        SELECT floor(extract(epoch from p_date::timestamp without time zone)/86400)::bigint + 719528;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'to_days(text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.to_days( p_date text)
        RETURNS bigint IMMUTABLE AS $$
        SELECT floor(extract(epoch from p_date::timestamp)/86400)::bigint + 719528;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'to_days(text,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.to_days( p_date text
                                                     , p_format text)
        RETURNS bigint IMMUTABLE AS $$
        SELECT floor(extract(epoch from to_timestamp(p_date, p_format))/86400)::bigint + 719528;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'to_days(int8)', '1.0', 'plpgsql')
    then
        CREATE or replace FUNCTION pg_catalog.to_days(p_date bigint)
        RETURNS bigint IMMUTABLE AS $$
        begin
            if p_date <= 100
            then
                return null;
            elsif p_date < 1000
            then
                -- 3位数字，年份部分暂时补充 2000，至少能保证短期结果正确
                return floor(extract(epoch from ('20000'||p_date)::timestamp)/86400)::bigint + 719528;
                -- return floor(extract(epoch from to_timestamp('20000'||p_date::text, 'YYYYMMDD'))/86400)::bigint + 719528;
            elsif p_date < 10000
            then
                -- 4位数字，年份部分暂时补充 2000，至少能保证短期结果正确
                return floor(extract(epoch from ('2000'||p_date)::timestamp)/86400)::bigint + 719528;
                -- return floor(extract(epoch from to_timestamp('2000'||p_date::text, 'YYYYMMDD'))/86400)::bigint + 719528;
            elsif p_date < 100000
            then
                -- 5 位数，使用格式 YYMMDD 格式化
                return floor(extract(epoch from ('0'||p_date)::timestamp)/86400)::bigint + 719528;
                -- return floor(extract(epoch from to_timestamp('0'||p_date::text, 'YYMMDD'))/86400)::bigint + 719528;
            elsif p_date < 1000000
            then
                -- 6 位数，使用格式 YYMMDD 格式化
                return floor(extract(epoch from p_date::text::timestamp)/86400)::bigint + 719528;
                -- return floor(extract(epoch from to_timestamp(p_date::text, 'YYMMDD'))/86400)::bigint + 719528;
            elsif p_date < 10000000
            then
                -- 7 位数，直接返回 NULL
                return null;
            elsif p_date > 99991231
            then
                -- 超过支持的最大值 99991231 直接返回 NULL
                return null;
            else
                -- 8 位数，使用格式 YYYYMMDD 格式化
                return floor(extract(epoch from p_date::text::timestamp)/86400)::bigint + 719528;
                -- return floor(extract(epoch from to_timestamp(p_date::text, 'YYYYMMDD'))/86400)::bigint + 719528;
            end if;
        exception
            when others then
                return null;
        end
        $$ LANGUAGE plpgsql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('to_days(''1111-11-11'')', '406098') into l_result;
    select compat_tools.f_unit_test('to_days(''1111-11-11''::timestamp without time zone)', '406098') into l_result;
    select compat_tools.f_unit_test('to_days(''1111-11-11''::timestamptz)', '406098') into l_result;
    select compat_tools.f_unit_test('to_days(to_date(''2020-02-02'', ''YYYY-MM-DD''))', '737822') into l_result;
    select compat_tools.f_unit_test('to_days(''2020-02-02'', ''YYYY-MM-DD'')', '737822') into l_result;
    select compat_tools.f_unit_test('to_days(12340501)', '450830') into l_result;
    select compat_tools.f_unit_test('to_days(950501)', '728779') into l_result;
    select compat_tools.f_unit_test('to_days(090501)', '733893') into l_result;
    select compat_tools.f_unit_test('to_days(50501)', '732432') into l_result;
    select compat_tools.f_unit_test('to_days(1101)', '730790') into l_result;
    select compat_tools.f_unit_test('to_days(501)', '730606') into l_result;
    select compat_tools.f_unit_test('to_days(101)', '730485') into l_result;
    select compat_tools.f_unit_test('to_days(100)', 'NULL', 'IS') into l_result;
    select compat_tools.f_unit_test('to_days(51)', 'NULL', 'IS') into l_result;
    select compat_tools.f_unit_test('to_days(1)', 'NULL', 'IS') into l_result;
    select compat_tools.f_unit_test('to_days(950551)', 'NULL', 'IS') into l_result;
    select compat_tools.f_unit_test('to_days(51501)', 'NULL', 'IS') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- to_seconds(timestamp)
    -- to_seconds(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'to_seconds(timestamp)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.to_seconds(p_date timestamp)
        RETURNS bigint IMMUTABLE AS $$
        SELECT floor(extract(epoch from p_date))::bigint + 62167219200;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'to_seconds(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.to_seconds(p_date timestamptz)
        RETURNS bigint IMMUTABLE AS $$
        SELECT floor(extract(epoch from p_date))::bigint + 62167219200;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    --     select to_seconds(950501);                 -- 62966505600 (暂时不支持)
    select compat_tools.f_unit_test('to_seconds(''2009-11-29''::timestamp)', '63426672000') into l_result;
    select compat_tools.f_unit_test('to_seconds(''2009-11-29 13:43:32''::timestamptz) - to_seconds(''1999-11-29 11:22:33''::timestamptz)', '315627659') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- timediff(timestamptz,timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'timediff(timestamptz,timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.timediff(p_date1 timestamptz, p_date2 timestamptz)
        RETURNS interval IMMUTABLE AS $$
        SELECT p_date1 - p_date2;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('timediff(''2000-01-01 00:00:00'', ''2000-01-01 00:00:00.000001'')', '''-00:00:00.000001''') into l_result;
    select compat_tools.f_unit_test('timediff(''2008-12-31 23:59:59.000001'', ''2008-12-30 01:01:01.000002'')', '''1 day 22:58:57.999999''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- time_to_sec(time)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'time_to_sec(time)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.time_to_sec(p_time time)
        RETURNS int IMMUTABLE AS $$
        select extract(epoch from p_time::time)::int;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('time_to_sec(''22:23:00'')', '80580') into l_result;
    select compat_tools.f_unit_test('time_to_sec(''00:39:38'')', '2378') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- date_format(timestamp,text)
    -- date_format(timestamptz,text)
    -- 测试备注：
    --     select t
    --          , to_char(t, 'D') as d
    --          , to_char(t + interval '1 day', 'IDDD') as iddd_1
    --          , to_char(t, 'DDD') as ddd
    --          , to_char(t, 'Day') as week
    --          , to_char(t, 'DDD')::int monday
    --          , (select case when c_first = 1  -- 元旦是周日
    --                         then ceil(c_day/7)
    --                         when c_day <= (8 - c_first)  -- 去年的周
    --                         then case p_first
    --                              when 1 then ceil(p_last/7)
    --                              -- when 2 then ceil((p_last - 6)/7)
    --                              -- when 3 then ceil((p_last - 5)/7)
    --                              -- when 4 then ceil((p_last - 4)/7)
    --                              -- when 5 then ceil((p_last - 3)/7)
    --                              -- when 6 then ceil((p_last - 2)/7)
    --                              -- else ceil((p_last - 1)/7)
    --                              else ceil((p_last - (8 - p_first))/7)
    --                              end
    --                         else ceil((c_day - (8 - c_first)) / 7)  -- 今年的周
    --                         -- else case c_first  -- 今年的周
    --                         --      when 2 then ceil((c_day - 6)/7)
    --                         --      when 3 then ceil((c_day - 5)/7)
    --                         --      when 4 then ceil((c_day - 4)/7)
    --                         --      when 5 then ceil((c_day - 3)/7)
    --                         --      when 6 then ceil((c_day - 2)/7)
    --                         --      else ceil((c_day - 1)/7)
    --                         --      end
    --                         end
    --               from (select to_char(t, 'DDD')::int as c_day
    --                          , to_char(date_trunc('year', t), 'D')::int as c_first
    --                          , to_char(date_trunc('year', t) - interval '1 year', 'D')::int as p_first
    --                          , to_char(date_trunc('year', t) - interval '1 day', 'DDD')::int as p_last
    --                    )
    --            ) as result
    --       from (select '1996-01-01'::timestamp + (interval '1 day' * d) as t from generate_series(0, 368) as d) as x;
    -- =========================================================================
    -- 变更记录：
    --     v1.1 => 解决不同兼容模式下 NULL 与空格导致的部分测试用例失败的问题（A/B）
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'date_format(timestamp,text)', '1.1')
    then
        CREATE or replace FUNCTION pg_catalog.date_format( p_ts  timestamp
                                                         , p_fmt text)
        RETURNS text IMMUTABLE AS $$
        select to_char( p_ts
                      , string_agg(case
                                   when fmt is null or fmt = '' then case when id = 1 then '' else '%' end
                                   else case substr(fmt, 1, 1)
                                        when 'I' then 'HH12'
                                        when 'l' then 'FMHH12'
                                        when 'h' then 'HH12'
                                        when 'H' then 'HH24'
                                        when 'k' then 'FMHH24'
                                        when 'i' then 'MI'
                                        when 'S' then 'SS'
                                        when 's' then 'SS'
                                        when 'f' then 'US'
                                        when 'p' then 'PM'
                                        when 'Y' then 'YYYY'
                                        when 'y' then 'YY'
                                        when 'M' then 'FMMonth'
                                        when 'b' then 'Mon'
                                        when 'c' then 'FMMM'
                                        when 'm' then 'MM'
                                        when 'a' then 'Dy'
                                        when 'W' then 'FMDay'
                                        when 'j' then 'DDD'
                                        when 'd' then 'DD'
                                        when 'e' then 'FMDD'
                                        when 'w' then (to_char(p_ts, 'D')::int - 1)::text
                                        when 'D' then 'FMDDth'
                                        when 'U' then lpad((select case when c_first = 1  -- 元旦是周日
                                                                        then ceil(c_day/7)
                                                                        when c_day <= (8 - c_first)  -- 去年的周
                                                                        then 0
                                                                        else ceil((c_day - (8 - c_first)) / 7)  -- 今年的周
                                                                   end
                                                              from (select to_char(p_ts, 'DDD')::int as c_day
                                                                          , to_char(date_trunc('year', p_ts), 'D')::int as c_first
                                                                   )
                                                           )::text, 2, '0')  -- Mode 0
                                        when 'u' then case when to_char(p_ts, 'DDD') <= 7 and to_char(p_ts, 'IW') > 50 then '00' else 'IW' end  -- Mode 1
                                        when 'V' then lpad((select case when c_first = 1  -- 元旦是周日
                                                                        then ceil(c_day/7)
                                                                        when c_day <= (8 - c_first)  -- 去年的周
                                                                        then ceil((p_last - (case p_first when 1 then 0 else 8 - p_first end))/7)
                                                                        else ceil((c_day - (8 - c_first)) / 7)  -- 今年的周
                                                                   end
                                                              from (select to_char(p_ts, 'DDD')::int as c_day
                                                                          , to_char(date_trunc('year', p_ts), 'D')::int as c_first
                                                                          , to_char(date_trunc('year', p_ts) - interval '1 year', 'D')::int as p_first
                                                                          , to_char(date_trunc('year', p_ts) - interval '1 day', 'DDD')::int as p_last
                                                                   )
                                                           )::text, 2, '0')  -- Mode 2
                                        when 'v' then 'IW'  -- Mode 3
                                        when 'X' then case when to_char(date_trunc('year', p_ts), 'D')::int > 1 and to_char(p_ts, 'DDD')::int <= 8 - to_char(date_trunc('year', p_ts), 'D')::int
                                                           then lpad((to_char(p_ts, 'YYYY')::int - 1)::text, 4, '0')
                                                           else 'YYYY' end  -- Mode 2
                                        when 'x' then 'IYYY'  -- Mode 3
                                        when 'r' then 'HH12:MI:SS AM'
                                        when 'T' then 'HH24:MI:SS'
                                        when chr(31) then '%'
                                        else '"' || substr(fmt, 1, 1) || '"'  -- 预防存在其他 MogDB 中存在的额外格式值
                                        end || case when substr(fmt, 2) >= ' ' then '"' || substr(fmt, 2) || '"' else '' end  -- 预防存在其他 MogDB 中存在的额外格式值
                                   end, ''))
          from (select rownum as id, regexp_split_to_table(replace(p_fmt, '%%', '%'||chr(31)), '%') as fmt);
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'date_format(timestamptz,text)', '1.1')
    then
        CREATE or replace FUNCTION pg_catalog.date_format( p_ts  timestamptz
                                                         , p_fmt text)
        RETURNS text IMMUTABLE AS $$
        select to_char( p_ts
                      , string_agg(case
                                   when fmt is null or fmt = '' then case when id = 1 then '' else '%' end
                                   else case substr(fmt, 1, 1)
                                        when 'I' then 'HH12'
                                        when 'l' then 'FMHH12'
                                        when 'h' then 'HH12'
                                        when 'H' then 'HH24'
                                        when 'k' then 'FMHH24'
                                        when 'i' then 'MI'
                                        when 'S' then 'SS'
                                        when 's' then 'SS'
                                        when 'f' then 'US'
                                        when 'p' then 'PM'
                                        when 'Y' then 'YYYY'
                                        when 'y' then 'YY'
                                        when 'M' then 'FMMonth'
                                        when 'b' then 'Mon'
                                        when 'c' then 'FMMM'
                                        when 'm' then 'MM'
                                        when 'a' then 'Dy'
                                        when 'W' then 'FMDay'
                                        when 'j' then 'DDD'
                                        when 'd' then 'DD'
                                        when 'e' then 'FMDD'
                                        when 'w' then (to_char(p_ts, 'D')::int - 1)::text
                                        when 'D' then 'FMDDth'
                                        when 'U' then lpad((select case when c_first = 1  -- 元旦是周日
                                                                        then ceil(c_day/7)
                                                                        when c_day <= (8 - c_first)  -- 去年的周
                                                                        then 0
                                                                        else ceil((c_day - (8 - c_first)) / 7)  -- 今年的周
                                                                   end
                                                              from (select to_char(p_ts, 'DDD')::int as c_day
                                                                          , to_char(date_trunc('year', p_ts), 'D')::int as c_first
                                                                   )
                                                           )::text, 2, '0')  -- Mode 0
                                        when 'u' then case when to_char(p_ts, 'DDD') <= 7 and to_char(p_ts, 'IW') > 50 then '00' else 'IW' end  -- Mode 1
                                        when 'V' then lpad((select case when c_first = 1  -- 元旦是周日
                                                                        then ceil(c_day/7)
                                                                        when c_day <= (8 - c_first)  -- 去年的周
                                                                        then ceil((p_last - (case p_first when 1 then 0 else 8 - p_first end))/7)
                                                                        else ceil((c_day - (8 - c_first)) / 7)  -- 今年的周
                                                                   end
                                                              from (select to_char(p_ts, 'DDD')::int as c_day
                                                                         , to_char(date_trunc('year', p_ts), 'D')::int as c_first
                                                                         , to_char(date_trunc('year', p_ts) - interval '1 year', 'D')::int as p_first
                                                                         , to_char(date_trunc('year', p_ts) - interval '1 day', 'DDD')::int as p_last
                                                                   )
                                                           )::text, 2, '0')  -- Mode 2
                                        when 'v' then 'IW'  -- Mode 3
                                        when 'X' then case when to_char(date_trunc('year', p_ts), 'D')::int > 1 and to_char(p_ts, 'DDD')::int <= 8 - to_char(date_trunc('year', p_ts), 'D')::int
                                                           then lpad((to_char(p_ts, 'YYYY')::int - 1)::text, 4, '0')
                                                           else 'YYYY' end  -- Mode 2
                                        when 'x' then 'IYYY'  -- Mode 3
                                        when 'r' then 'HH12:MI:SS AM'
                                        when 'T' then 'HH24:MI:SS'
                                        when chr(31) then '%'
                                        else '"' || substr(fmt, 1, 1) || '"'  -- 预防存在其他 MogDB 中存在的额外格式值
                                        end || case when substr(fmt, 2) >= ' ' then '"' || substr(fmt, 2) || '"' else '' end  -- 预防存在其他 MogDB 中存在的额外格式值
                                   end, ''))
          from (select rownum as id, regexp_split_to_table(replace(p_fmt, '%%', '%'||chr(31)), '%') as fmt);
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('date_format(''2009-10-04 22:23:00'', ''%W %M %Y'')', '''Sunday October 2009''') into l_result;
    select compat_tools.f_unit_test('date_format(''2007-10-04 22:23:00'', ''%% %H:%i:%s%'')', '''% 22:23:00%''') into l_result;
    select compat_tools.f_unit_test('date_format(''1900-10-04 22:23:00'', ''%D %y %a %d %m %b %j'')', '''4th 00 Thu 04 10 Oct 277''') into l_result;
    select compat_tools.f_unit_test('date_format(''1997-10-04 22:23:00'', ''%H %k %I %r %T %S %w'')', '''22 22 10 10:23:00 PM 22:23:00 00 6''') into l_result;
    select compat_tools.f_unit_test('date_format(''1999-01-01'', ''Q %qQ %J'')', '''Q qQ J''') into l_result;
    select compat_tools.f_unit_test('date_format(''2006-01-01'', ''%X %x %V %v %U %u'')', '''2006 2005 01 52 01 00''') into l_result;
    select compat_tools.f_unit_test('date_format(''1999-01-02'', ''%X %x %V %v %U %u'')', '''1998 1998 52 53 00 00''') into l_result;
    select compat_tools.f_unit_test('date_format(''1999-01-03'', ''%X %x %V %v %U %u'')', '''1999 1998 01 53 01 00''') into l_result;
    select compat_tools.f_unit_test('date_format(''1999-01-04'', ''%X %x %V %v %U %u'')', '''1999 1999 01 01 01 01''') into l_result;

    -- select compat_tools.f_unit_test('date_format(''2006-06-00'', ''%d'')', '00') into l_result;  -- 不支持
    -- =========================================================================


    -- =========================================================================
    -- from_unixtime(int8)
    -- from_unixtime(numeric)
    -- from_unixtime(numeric,text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'from_unixtime(int8)', '1.1')
    then
        CREATE or replace FUNCTION pg_catalog.from_unixtime(p_ts bigint)
        RETURNS timestamp without time zone IMMUTABLE AS $$
        select to_date('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + interval '1 seconds' * p_ts;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'from_unixtime(numeric)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.from_unixtime(p_ts numeric)
        RETURNS timestamp without time zone IMMUTABLE AS $$
        select to_timestamp('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + interval '1 seconds' * p_ts;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'from_unixtime(numeric,text)', '2.0')
    then
        CREATE or replace FUNCTION pg_catalog.from_unixtime(p_ts numeric, p_format text)
        RETURNS text IMMUTABLE AS $$
        select date_format(to_timestamp('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + interval '1 seconds' * p_ts, p_format);
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('from_unixtime(123456789)', '''1973-11-29 21:33:09''') into l_result;
    select compat_tools.f_unit_test('from_unixtime(123456789.123456)', '''1973-11-29 21:33:09.123456''') into l_result;
    select compat_tools.f_unit_test('from_unixtime(123456789.123456, ''%Y.%m.%d %H.%i.%S'')', '''1973.11.29 21.33.09''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- sec_to_time(int4)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'sec_to_time(int4)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.sec_to_time(p_secs int)
        RETURNS interval IMMUTABLE AS $$
        select justify_interval(numtodsinterval(p_secs, 'second'));
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('sec_to_time(80580)', '''22:23:00''') into l_result;
    select compat_tools.f_unit_test('sec_to_time(2378)', '''00:39:38''') into l_result;
    select compat_tools.f_unit_test('sec_to_time(805801)', '''9 days 07:50:01''') into l_result;
    select compat_tools.f_unit_test('sec_to_time(8058011)', '''3 mons 3 days 06:20:11''') into l_result;  -- MySQL (34 days, 22:59:59) 达到上限了
    -- =========================================================================


    -- =========================================================================
    -- to_base64(text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'to_base64(text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.to_base64(p_str text)
        RETURNS text IMMUTABLE AS $$
        select encode(p_str::bytea, 'base64'::text);
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('to_base64(''abc'')', '''YWJj''') into l_result;
    select compat_tools.f_unit_test('to_base64(''Hongye'')', '''SG9uZ3ll''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- from_base64(text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'from_base64(text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.from_base64(p_str text)
        RETURNS text IMMUTABLE AS $$
        select convert_from(decode(p_str, 'base64'), 'UTF8');
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('from_base64(''YWJj'')', '''abc''') into l_result;
    select compat_tools.f_unit_test('from_base64(''SG9uZ3ll'')', '''Hongye''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- truncate(numeric,int4)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'truncate(numeric,int4)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.truncate(p_num numeric, p_prec int4)
        RETURNS numeric IMMUTABLE AS $$
        select trunc(p_num, p_prec);
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('truncate(1.223,1)', '1.2') into l_result;
    select compat_tools.f_unit_test('truncate(1.999, 1)', '1.9') into l_result;
    select compat_tools.f_unit_test('truncate(1.999, 0)', '1') into l_result;
    select compat_tools.f_unit_test('truncate(1.999, -1)', '0') into l_result;
    select compat_tools.f_unit_test('truncate(121.999, -1)', '120') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- rand_setseed(int4)
    -- rand(int4)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'rand_setseed(int4)', '1.0', 'plpgsql')
    then
        CREATE or replace FUNCTION pg_catalog.rand_setseed(p_seed int)
        RETURNS double precision IMMUTABLE AS $$
        DECLARE
            l_result  text;
        begin
            select setseed(p_seed/4294967296::float8) into l_result;
            return 0;
        end;
        $$ LANGUAGE plpgsql;
    end if;
    if compat_tools.drop_compat_function('function', 'rand()', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.rand()
        RETURNS double precision AS $$
        select  random() ;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'rand(int4)', '2.0')
    then
        CREATE or replace FUNCTION pg_catalog.rand(p_seed int)
        RETURNS double precision AS $$
        select case when p_seed is null then random() else rand_setseed(p_seed) + random() end;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('rand()', '0.0', '>') into l_result;
    select compat_tools.f_unit_test('rand(123)', '0.0', '>') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- log2(numeric)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'log2(numeric)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.log2(p_num numeric)
        RETURNS numeric IMMUTABLE AS $$
        select case when p_num <= 0 then null else log(2, p_num) end;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('log2(64)', '6.0') into l_result;
    select compat_tools.f_unit_test('log2(123)::float8', '6.9425145053392399::float8') into l_result;
    select compat_tools.f_unit_test('log2(0)', 'NULL', 'IS') into l_result;
    select compat_tools.f_unit_test('log2(-12)', 'NULL', 'IS') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- uuid()
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'uuid()', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.uuid()
        RETURNS uuid VOLATILE AS $$
        select md5(random()::text || clock_timestamp()::text)::uuid;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('length(uuid()::text)', '36') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- uuid_to_bin(uuid,int4)
    --     swap_flag 参数暂不处理，由于生成的 uuid 是纯随机的，暂时做不到顺序处理
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'uuid_to_bin(uuid,int4)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.uuid_to_bin(p_uuid uuid, swap_flag int4 default 0)
        RETURNS bytea IMMUTABLE AS $$
        select ('\x'||replace(p_uuid::text, '-', ''))::bytea;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('uuid_to_bin(''0768a47d-d355-372b-4926-ee668b6cb443''::uuid)::text', '''\x0768a47dd355372b4926ee668b6cb443''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- bin_to_uuid(bytea,int4)
    --     swap_flag 参数暂不处理，由于生成的 uuid 是纯随机的，暂时做不到顺序处理
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'bin_to_uuid(bytea,int4)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.bin_to_uuid(p_uuid bytea, swap_flag int4 default 0)
        RETURNS uuid IMMUTABLE AS $$
        select (replace(p_uuid::text, '\x', ''))::uuid;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('bin_to_uuid(''\x0768a47dd355372b4926ee668b6cb443'')', '''0768a47d-d355-372b-4926-ee668b6cb443''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- timestampdiff(text,timestamptz,timestamptz)
    -- 本兼容函数用法与 MySQL 有区别：
    --     1. 必须指定 pg_catalog Schema，否则会报错 timestampdiff syntax is not supported.
    --     2. 第一个参数（时间单位），必须使用单引号包裹，但可以不区分大小写
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'timestampdiff(text,timestamptz,timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.timestampdiff( p_unit text
                                                           , p_ts1  timestamptz
                                                           , p_ts2  timestamptz)
        RETURNS bigint IMMUTABLE AS $$
        select (case upper(p_unit)
                when 'MICROSECOND'   then extract(epoch from p_ts2) * 1000000 - extract(epoch from p_ts1) * 1000000
                when 'SECOND'        then extract(epoch from p_ts2) - extract(epoch from p_ts1)
                when 'MINUTE'        then trunc((extract(epoch from p_ts2) - extract(epoch from p_ts1))/60)
                when 'HOUR'          then trunc((extract(epoch from p_ts2) - extract(epoch from p_ts1))/3600)
                when 'DAY'           then extract(DAY from p_ts2 - p_ts1)
                when 'WEEK'          then trunc((extract(DAY from p_ts2 - p_ts1))/7)
                when 'MONTH'         then (select (extract(YEAR from t) * 12 + extract(MONTH from t)) from (SELECT age(p_ts2, p_ts1) as t) as x)
                when 'QUARTER'       then (select trunc((extract(YEAR from t) * 12 + extract(MONTH from t))/3) from (SELECT age(p_ts2, p_ts1) as t) as x)
                when 'YEAR'          then extract(YEAR from age(p_ts2, p_ts1))
                else null
                end)::bigint;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('pg_catalog.timestampdiff (''MICROSECOND'', ''1911-11-11 11:23:45.123456''::timestamp without time zone, ''2021-12-12 12:12:12.654321''::timestamp without time zone)', 3474060507530865) into l_result;
    select compat_tools.f_unit_test('pg_catalog.timestampdiff (''SECOND'', ''1911-11-11 11:23:45''::timestamp without time zone, ''2021-12-12 12:12:12''::timestamp without time zone)', 3474060507) into l_result;
    select compat_tools.f_unit_test('pg_catalog.timestampdiff (''MINUTE'', ''1911-11-11 11:23:45''::timestamp without time zone, ''2021-12-12 12:12:12''::timestamp without time zone)', 57901008) into l_result;
    select compat_tools.f_unit_test('pg_catalog.timestampdiff (''HOUR'', ''1911-11-11 11:23:45''::timestamp without time zone, ''2021-12-12 12:12:12''::timestamp without time zone)', 965016) into l_result;
    select compat_tools.f_unit_test('pg_catalog.timestampdiff (''DAY'', ''1911-11-11 11:23:45''::timestamp without time zone, ''2021-12-12 12:12:12''::timestamp without time zone)', 40209) into l_result;
    select compat_tools.f_unit_test('pg_catalog.timestampdiff (''WEEK'', ''1911-11-11 11:23:45''::timestamp without time zone, ''2021-12-12 12:12:12''::timestamp without time zone)', 5744) into l_result;
    select compat_tools.f_unit_test('pg_catalog.timestampdiff (''MONTH'', ''1911-11-11 11:23:45''::timestamp without time zone, ''2021-12-12 12:12:12''::timestamp without time zone)', 1321) into l_result;
    select compat_tools.f_unit_test('pg_catalog.timestampdiff (''QUARTER'', ''1911-11-11 11:23:45''::timestamp without time zone, ''2021-12-12 12:12:12''::timestamp without time zone)', 440) into l_result;
    select compat_tools.f_unit_test('pg_catalog.timestampdiff (''YEAR'', ''1911-11-11 11:23:45''::timestamp without time zone, ''2021-12-12 12:12:12''::timestamp without time zone)', 110) into l_result;
    select compat_tools.f_unit_test('pg_catalog.timestampdiff (''day'', ''2021-12-12 12:12:12''::timestamp without time zone, ''1911-11-11 11:23:45''::timestamp without time zone)', -40209) into l_result;
    select compat_tools.f_unit_test('pg_catalog.timestampdiff (''year'',''2002-01-01''::timestamp without time zone, ''2001-01-01''::timestamp without time zone)', -1) into l_result;
    select compat_tools.f_unit_test('pg_catalog.timestampdiff (''year'',''2002-01-01''::timestamp without time zone, ''2001-01-02''::timestamp without time zone)', 0) into l_result;
    -- =========================================================================


    -- =========================================================================
    -- isnull(text)
    -- isnull(numeric)
    -- isnull(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'isnull(text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.isnull(text)
        RETURNS bool IMMUTABLE AS $$
        select (case when $1 is null then 'true' else 'false' end)::bool;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'isnull(numeric)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.isnull(numeric)
        RETURNS bool IMMUTABLE AS $$
        select (case when $1 is null then 'true' else 'false' end)::bool;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'isnull(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.isnull(timestamptz)
        RETURNS bool IMMUTABLE AS $$
        select (case when $1 is null then 'true' else 'false' end)::bool;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '5.0.0') = -1 then
    select compat_tools.f_unit_test('isnull(0)::text', '''false''') into l_result;
    select compat_tools.f_unit_test('isnull(1)::text', '''false''') into l_result;
    select compat_tools.f_unit_test('isnull(11)::text', '''false''') into l_result;
    select compat_tools.f_unit_test('isnull('''')::text', '''false''', p_db_compat => 'B') into l_result;    -- Failed in A
    select compat_tools.f_unit_test('isnull('''')::text', '''false''', p_db_compat => 'PG') into l_result;   -- Failed in A
    select compat_tools.f_unit_test('isnull(''x'')::text', '''false''') into l_result;
    select compat_tools.f_unit_test('isnull(null)::text', '''true''') into l_result;
    select compat_tools.f_unit_test('isnull(now())::text', '''false''') into l_result;
    select compat_tools.f_unit_test('isnull(''2012-12-12 12:34:56''::timestamp)::text', '''false''') into l_result;
    else
    select compat_tools.f_unit_test('isnull(0)::text', '''0''', p_db_compat => 'B') into l_result;
    select compat_tools.f_unit_test('isnull(1)::text', '''0''', p_db_compat => 'B') into l_result;
    select compat_tools.f_unit_test('isnull(11)::text', '''0''', p_db_compat => 'B') into l_result;
    select compat_tools.f_unit_test('isnull('''')::text', '''0''', p_db_compat => 'B') into l_result;  
    select compat_tools.f_unit_test('isnull(''x'')::text', '''0''', p_db_compat => 'B') into l_result;
    select compat_tools.f_unit_test('isnull(null)::text', '''1''', p_db_compat => 'B') into l_result;
    select compat_tools.f_unit_test('isnull(now())::text', '''0''', p_db_compat => 'B') into l_result;
    select compat_tools.f_unit_test('isnull(''2012-12-12 12:34:56''::timestamp)::text', '''0''', p_db_compat => 'B') into l_result;
    
    select compat_tools.f_unit_test('isnull(0)::text', '''false''', p_db_compat => 'A') into l_result;
    select compat_tools.f_unit_test('isnull(1)::text', '''false''', p_db_compat => 'A') into l_result;
    select compat_tools.f_unit_test('isnull(11)::text', '''false''', p_db_compat => 'A') into l_result;
    select compat_tools.f_unit_test('isnull(''x'')::text', '''false''', p_db_compat => 'A') into l_result;
    select compat_tools.f_unit_test('isnull(null)::text', '''true''', p_db_compat => 'A') into l_result;
    select compat_tools.f_unit_test('isnull(now())::text', '''false''', p_db_compat => 'A') into l_result;
    select compat_tools.f_unit_test('isnull(''2012-12-12 12:34:56''::timestamp)::text', '''false''', p_db_compat => 'A') into l_result;
    
    select compat_tools.f_unit_test('isnull(0)::text', '''false''', p_db_compat => 'PG') into l_result;
    select compat_tools.f_unit_test('isnull(1)::text', '''false''', p_db_compat => 'PG') into l_result;
    select compat_tools.f_unit_test('isnull(11)::text', '''false''', p_db_compat => 'PG') into l_result;
    select compat_tools.f_unit_test('isnull('''')::text', '''false''', p_db_compat => 'PG') into l_result;   -- Failed in A
    select compat_tools.f_unit_test('isnull(''x'')::text', '''false''', p_db_compat => 'PG') into l_result;
    select compat_tools.f_unit_test('isnull(null)::text', '''true''', p_db_compat => 'PG') into l_result;
    select compat_tools.f_unit_test('isnull(now())::text', '''false''', p_db_compat => 'PG') into l_result;
    select compat_tools.f_unit_test('isnull(''2012-12-12 12:34:56''::timestamp)::text', '''false''', p_db_compat => 'PG') into l_result;
    end if;
    -- =========================================================================


    -- =========================================================================
    -- str_to_date(text,text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'str_to_date(text,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.str_to_date( p_ts  text
                                                         , p_fmt text)
        RETURNS timestamp IMMUTABLE AS $$
        select to_timestamp( p_ts
                           , string_agg(case
                                        when fmt is null then (case when id = 1 then '' else '%' end)
                                        else case substr(fmt, 1, 1)
                                             when 'I' then 'HH12'
                                             when 'l' then 'FMHH12'
                                             when 'h' then 'HH12'
                                             when 'H' then 'HH24'
                                             when 'k' then 'FMHH24'
                                             when 'i' then 'MI'
                                             when 'S' then 'SS'
                                             when 's' then 'SS'
                                             when 'f' then 'US'
                                             when 'p' then 'PM'
                                             when 'Y' then 'YYYY'
                                             when 'y' then 'YY'
                                             when 'M' then 'FMMonth'
                                             when 'b' then 'Mon'
                                             when 'c' then 'FMMM'
                                             when 'm' then 'MM'
                                             when 'a' then 'Dy'
                                             when 'W' then 'FMDay'
                                             when 'j' then 'DDD'
                                             when 'd' then 'DD'
                                             when 'e' then 'FMDD'
                                             when 'D' then 'FMDDth'
                                             when 'x' then 'IYYY'
                                             when 'r' then 'HH12:MI:SS AM'
                                             when 'T' then 'HH24:MI:SS'
                                             when chr(31) then '%'
                                             else '"' || substr(fmt, 1, 1) || '"'  -- 预防存在其他 MogDB 中存在的额外格式值
                                             end || case when substr(fmt, 2) is null then '' else '"' || substr(fmt, 2) || '"' end  -- 预防存在其他 MogDB 中存在的额外格式值
                                        end, ''))
          from (select rownum as id, regexp_split_to_table(replace(p_fmt, '%%', '%'||chr(31)), '%') as fmt);
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    -- 注意： MySQL 和 openGauss 中的起始时间不一样
    --       MySQL 起始于 0000-00-00
    --       openGauss 起始于 0001-01-01
    select compat_tools.f_unit_test('str_to_date(''01,5,2013'',''%d,%m,%Y'')::text', '''2013-05-01 00:00:00''') into l_result;  --> ''2013-05-01''
    select compat_tools.f_unit_test('str_to_date(''May 1, 2013'',''%M %d,%Y'')::text', '''2013-05-01 00:00:00''') into l_result;  --> ''2013-05-01''
    select compat_tools.f_unit_test('str_to_date(''9'',''%m'')::text', '''0001-09-01 00:00:00 BC''') into l_result;  --> ''0000-09-00''
    select compat_tools.f_unit_test('str_to_date(''9'',''%s'')::text', '''0001-01-01 00:00:09 BC''') into l_result;  --> ''00:00:09''
    select compat_tools.f_unit_test('str_to_date(null, ''%m/%d/%Y'')', 'NULL', 'IS') into l_result;  --> null
    select compat_tools.f_unit_test('str_to_date(''04/31/2004'', null)', 'NULL', 'IS') into l_result;  --> null

    -- MySQL 中的以下场景不被支持：
    -- 1. 时间与格式串中存在某种不能识别的字符
    --    select str_to_date('a09:30:17','a%h:%i:%s');  --> '09:30:17'
    --    select str_to_date('a09:30:17','%h:%i:%s');  --> NULL
    --    select str_to_date('09:30:17a','%h:%i:%s');  --> '09:30:17'
    --    select str_to_date('abc','abc');  --> '0000-00-00'
    -- 2. 全 0 时间（年、月、日任意部分在 MogDB 中都不能为全零）
    --    select str_to_date('00/00/0000', '%m/%d/%Y');  --> '0000-00-00'
    -- 3. 其他不合法的时间，例如： 4月31（4月没有31）
    --    select str_to_date('04/31/2004', '%m/%d/%Y');  --> '2004-04-31'
    -- =========================================================================


    -- =========================================================================
    -- conv(text,int4,int4)
    -- 注意： 目前只支持 2, 10, 16 进制之间的互相转换
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'conv(text,int4,int4)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.conv( p_num        text
                                                  , p_from_base  int4
                                                  , p_to_base    int4)
        RETURNS text IMMUTABLE AS $$
        select case when p_from_base = 10 and p_to_base = 16 then upper(to_hex(p_num::bigint)::text)
                    when p_from_base = 10 and p_to_base = 2  then ltrim(p_num::bigint::bit(128)::text, '0')
                    when p_from_base = 16 and p_to_base = 10 then (select sum(n)::text
                                                                     from (select case substr(lower(p_num), n, 1)
                                                                                  when 'a' then 10
                                                                                  when 'b' then 11
                                                                                  when 'c' then 12
                                                                                  when 'd' then 13
                                                                                  when 'e' then 14
                                                                                  when 'f' then 15
                                                                                  else substr(lower(p_num), n, 1)::int
                                                                                  end * (16 ^ (max(n) over () - n)) as n
                                                                             from generate_series(1, length(p_num)) as n))
                    when p_from_base = 16 and p_to_base = 2  then ltrim(('x'||p_num)::varbit(128)::text, '0')
                    when p_from_base = 2 and p_to_base = 10  then (select sum(n)::text
                                                                     from (select substr(lower(p_num), n, 1)::int
                                                                                * (2 ^ (max(n) over () - n)) as n
                                                                             from generate_series(1, length(p_num)) as n))
                    when p_from_base = 2 and p_to_base = 16  then (select string_agg(hex_data, '' order by id desc)
                                                                     from (select rownum as id
                                                                                , case substr('000'||p_num, l - rownum * 4 + 4, 4)
                                                                                       -- 首部补 3 个零，避免高位不足 4 个，同时截取起始位置从 l - rownum * 4 + 1 变为 l - rownum * 4 + 4
                                                                                       when '0000' then '0'
                                                                                       when '0001' then '1'
                                                                                       when '0010' then '2'
                                                                                       when '0011' then '3'
                                                                                       when '0100' then '4'
                                                                                       when '0101' then '5'
                                                                                       when '0110' then '6'
                                                                                       when '0111' then '7'
                                                                                       when '1000' then '8'
                                                                                       when '1001' then '9'
                                                                                       when '1010' then 'A'
                                                                                       when '1011' then 'B'
                                                                                       when '1100' then 'C'
                                                                                       when '1101' then 'D'
                                                                                       when '1110' then 'E'
                                                                                       when '1111' then 'F'
                                                                                  end as hex_data
                                                                             from (select generate_series(1, ceil(length(p_num) / 4)) as n, length(p_num) as l)
                                                                            order by id desc))  -- 取值是从右往左取，拼接的时候从左往右
                    else null
               end;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('conv(''123456'', 10, 16)', '''1E240''') into l_result;
    select compat_tools.f_unit_test('conv(''123456'', 10, 2)', '''11110001001000000''') into l_result;
    select compat_tools.f_unit_test('conv(''1E240'', 16, 10)', '''123456''') into l_result;
    select compat_tools.f_unit_test('conv(''1E240'', 16, 2)', '''11110001001000000''') into l_result;
    select compat_tools.f_unit_test('conv(''11110001001000000'', 2, 10)', '''123456''') into l_result;
    select compat_tools.f_unit_test('conv(''11110001001000000'', 2, 16)', '''1E240''') into l_result;
    select compat_tools.f_unit_test('conv(''a'',16, 2)', '''1010''') into l_result;
    select compat_tools.f_unit_test('conv(''6e'',16, 10)', '''110''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- curdate()
    -- 注意： A模式和B模式，此函数输出格式不一样
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'curdate()', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.curdate()
        RETURNS date IMMUTABLE AS $$
        select current_date::date;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('curdate()', 'current_date', '=') into l_result;  --> null
    -- =========================================================================


    -- =========================================================================
    -- yearweek(timestamp,int4)
    -- yearweek(timestamptz,int4)
    -- yearweek(text,int4)
    -- 注意： openGauss输出结果与mysql第二个参数为3时输出一致
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'yearweek(timestamp,int4)', '1.0')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.yearweek(timestamp,int4 default 3)
        RETURNS text
        LANGUAGE plpgsql
        IMMUTABLE NOT FENCED NOT SHIPPABLE
        AS $$
        declare
        begin
        /*第二个参数mode,强制为3*/
                return EXTRACT(isoyear FROM $1)||
                    lpad(EXTRACT(WEEK FROM $1),2,'0');
        end;
        $$;
    end if;
    if compat_tools.drop_compat_function('function', 'yearweek(timestamptz,int4)', '1.0')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.yearweek(timestamptz,int4 default 3)
        RETURNS text
        LANGUAGE plpgsql
        IMMUTABLE NOT FENCED NOT SHIPPABLE
        AS $$
        declare
        begin
        /*第二个参数mode,强制为3*/
                return EXTRACT(isoyear FROM $1)||
                    lpad(EXTRACT(WEEK FROM $1),2,'0');
        end;
        $$;
    end if;
    if compat_tools.drop_compat_function('function', 'yearweek(text,int4)', '1.0')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.yearweek(text,int4 default 3)
        RETURNS text
        LANGUAGE plpgsql
        IMMUTABLE NOT FENCED NOT SHIPPABLE
        AS $$
        declare
        begin
        /*第二个参数mode,强制为3*/
                return EXTRACT(isoyear FROM $1)||
                    lpad(EXTRACT(WEEK FROM $1),2,'0');
        end;
        $$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('yearweek(''20220102'',3)', '''202152''', '=','B') into l_result;
    select compat_tools.f_unit_test('yearweek(20220102,3)', '''202152''', '=','B') into l_result;
    select compat_tools.f_unit_test('yearweek(''20220103'',3)', '''202201''', '=','B') into l_result;
    select compat_tools.f_unit_test('yearweek(date''2022-01-03'',3)', '''202201''', '=','B') into l_result;
    select compat_tools.f_unit_test('yearweek(20220103,3)', '''202201''', '=','B') into l_result;

    -- =========================================================================


    -- =========================================================================
    -- date_add(TIMESTAMP,interval)
    -- 注意： 第二个参数，数字要用单引号包裹
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'date_add(timestamp,interval)', '1.0')
    then
        create or replace function pg_catalog.date_add(timestamp,interval) RETURNS timestamp
        LANGUAGE sql
        IMMUTABLE NOT FENCED NOT SHIPPABLE
        AS $$
                select $1+$2;
        $$;
    end if;
    if compat_tools.drop_compat_function('function', 'date_add(timestamptz,interval)', '1.0')
    then
        create or replace function pg_catalog.date_add(timestamptz,interval) RETURNS timestamptz
        LANGUAGE sql
        IMMUTABLE NOT FENCED NOT SHIPPABLE
        AS $$
                select $1+$2;
        $$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('date_add(to_timestamp(''20180501'',''yyyymmdd''),INTERVAL ''1'' DAY)', '''2018-05-02 00:00:00.000''::timestamp', '=') into l_result;  --> null
    select compat_tools.f_unit_test('date_add(''2020-12-31 23:59:59'',INTERVAL ''1'' SECOND)', '''2021-01-01 00:00:00.000''::timestamp', '=') into l_result;  --> null
    -- =========================================================================

    -- =========================================================================
    -- json_extract(json,text)
    -- json_extract(json,VARIADIC text[])
    -- 注：仅3.0版本数据库可用
    -- select 
    -- json_extract('{"name":"mogdb","tel":"13888888888"}','$.name'), 
    -- json_extract('{"name":"mogdb","tel":"13888888888","c":{"d":"3"}}','$.c.d'),
    -- json_extract('[10, 20, [30, 40]]', '$[2][*]') ,
    -- json_extract('[10, 20, [30, 40]]', '$[2][1]') ,
    -- json_extract('[{"f2":{"f3":1},"f4":{"f5":99,"f6":[0,1,2]}},{"f4":"abc"}]','$[0].f4.f6[2]'), 
    -- json_extract('[10, 20, [30, 40]]', '$[1]', '$[0]');
    -- =========================================================================
    if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '2.0.0') = 1 and f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '5.0.0') = -1
    then
    if compat_tools.drop_compat_function('function', 'json_extract(json,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.json_extract (json,text)
        RETURNS setof json
        IMMUTABLE
        AS $$
        select $1 #> ('{'||substr(translate(replace($2,'[*]',''),'.[]$',',,'),2)||'}')::_text;
        $$ LANGUAGE sql;
    end if;

    if compat_tools.drop_compat_function('function', 'json_extract(json,text[])', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.json_extract (json,VARIADIC text[])
        RETURNS setof json
        IMMUTABLE
        AS $$
         select to_json(array_agg($1 #> ('{'||substr(translate(replace(s,'[*]',''),'.[]$',',,'),2)||'}')::_text))
       from unnest($2) s;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('json_extract(''{"name":"mogdb","tel":"13888888888"}'',''$.name'')', '''"mogdb"''', '=') into l_result;  
    select compat_tools.f_unit_test('json_extract(''{"name":"mogdb","tel":"13888888888","c":{"d":"3"}}'',''$.c.d'')', '''"3"''', '=') into l_result;   
    select compat_tools.f_unit_test('json_extract(''[10, 20, [30, 40]]'', ''$[2][*]'')', '''[30, 40]''', '=') into l_result; 
    select compat_tools.f_unit_test('json_extract(''[10, 20, [30, 40]]'', ''$[2][1]'')', '40', '=') into l_result; 
    select compat_tools.f_unit_test('json_extract(''[{"f2":{"f3":1},"f4":{"f5":99,"f6":[0,1,2]}},{"f4":"abc"}]'',''$[0].f4.f6[2]'')', '2', '=') into l_result; 
    select compat_tools.f_unit_test('json_extract(''[10, 20, [30, 40]]'', ''$[1]'', ''$[0]'')', '''[20,10]''', '=') into l_result; 
    -- =========================================================================
    end if;

end;
$VIEW_CREATION$ language plpgsql;


-- Show result & Exit
do $RESULT_SUMMARY$
declare
    l_app_name      text  := current_setting('application_name');
    l_failed_cnt    bigint;
begin
    set client_min_messages='notice';
    if l_app_name not in ('runMe', 'checkMe')
    then
        raise notice '';
        raise notice '-- =====================================================================';
        raise notice '-- Compat Object List: ';
        raise notice '-- =====================================================================';
        for l_app_name in select '   |' || pad_char
                              || rpad(coalesce(object_type, ' '), max_object_type, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(object_name, ' '), max_object_name, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(object_version, ' '), max_object_version, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(object_language, ' '), max_object_language, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(operation, ' '), max_operation, pad_char) || pad_char || '|' as result_data
                            from (select greatest(max(length(object_type)), 5) max_object_type
                                       , greatest(max(length(object_name)), 6) max_object_name
                                       , greatest(max(length(case when local_version = script_version then local_version else local_version || ' => ' || script_version end)), 7) max_object_version
                                       , greatest(max(length(case when local_language = script_language then local_language else local_language || ' => ' || script_language end)), 8) max_object_language
                                       , greatest(max(length(operation)), 9) max_operation
                                    from temp_result) l
                            join (select 'type' as object_type
                                       , 'name' as object_name
                                       , 'version' as object_version
                                       , 'language' as object_language
                                       , 'operation' as operation
                                       , ' ' as pad_char
                                   union all
                                  select '-' as object_type
                                       , '-' as object_name
                                       , '-' as object_version
                                       , '-' as object_language
                                       , '-' as operation
                                       , '-' as pad_char
                                   union all
                                  select object_type, object_name
                                       , case when local_version = script_version then local_version else local_version || ' => ' || script_version end as object_version
                                       , case when local_language = script_language then local_language else local_language || ' => ' || script_language end as object_language
                                       , operation, ' ' from temp_result) r on 1 = 1
        loop
            raise notice '%', l_app_name;
        end loop;

        raise notice '';
        raise notice '-- =====================================================================';
        raise notice '-- Test Summary: ';
        raise notice '-- =====================================================================';
        for l_app_name in select '   | result_type | case_count | start_time          | complete_time       |'
                           union all
                          select '   |-------------|------------|---------------------|---------------------|'
                           union all
                          select '   | '
                              || rpad((case when test_ok then 'PASSED' when not test_ok then 'FAILED' else 'NULL' end)::text, 11)
                              || ' | '
                              || lpad(count(*)::text, 10)
                              || ' | '
                              || to_char(min(test_timestamp), 'YYYY-MM-DD HH24:MI:SS')
                              || ' | '
                              || to_char(max(test_timestamp), 'YYYY-MM-DD HH24:MI:SS')
                              || ' |' as result_data
                            from compat_tools.compat_testing
                           group by case when test_ok then 'PASSED' when not test_ok then 'FAILED' else 'NULL' end
        loop
            raise notice '%', l_app_name;
        end loop;

        raise notice '';
        raise notice '-- =====================================================================';
        raise notice '-- Test Detail (Failed or Null): ';
        raise notice '-- =====================================================================';
        select count(*) into l_failed_cnt
          from compat_tools.compat_testing
         where test_ok is null or (not test_ok);
        if l_failed_cnt = 0
        then
            raise notice '-- <<< ALL SUCCEED >>>';
        else
            for l_app_name in select '   | test_expression                              | result          | expect          | complete_time       |'
                               union all
                              select '   |----------------------------------------------|-----------------|-----------------|---------------------|'
                               union all
                              select '   | '
                                  || case when length(test_expr) > 44 then substr(test_expr, 1, 40)||' ...' else rpad(test_expr, 44) end
                                  || ' | '
                                  || lpad(coalesce(test_result, ' '), 15)
                                  || ' | '
                                  || rpad(coalesce(expect_result, ' '), 15)
                                  || ' | '
                                  || to_char(test_timestamp, 'YYYY-MM-DD HH24:MI:SS')
                                  || ' |' as result_data
                                from compat_tools.compat_testing
                               where test_ok is null or (not test_ok)
            loop
                raise notice '%', l_app_name;
            end loop;
        end if;
    end if;
end;
$RESULT_SUMMARY$ language plpgsql;

reset behavior_compat_options;
\q