代码拉取完成，页面将自动刷新
-- This scripts contains following function's definition:
-- =============================================================================
-- NULL 相关函数
-- 注意： value 效果等同于通用函数 coalesce，在条件允许的情况下，建议修改 SQL 使用 coalesce 函数，该函数几乎在各个数据库中均支持
-- value(text,text)
-- value(numeric,numeric)
-- value(timestamp,timestamp)
-- value(timestamptz,timestamptz)
-- 字符函数
-- posstr(text,text)
-- locate_in_string(text,text,int4,int4,text)
-- regexp_match_count(text,text,int4,text,text)
-- locate(text,text,int4)
-- locate(text,text,int4,text)
-- 数字函数
-- 时间函数
-- year(text)
-- year(timestamptz)
-- year(interval)
-- month(text)
-- month(timestamptz)
-- month(interval)
-- quarter(timestamptz)
-- week(timestamptz)
-- day(text)
-- day(timestamptz)
-- day(interval)
-- hour(text)
-- hour(timestamptz)
-- hour(interval)
-- minute(text)
-- minute(timestamptz)
-- minute(interval)
-- second(text)
-- second(timestamptz)
-- second(interval)
-- days(timestamptz)
-- dayofyear(timestamptz)
-- dayofweek(timestamptz)
-- dayofweek_iso(timestamptz)
-- dayname(timestamptz)
-- monthname(timestamptz)
-- midnight_seconds(timestamptz)
-- next_day(timestamptz,text,text)
-- next_month(timestamptz)
-- next_quarter(timestamptz)
-- next_week(timestamptz)
-- next_year(timestamptz)
-- last_day(timestamptz)
-- first_day(timestamptz)
-- this_month(timestamptz)
-- this_quarter(timestamptz)
-- this_week(timestamptz)
-- this_year(timestamptz)
-- days_between(timestamptz,timestamptz)
-- years_between(timestamptz,timestamptz)
-- ymd_between(timestamptz,timestamptz)
-- 其他函数
-- todo
-- =============================================================================
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
-- =========================================================================
-- 注意： value 效果等同于 coalesce 函数，在条件允许的情况下，建议修改 SQL 使用 coalesce 函数，几乎在各个数据库中均支持
-- value(text,text)
-- value(numeric,numeric)
-- value(timestamp,timestamp)
-- value(timestamptz,timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'value(text,text)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.value(p1 text, p2 text)
RETURNS text IMMUTABLE AS $$
SELECT coalesce(p1, p2);
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'value(numeric,numeric)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.value(p1 numeric, p2 numeric)
RETURNS numeric IMMUTABLE AS $$
SELECT coalesce(p1, p2);
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'value(timestamp,timestamp)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.value(p1 timestamp, p2 timestamp)
RETURNS timestamp IMMUTABLE AS $$
SELECT coalesce(p1, p2);
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'value(timestamptz,timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.value(p1 timestamptz, p2 timestamptz)
RETURNS timestamptz IMMUTABLE AS $$
SELECT coalesce(p1, p2);
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('pg_typeof(value(123, 1111))', '''numeric''') into l_result;
select compat_tools.f_unit_test('value(123, 1111)', '123') into l_result;
select compat_tools.f_unit_test('value(null, 1111)', '1111') into l_result;
select compat_tools.f_unit_test('value(123, null)', '123') into l_result;
select compat_tools.f_unit_test('pg_typeof(value(''xyz'', ''abc''))', '''text''') into l_result;
select compat_tools.f_unit_test('value(''xyz'', ''abc'')', '''xyz''') into l_result;
select compat_tools.f_unit_test('value(null, ''abc'')', '''abc''') into l_result;
select compat_tools.f_unit_test('value(''xyz'', null)', '''xyz''') into l_result;
select compat_tools.f_unit_test('pg_typeof(value(''2012-12-12''::timestamp, ''2021-03-04''::timestamp))', '''timestamp without time zone''') into l_result;
select compat_tools.f_unit_test('value(''2012-12-12''::timestamp, ''2021-03-04''::timestamp)', '''2012-12-12 00:00:00''') into l_result;
select compat_tools.f_unit_test('value(null, ''2021-03-04''::timestamp)', '''2021-03-04 00:00:00''') into l_result;
select compat_tools.f_unit_test('value(''2012-12-12''::timestamp, null)', '''2012-12-12 00:00:00''') into l_result;
select compat_tools.f_unit_test('value(null, null)', 'NULL', 'IS') into l_result;
-- =========================================================================
-- =========================================================================
-- posstr(text,text)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'posstr(text,text)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.posstr(p_source text, p_str text)
RETURNS int IMMUTABLE AS $$
select instr(p_source, p_str);
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('posstr(''Hongye'', ''ong'')', '2') into l_result;
select compat_tools.f_unit_test('posstr(''Hongye'', ''H'')', '1') into l_result;
select compat_tools.f_unit_test('posstr(''Hongye'', ''Hxxx'')', '0') into l_result;
-- =========================================================================
-- =========================================================================
-- year(text)
-- year(timestamptz)
-- year(interval)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'year(text)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.year(p_text text)
RETURNS int IMMUTABLE strict AS $$
select extract('year' from p_text::timestamp)::int;
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'year(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.year(p_ts timestamptz)
RETURNS int IMMUTABLE strict AS $$
select extract('year' from p_ts)::int;
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'year(interval)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.year(p_interval interval)
RETURNS int IMMUTABLE strict AS $$
select extract('year' from p_interval)::int;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('year(''2012-12-12 12:12:12''::timestamp)', '2012') into l_result;
select compat_tools.f_unit_test('year(''2021-11-13 14:15:16''::timestamptz)', '2021') into l_result;
select compat_tools.f_unit_test('year(interval ''1 year 2 month'')', '1') into l_result;
-- =========================================================================
-- =========================================================================
-- month(text)
-- month(timestamptz)
-- month(interval)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'month(text)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.month(p_text text)
RETURNS int IMMUTABLE strict AS $$
select extract('month' from p_text::timestamp)::int;
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'month(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.month(p_ts timestamptz)
RETURNS int IMMUTABLE strict AS $$
select extract('month' from p_ts)::int;
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'month(interval)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.month(p_interval interval)
RETURNS int IMMUTABLE strict AS $$
select extract('month' from p_interval)::int;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('month(''2012-12-12 12:12:12''::timestamp)', '12') into l_result;
select compat_tools.f_unit_test('month(''2021-11-13 14:15:16''::timestamptz)', '11') into l_result;
select compat_tools.f_unit_test('month(interval ''1 year 2 month'')', '2') into l_result;
-- =========================================================================
-- =========================================================================
-- quarter(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'quarter(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.quarter(p_ts timestamptz)
RETURNS int IMMUTABLE strict AS $$
select extract('quarter' from p_ts)::int;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('quarter(''2012-12-12 12:12:12''::timestamp)', '4') into l_result;
select compat_tools.f_unit_test('quarter(''2021-01-13 14:15:16''::timestamptz)', '1') into l_result;
-- =========================================================================
-- =========================================================================
-- week(timestamptz)
-- =========================================================================
-- 计算思路：
-- 每年的第一周时间大概率不足7天，由于 openGauss 不存在从周日计数的年内周数，
-- 所以按照年内天数除以 7 计算周数，并补齐第一周的天数，
-- 依据当年一月一号是周几： 周一补一天 ... 周六补6天，周日不补
-- select extract('isodow' from trunc('1921-01-01'::timestamp)) -- 6
-- select extract('isodow' from trunc('2021-01-01'::timestamp)) -- 5
-- select extract('isodow' from trunc('1021-01-01'::timestamp)) -- 1
-- 1 6 +1
-- 2 5 +2
-- 3 4 +3
-- 4 3 +4
-- 5 2 +5
-- 6 1 +6
-- 7 0 +7
-- select ceil((extract('doy' from '2021-01-01'::timestamp) + 5)/7)
-- , ceil((extract('doy' from '2021-01-02'::timestamp) + 5)/7)
-- , ceil((extract('doy' from '2021-01-03'::timestamp) + 5)/7)
-- , ceil((extract('doy' from '2021-01-09'::timestamp) + 5)/7)
-- , ceil((extract('doy' from '2021-01-10'::timestamp) + 5)/7) from dual;
-- =========================================================================
if compat_tools.drop_compat_function('function', 'week(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.week(p_ts timestamptz)
RETURNS int IMMUTABLE strict AS $$
select case extract('isodow' from date_trunc('year', p_ts))
when 7 then ceil(extract('doy' from p_ts)/7)::int
else ceil((extract('doy' from p_ts) + extract('isodow' from date_trunc('year', p_ts)))/7)::int
end;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('week(''2021-01-01'')', '1') into l_result;
select compat_tools.f_unit_test('week(''2021-01-02'')', '1') into l_result;
select compat_tools.f_unit_test('week(''2021-01-03'')', '2') into l_result;
select compat_tools.f_unit_test('week(''2021-01-09'')', '2') into l_result;
select compat_tools.f_unit_test('week(''2021-01-10'')', '3') into l_result;
select compat_tools.f_unit_test('week(''1921-01-01'')', '1') into l_result;
select compat_tools.f_unit_test('week(''1921-01-02'')', '2') into l_result;
select compat_tools.f_unit_test('week(''1921-01-03'')', '2') into l_result;
select compat_tools.f_unit_test('week(''1921-01-09'')', '3') into l_result;
select compat_tools.f_unit_test('week(''1921-01-10'')', '3') into l_result;
select compat_tools.f_unit_test('week(''2121-01-01'')', '1') into l_result;
select compat_tools.f_unit_test('week(''2121-01-02'')', '1') into l_result;
select compat_tools.f_unit_test('week(''2121-01-03'')', '1') into l_result;
select compat_tools.f_unit_test('week(''2121-01-09'')', '2') into l_result;
select compat_tools.f_unit_test('week(''2121-01-10'')', '2') into l_result;
select compat_tools.f_unit_test('week(''1004-01-01'')', '1') into l_result;
select compat_tools.f_unit_test('week(''1004-01-07'')', '1') into l_result;
select compat_tools.f_unit_test('week(''1004-01-08'')', '2') into l_result;
select compat_tools.f_unit_test('week(''1004-01-09'')', '2') into l_result;
-- =========================================================================
-- =========================================================================
-- day(text)
-- day(timestamptz)
-- day(interval)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'day(text)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.day(p_text text)
RETURNS int IMMUTABLE strict AS $$
select extract('day' from p_text::timestamp)::int;
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'day(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.day(p_ts timestamptz)
RETURNS int IMMUTABLE strict AS $$
select extract('day' from p_ts)::int;
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'day(interval)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.day(p_interval interval)
RETURNS int IMMUTABLE strict AS $$
select extract('day' from p_interval)::int;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('day(''2021-01-01'')', '1') into l_result;
select compat_tools.f_unit_test('day(''2021-01-02'')', '2') into l_result;
select compat_tools.f_unit_test('day(''2021-01-03'')', '3') into l_result;
select compat_tools.f_unit_test('day(''2021-01-09'')', '9') into l_result;
select compat_tools.f_unit_test('day(''2021-01-10'')', '10') into l_result;
select compat_tools.f_unit_test('day(interval ''1 year 2 month'')', '0') into l_result;
select compat_tools.f_unit_test('day(interval ''1 day 2 minutes'')', '1') into l_result;
-- =========================================================================
-- =========================================================================
-- hour(text)
-- hour(timestamptz)
-- hour(interval)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'hour(text)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.hour(p_text text)
RETURNS int IMMUTABLE strict AS $$
select extract('hour' from p_text::timestamp)::int;
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'hour(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.hour(p_ts timestamptz)
RETURNS int IMMUTABLE strict AS $$
select extract('hour' from p_ts)::int;
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'hour(interval)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.hour(p_interval interval)
RETURNS int IMMUTABLE strict AS $$
select extract('hour' from p_interval)::int;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('hour(''2021-01-01 12:12:12'')', '12') into l_result;
select compat_tools.f_unit_test('hour(''2021-01-01 00:12:12'')', '0') into l_result;
select compat_tools.f_unit_test('hour(interval ''1 year 2 month'')', '0') into l_result;
select compat_tools.f_unit_test('hour(interval ''1 day 2 hour'')', '2') into l_result;
-- =========================================================================
-- =========================================================================
-- minute(text)
-- minute(timestamptz)
-- minute(interval)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'minute(text)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.minute(p_text text)
RETURNS int IMMUTABLE strict AS $$
select extract('minute' from p_text::timestamp)::int;
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'minute(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.minute(p_ts timestamptz)
RETURNS int IMMUTABLE strict AS $$
select extract('minute' from p_ts)::int;
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'minute(interval)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.minute(p_interval interval)
RETURNS int IMMUTABLE strict AS $$
select extract('minute' from p_interval)::int;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('minute(''2021-01-01 12:12:12'')', '12') into l_result;
select compat_tools.f_unit_test('minute(''2021-01-01 00:00:12'')', '0') into l_result;
select compat_tools.f_unit_test('minute(interval ''1 year 2 month'')', '0') into l_result;
select compat_tools.f_unit_test('minute(interval ''1 day 2 minute'')', '2') into l_result;
-- =========================================================================
-- =========================================================================
-- second(text)
-- second(timestamptz)
-- second(interval)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'second(text)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.second(p_text text)
RETURNS int IMMUTABLE strict AS $$
select extract('second' from p_text::timestamp)::int;
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'second(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.second(p_ts timestamptz)
RETURNS int IMMUTABLE strict AS $$
select extract('second' from p_ts)::int;
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'second(interval)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.second(p_interval interval)
RETURNS int IMMUTABLE strict AS $$
select extract('second' from p_interval)::int;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('second(''2021-01-01 12:12:12'')', '12') into l_result;
select compat_tools.f_unit_test('second(''2021-01-01 00:00:00'')', '0') into l_result;
select compat_tools.f_unit_test('second(interval ''1 year 2 month'')', '0') into l_result;
select compat_tools.f_unit_test('second(interval ''1 day 2 second'')', '2') into l_result;
-- =========================================================================
-- =========================================================================
-- days(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'days(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.days(p_ts timestamptz)
RETURNS int IMMUTABLE strict AS $$
select extract('days' from p_ts - '0001-01-01'::timestamp)::int + 1;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('days(''0001-01-01'')', '1') into l_result;
select compat_tools.f_unit_test('days(''2021-01-01'')', '737791') into l_result;
select compat_tools.f_unit_test('days(''1021-12-30'')', '372911') into l_result;
-- =========================================================================
-- =========================================================================
-- dayofyear(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'dayofyear(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.dayofyear(p_ts timestamptz)
RETURNS int IMMUTABLE strict AS $$
select extract('doy' from p_ts)::int;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('dayofyear(''0001-01-01'')', '1') into l_result;
select compat_tools.f_unit_test('dayofyear(''2021-01-01'')', '1') into l_result;
select compat_tools.f_unit_test('dayofyear(''1021-12-30'')', '364') into l_result;
-- =========================================================================
-- =========================================================================
-- dayofweek(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'dayofweek(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.dayofweek(p_ts timestamptz)
RETURNS int IMMUTABLE strict AS $$
select extract('dow' from p_ts)::int + 1;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('dayofweek(''2021-01-01'')', '6') into l_result;
select compat_tools.f_unit_test('dayofweek(''2021-01-02'')', '7') into l_result;
select compat_tools.f_unit_test('dayofweek(''2021-01-03'')', '1') into l_result;
select compat_tools.f_unit_test('dayofweek(''2021-01-09'')', '7') into l_result;
select compat_tools.f_unit_test('dayofweek(''2021-01-10'')', '1') into l_result;
-- =========================================================================
-- =========================================================================
-- dayofweek_iso(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'dayofweek_iso(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.dayofweek_iso(p_ts timestamptz)
RETURNS int IMMUTABLE strict AS $$
select extract('isodow' from p_ts)::int;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('dayofweek_iso(''2021-01-01'')', '5') into l_result;
select compat_tools.f_unit_test('dayofweek_iso(''2021-01-02'')', '6') into l_result;
select compat_tools.f_unit_test('dayofweek_iso(''2021-01-03'')', '7') into l_result;
select compat_tools.f_unit_test('dayofweek_iso(''2021-01-09'')', '6') into l_result;
select compat_tools.f_unit_test('dayofweek_iso(''2021-01-10'')', '7') into l_result;
-- =========================================================================
-- =========================================================================
-- dayname(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'dayname(timestamptz)', '2.0')
then
CREATE or replace FUNCTION pg_catalog.dayname(p_ts timestamptz)
RETURNS text IMMUTABLE strict AS $$
select rtrim(to_char(p_ts, 'Day'));
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('dayname(''2021-01-01'')', '''Friday''') into l_result;
select compat_tools.f_unit_test('dayname(''2021-01-02'')', '''Saturday''') into l_result;
select compat_tools.f_unit_test('dayname(''2021-01-03'')', '''Sunday''') into l_result;
select compat_tools.f_unit_test('dayname(''2021-01-09'')', '''Saturday''') into l_result;
select compat_tools.f_unit_test('dayname(''2021-01-10'')', '''Sunday''') into l_result;
-- =========================================================================
-- =========================================================================
-- monthname(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'monthname(timestamptz)', '2.0')
then
CREATE or replace FUNCTION pg_catalog.monthname(p_ts timestamptz)
RETURNS text IMMUTABLE strict AS $$
select rtrim(to_char(p_ts, 'Month'));
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('monthname(''2021-01-01'')', '''January''') into l_result;
select compat_tools.f_unit_test('monthname(''2021-12-02'')', '''December''') into l_result;
-- =========================================================================
-- =========================================================================
-- midnight_seconds(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'midnight_seconds(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.midnight_seconds(p_ts timestamptz)
RETURNS int IMMUTABLE strict AS $$
select extract('epoch' from p_ts)::int - extract('epoch' from trunc(p_ts))::int;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('midnight_seconds(''2021-01-01 12:12:12'')', '43932') into l_result;
select compat_tools.f_unit_test('midnight_seconds(''2021-12-02 00:00:00'')', '0') into l_result;
select compat_tools.f_unit_test('midnight_seconds(''2021-12-02 23:59:59'')', '86399') into l_result;
-- =========================================================================
-- =========================================================================
-- next_day(timestamptz,text,text)
-- 实现思路：
-- 当日星期 目标星期 加天数
-- 1 1 +7
-- 1 2 +1
-- 1 3 +2
-- 1 4 +3
-- 1 5 +4
-- 1 6 +5
-- 1 7 +6
-- 2 1 +6
-- 2 2 +7
-- 2 3 +1
-- 2 4 +2
-- 2 5 +3
-- 2 6 +4
-- 2 7 +5
-- 3 1 +5
-- 3 2 +6
-- 3 3 +7
-- 3 4 +1
-- 3 5 +2
-- 3 6 +3
-- 3 7 +4
-- 加天数计算公式： 目标星期 - 当日星期 (逢 0 变 7 )
-- =========================================================================
if compat_tools.drop_compat_function('function', 'next_day(timestamptz,text,text)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.next_day( p_ts timestamptz
, p_weekday text
, p_locale text)
RETURNS timestamptz IMMUTABLE strict AS $$
select p_ts + interval '1 day' * (case when week_target = week_day then 7 else week_target - week_day end)
from (select case lower(p_weekday)
when 'monday' then 1 when 'mon' then 1
when 'tuesday' then 1 when 'tue' then 1
when 'wednesday' then 1 when 'wed' then 1
when 'thursday' then 1 when 'thu' then 1
when 'friday' then 1 when 'fri' then 1
when 'saturday' then 1 when 'sat' then 1
else 7 end as week_target
, extract('isodow' from p_ts) as week_day) as t;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('next_day(''2021-01-01 12:12:12'', ''monday'')', '''2021-01-04 12:12:12''') into l_result;
select compat_tools.f_unit_test('next_day(''2021-03-02'', ''tue'')', '''2021-03-09 00:00:00''') into l_result;
select compat_tools.f_unit_test('next_day(''2021-12-02 23:59:59'', ''sun'')', '''2021-12-05 23:59:59''') into l_result;
-- =========================================================================
-- =========================================================================
-- next_month(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'next_month(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.next_month(p_ts timestamptz)
RETURNS timestamptz IMMUTABLE strict AS $$
select date_trunc('month', p_ts) + interval '1 month';
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('pg_typeof(next_month(''2021-02-28''))', '''timestamp with time zone''') into l_result;
select compat_tools.f_unit_test('next_month(''2021-01-01 12:12:12'')::timestamp', '''2021-02-01 00:00:00''') into l_result;
select compat_tools.f_unit_test('next_month(''2021-02-28'')::timestamp', '''2021-03-01 00:00:00''') into l_result;
select compat_tools.f_unit_test('next_month(''2021-12-02 23:59:59'')::timestamp', '''2022-01-01 00:00:00''') into l_result;
-- =========================================================================
-- =========================================================================
-- next_quarter(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'next_quarter(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.next_quarter(p_ts timestamptz)
RETURNS timestamptz IMMUTABLE strict AS $$
select date_trunc('quarter', p_ts) + interval '3 month';
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('pg_typeof(next_quarter(''2021-02-28''))', '''timestamp with time zone''') into l_result;
select compat_tools.f_unit_test('next_quarter(''2021-01-01 12:12:12'')::timestamp', '''2021-04-01 00:00:00''') into l_result;
select compat_tools.f_unit_test('next_quarter(''2021-02-28'')::timestamp', '''2021-04-01 00:00:00''') into l_result;
select compat_tools.f_unit_test('next_quarter(''2021-12-02 23:59:59'')::timestamp', '''2022-01-01 00:00:00''') into l_result;
-- =========================================================================
-- =========================================================================
-- next_week(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'next_week(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.next_week(p_ts timestamptz)
RETURNS timestamptz IMMUTABLE strict AS $$
select date_trunc('day', p_ts) + interval '1 day' * (7 - extract('dow' from p_ts));
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('pg_typeof(next_week(''2021-02-28''))', '''timestamp with time zone''') into l_result;
select compat_tools.f_unit_test('next_week(''2021-01-03 12:12:12'')::timestamp', '''2021-01-10 00:00:00''') into l_result;
select compat_tools.f_unit_test('next_week(''2021-02-28'')::timestamp', '''2021-03-07 00:00:00''') into l_result;
select compat_tools.f_unit_test('next_week(''2021-12-02 23:59:59'')::timestamp', '''2021-12-05 00:00:00''') into l_result;
-- =========================================================================
-- =========================================================================
-- next_year(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'next_year(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.next_year(p_ts timestamptz)
RETURNS timestamptz IMMUTABLE strict AS $$
select date_trunc('year', p_ts) + interval '1 year';
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('pg_typeof(next_year(''2021-02-28''))', '''timestamp with time zone''') into l_result;
select compat_tools.f_unit_test('next_year(''0001-02-28'')::timestamp', '''0002-01-01 00:00:00''') into l_result;
select compat_tools.f_unit_test('next_year(''2021-12-02 23:59:59'')::timestamp', '''2022-01-01 00:00:00''') into l_result;
-- =========================================================================
-- =========================================================================
-- locate_in_string(text,text,int4,int4,text)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'locate_in_string(text,text,int4,int4,text)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.locate_in_string( p_source text
, p_search text
, p_start int4 default 1
, p_instance int4 default 1
, p_charset text default 'x')
RETURNS int IMMUTABLE strict AS $$
select instr(p_source, p_search, p_start, p_instance);
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('locate_in_string(''hongye'', ''h'')', '1') into l_result;
select compat_tools.f_unit_test('locate_in_string(''hongye'', ''ye'')', '5') into l_result;
select compat_tools.f_unit_test('locate_in_string(''hongye'', ''xx'')', '0') into l_result;
select compat_tools.f_unit_test('locate_in_string(null, ''xx'')', 'NULL', 'IS') into l_result;
select compat_tools.f_unit_test('locate_in_string(''hongye'', null)', 'NULL', 'IS') into l_result;
-- =========================================================================
-- =========================================================================
-- last_day(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'last_day(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.last_day(p_tstz timestamptz)
RETURNS timestamptz IMMUTABLE strict AS $$
select p_tstz + interval '1 day' * (extract(day from date_trunc('month', p_tstz) + interval '1 month -1 day') - extract(day from p_tstz));
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('pg_typeof(last_day(''2021-02-28''))', '''timestamp with time zone''') into l_result;
select compat_tools.f_unit_test('last_day(''2012-12-12 12:12:12.121212+01:23''::timestamptz)::timestamp', '''2012-12-31 10:49:12.121212''') into l_result;
select compat_tools.f_unit_test('last_day(''2021-01-01''::timestamp)::timestamp', '''2021-01-31 00:00:00''') into l_result;
-- =========================================================================
-- =========================================================================
-- first_day(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'first_day(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.first_day(p_tstz timestamptz)
RETURNS timestamptz IMMUTABLE strict AS $$
select p_tstz - interval '1 day' * (extract(day from p_tstz) - 1);
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('pg_typeof(first_day(''2021-02-28''))', '''timestamp with time zone''') into l_result;
select compat_tools.f_unit_test('first_day(''2012-12-12 12:12:12.121212+01:23''::timestamptz)::timestamp', '''2012-12-01 10:49:12.121212''') into l_result;
select compat_tools.f_unit_test('first_day(''2021-01-01''::timestamp)::timestamp', '''2021-01-01 00:00:00''') into l_result;
-- =========================================================================
-- =========================================================================
-- regexp_match_count(text,text,int4,text,text)
-- 注意： p_flag 只支持 Oracle 中的 i 和 c 模式，分别表示：
-- i = 大小写不敏感
-- c = 大小写敏感，默认模式
-- =========================================================================
if compat_tools.drop_compat_function('function', 'regexp_match_count(text,text,int4,text,text)', '1.0', 'sql')
then
CREATE or replace FUNCTION pg_catalog.regexp_match_count ( p_source text
, p_pattern text
, p_start int default 1
, p_flag text default 'c'
, p_charset text default 'x')
RETURNS int IMMUTABLE strict AS $$
select length(pg_catalog.regexp_replace(source_str, p_pattern, '#\&', case lower(p_flag) when 'i' then 'gi' else 'g' end))
- length(source_str)
from (select substr(p_source, p_start) as source_str) as x;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('regexp_match_count(''abcdA123'', ''a.'')', '1') into l_result;
select compat_tools.f_unit_test('regexp_match_count(''abcdA123'', ''a.'', 1, ''i'')', '2') into l_result;
select compat_tools.f_unit_test('regexp_match_count(''abcdA123'', ''a.'', 3)', '0') into l_result;
select compat_tools.f_unit_test('regexp_match_count(''abcdA123'', ''a.'', 3, ''i'')', '1') into l_result;
-- =========================================================================
-- =========================================================================
-- this_month(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'this_month(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.this_month(p_ts timestamptz)
RETURNS timestamptz IMMUTABLE strict AS $$
select date_trunc('month', p_ts);
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('pg_typeof(this_month(''2021-02-28''))', '''timestamp with time zone''') into l_result;
select compat_tools.f_unit_test('this_month(''2021-01-01 12:12:12'')::timestamp', '''2021-01-01 00:00:00''') into l_result;
select compat_tools.f_unit_test('this_month(''2021-02-28'')::timestamp', '''2021-02-01 00:00:00''') into l_result;
select compat_tools.f_unit_test('this_month(''2021-12-02 23:59:59'')::timestamp', '''2021-12-01 00:00:00''') into l_result;
-- =========================================================================
-- =========================================================================
-- this_quarter(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'this_quarter(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.this_quarter(p_ts timestamptz)
RETURNS timestamptz IMMUTABLE strict AS $$
select date_trunc('quarter', p_ts);
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('pg_typeof(this_quarter(''2021-02-28''))', '''timestamp with time zone''') into l_result;
select compat_tools.f_unit_test('this_quarter(''2021-01-01 12:12:12'')::timestamp', '''2021-01-01 00:00:00''') into l_result;
select compat_tools.f_unit_test('this_quarter(''2021-02-28'')::timestamp', '''2021-01-01 00:00:00''') into l_result;
select compat_tools.f_unit_test('this_quarter(''2021-12-02 23:59:59'')::timestamp', '''2021-10-01 00:00:00''') into l_result;
-- =========================================================================
-- =========================================================================
-- this_week(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'this_week(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.this_week(p_ts timestamptz)
RETURNS timestamptz IMMUTABLE strict AS $$
select date_trunc('day', p_ts) - interval '1 day' * extract('dow' from p_ts);
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('pg_typeof(this_week(''2021-02-28''))', '''timestamp with time zone''') into l_result;
select compat_tools.f_unit_test('this_week(''2021-01-05 12:12:12'')::timestamp', '''2021-01-03 00:00:00''') into l_result;
select compat_tools.f_unit_test('this_week(''2021-02-28'')::timestamp', '''2021-02-28 00:00:00''') into l_result;
select compat_tools.f_unit_test('this_week(''2021-12-02 23:59:59'')::timestamp', '''2021-11-28 00:00:00''') into l_result;
-- =========================================================================
-- =========================================================================
-- this_year(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'this_year(timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.this_year(p_ts timestamptz)
RETURNS timestamptz IMMUTABLE strict AS $$
select date_trunc('year', p_ts);
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('pg_typeof(this_year(''2021-02-28''))', '''timestamp with time zone''') into l_result;
select compat_tools.f_unit_test('this_year(''0001-02-28'')::timestamp', '''0001-01-01 00:00:00''') into l_result;
select compat_tools.f_unit_test('this_year(''2021-12-02 23:59:59'')::timestamp', '''2021-01-01 00:00:00''') into l_result;
-- =========================================================================
-- =========================================================================
-- days_between(timestamptz,timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'days_between(timestamptz,timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.days_between( p_ts1 timestamptz
, p_ts2 timestamptz)
RETURNS int8 IMMUTABLE strict AS $$
select extract('day' from p_ts1 - p_ts2)::bigint;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('days_between(''2004-03-31 12:12:12'', ''2004-02-22'')', '38') into l_result;
select compat_tools.f_unit_test('days_between(''1014-12-01 12:12:12'', ''2004-03-31'')', '-361345') into l_result;
select compat_tools.f_unit_test('days_between(''2014-12-31 12:12:12'', ''2004-03-31'')', '3927') into l_result;
-- =========================================================================
-- =========================================================================
-- years_between(timestamptz,timestamptz)
-- 测试用例：
-- =========================================================================
if compat_tools.drop_compat_function('function', 'years_between(timestamptz,timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.years_between( p_ts1 timestamptz
, p_ts2 timestamptz)
RETURNS int8 IMMUTABLE strict AS $$
select case when p_ts1 < p_ts2 and to_char(p_ts1, 'MMDDHH24MISSFF') > to_char(p_ts2, 'MMDDHH24MISSFF')
then extract('year' from p_ts1)::bigint - extract('year' from p_ts2)::bigint + 1
else extract('year' from p_ts1)::bigint - extract('year' from p_ts2)::bigint
end;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('years_between(''2004-01-01 12:12:12'', ''2004-12-31'')', '0') into l_result;
select compat_tools.f_unit_test('years_between(''1004-01-01 12:12:12'', ''2004-12-31'')', '-1000') into l_result;
select compat_tools.f_unit_test('years_between(''2014-12-31 12:12:12'', ''2004-01-01'')', '10') into l_result;
select compat_tools.f_unit_test('years_between(''1004-12-31 12:12:12'', ''2004-01-01'')', '-999') into l_result;
select compat_tools.f_unit_test('years_between(''1004-12-31 12:12:12'', ''2004-12-31 12:12:11'')', '-999') into l_result;
select compat_tools.f_unit_test('years_between(''1004-12-31 12:12:12'', ''2004-12-31 12:12:12'')', '-1000') into l_result;
-- =========================================================================
-- =========================================================================
-- ymd_between(timestamptz,timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'ymd_between(timestamptz,timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.ymd_between( p_ts1 timestamptz
, p_ts2 timestamptz)
RETURNS numeric IMMUTABLE strict AS $$
select case when p_ts1 < p_ts2 and to_char(p_ts1, 'MMDDHH24MISSFF') > to_char(p_ts2, 'MMDDHH24MISSFF')
then trunc((to_char(date_trunc('day', p_ts1) + interval '1 day', 'YYYYMMDDHH24MISS.FF')::numeric(21,6) - to_char(p_ts2, 'YYYYMMDDHH24MISS.FF')::numeric(21,6))/1000000)
else trunc((to_char(p_ts1, 'YYYYMMDDHH24MISS.FF')::numeric(21,6) - to_char(p_ts2, 'YYYYMMDDHH24MISS.FF')::numeric(21,6))/1000000)
end;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('ymd_between(''2004-01-01 12:12:12'', ''2004-12-31'')', '-1129') into l_result;
select compat_tools.f_unit_test('ymd_between(''1004-01-01 12:12:12'', ''2004-12-31'')', '-10001129') into l_result;
select compat_tools.f_unit_test('ymd_between(''2014-12-31 12:12:12'', ''2004-01-01'')', '101130') into l_result;
select compat_tools.f_unit_test('ymd_between(''1004-12-31 12:12:12'', ''2004-01-01'')', '-9990000') into l_result;
select compat_tools.f_unit_test('ymd_between(''1004-12-31 12:12:12'', ''2004-12-31 12:12:11'')', '-9991130') into l_result;
select compat_tools.f_unit_test('ymd_between(''1004-12-31 12:12:12'', ''2004-12-31 12:12:12'')', '-10000000') into l_result;
-- =========================================================================
-- =========================================================================
-- digits(int2)
-- digits(int4)
-- digits(int8)
-- digits(text)
-- digits(numeric)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'digits(int2)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.digits(int2)
RETURNS text IMMUTABLE strict AS $$
select lpad(abs($1)::text, 5, '0');
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'digits(int4)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.digits(int4)
RETURNS text IMMUTABLE strict AS $$
select lpad(abs($1)::text, 10, '0');
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'digits(int8)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.digits(int8)
RETURNS text IMMUTABLE strict AS $$
select lpad(abs($1)::text, 19, '0');
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'digits(text)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.digits(text)
RETURNS text IMMUTABLE strict AS $$
select lpad(floor(abs($1::numeric) * 1000000)::text, 31, '0');
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'digits(numeric)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.digits(numeric)
RETURNS text IMMUTABLE strict AS $$
select replace(abs($1)::text, '.', '');
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
-- select digits(cast(123.12345678 as decimal(20,10))) from dual; -- 00000001231234567800
select compat_tools.f_unit_test('digits(''123'')', '''0000000000000000000000123000000''') into l_result;
select compat_tools.f_unit_test('digits(''123.1234567'')', '''0000000000000000000000123123456''','=','A') into l_result;
select compat_tools.f_unit_test('digits(''-123.1234567'')', '''0000000000000000000000123123456''','=','A') into l_result;
select compat_tools.f_unit_test('digits(123.12345678)', '''12312345678''') into l_result;
select compat_tools.f_unit_test('digits(-123.12345678)', '''12312345678''') into l_result;
select compat_tools.f_unit_test('digits(cast(-123 as smallint))', '''00123''') into l_result;
select compat_tools.f_unit_test('digits(cast(123 as smallint))', '''00123''') into l_result;
select compat_tools.f_unit_test('digits(cast(123 as int))', '''0000000123''') into l_result;
select compat_tools.f_unit_test('digits(cast(123 as bigint))', '''0000000000000000123''') into l_result;
-- =========================================================================
-- =========================================================================
-- 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;
-- =========================================================================
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
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。