代码拉取完成，页面将自动刷新
-- This scripts contains following function's definition:
-- =============================================================================
-- months_between(timestamptz,timestamptz)
-- sys_guid()
-- wm_concat(text)
-- nullif(anyelement,anyelement)
-- nvl2(anyelement,anyelement,anyelement)
-- nvl2(text,text,text)
-- nvl2(numeric,numeric,numeric)
-- nvl2(timestamptz,timestamptz,timestamptz)
-- trunc(timestamp,text)
-- trunc(timestamptz,text)
-- nanvl(numeric,numeric)
-- regexp_substr(text,text,int8)
-- regexp_substr(text,text,int8,int8,text,int8)
-- regexp_instr(text,text,int8,int8,int8,text,int8)
-- regexp_replace(text,text,text,int8,int8,text)
-- regexp_count(text,text,int8,text)
-- regexp_count(text,text,int8)
-- regexp_count(text,text)
-- replace(text,text)
-- ora_hash(anyelement,int8,int8)
-- show(text)
-- show_parameter(text)
-- dump(anyelement,numeric,int8,int8)
-- instrb(text,text,int8,int8)
-- vsize(anyelement)
-- tz_offset(text)
-- sys_context(text,text,int8)
-- userenv(text)
-- round(timestamptz,text)
-- bitor(int8,int8)
-- bitxor(int8,int8)
-- bit_and_agg(numeric)
-- bit_or_agg(numeric)
-- bit_xor_agg(numeric)
-- bin_to_num(int4[])
-- any_value(anyelement)
-- sinh(numeric)
-- cosh(numeric)
-- tanh(numeric)
-- lnnvl(bool)
-- numtoyminterval(numeric,text)
-- systimestamp()
-- remainder(numeric,numeric)
-- round_ties_to_even(numeric,int8)
-- soundex(text)
-- kurtosis_pop(numeric)
-- kurtosis_samp(numeric)
-- skewness_pop(numeric)
-- skewness_samp(numeric)
-- asciistr(text)
-- unistr(text)
-- to_blob(raw)
-- convert(text,text,text)
-- nls_charset_name(int8)
-- nls_charset_id(text)
-- empty_clob()
-- sys_extract_utc(timestamptz)
-- sys_extract_utc(timestamp)
-- new_time(timestamp,text,text)
-- lower(anyelement)
-- pg_catalog.substr(anyelement,int8)
-- pg_catalog.substr(anyelement,int8,int8)
-- add_months(timestamp,int8)
-- add_months(timestamptz,int8)
-- to_timestamp(text,text,text)
-- rawtohex(anyelement)
-- to_char(timestamp without time zone,text,text)
-- to_char(timestamptz,text,text)
-- to_lob(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';
-- =============================================================================
-- check extension conflict function
-- =============================================================================
CREATE OR REPLACE FUNCTION compat_tools.pg_describe_object2(oid, oid, INTEGER) returns text
LANGUAGE plpgsql NOT FENCED NOT SHIPPABLE AS
$$
DECLARE l_class_table text;
l_object_name text;
l_name_col text;
l_schema_col text;
l_schema_name text;
BEGIN
SELECT relname, a.attname
INTO l_class_table, l_name_col
FROM pg_class c, pg_attribute a
WHERE c.oid = a.attrelid
AND relname NOT IN ('pg_attrdef', 'pg_cast', 'pg_rewrite')
AND a.attnum = 1
AND c.oid = $1 ;
SELECT a.attname
INTO l_schema_col
FROM pg_class c, pg_attribute a
WHERE c.oid = a.attrelid
AND relname NOT IN ('pg_attrdef', 'pg_cast', 'pg_rewrite')
AND a.attnum = 2
AND c.oid = $1
AND attname NOT LIKE '%owner';
EXECUTE 'select ' || l_name_col || ' from ' || l_class_table || ' where oid = :1'
INTO l_object_name
USING $2;
EXECUTE 'select pn.nspname from pg_namespace pn,' || l_class_table || ' ct where pn.oid=ct.' || l_schema_col || ' and ct.oid=:1'
INTO l_schema_name
USING $2;
if l_class_table='pg_proc' then
select function_name into l_object_name from compat_tools.pg_function_list where oid=$2;
end if;
RETURN l_schema_name || '.' || l_object_name;
exception when others then return '';
END;
$$;
create or replace FUNCTION compat_tools.check_extension_conflict(text) RETURNS bool
LANGUAGE plpgsql NOT FENCED NOT SHIPPABLE AS
$$
DECLARE
l_exists int4;
BEGIN
SELECT COUNT(1)
INTO l_exists
FROM pg_depend t, pg_extension e
WHERE t.refobjid = e.oid
AND lower(compat_tools.pg_describe_object2(classid, objid, objsubid)) = lower($1);
IF l_exists > 0 THEN
RETURN true;
ELSE
RETURN false;
END IF;
END;
$$;
-- =============================================================================
-- 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 compat_tools.check_extension_conflict(p_object_schema||'.'||p_object_name) -- 系统中已有extension创建同名对象
then
l_result='false';
l_operation= 'Skip due to extension';
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;
l_db_encoding text;
begin
-- 获取当前DB的字符集编码
select pg_encoding_to_char(encoding) into l_db_encoding from pg_database where datname =current_database();
-- =========================================================================
-- months_between(timestamptz,timestamptz)
-- 2.1 版本修改返回类型 float8 -> numeric
-- =========================================================================
if compat_tools.drop_compat_function('function', 'months_between(timestamptz,timestamptz)', '2.1')
then
CREATE or replace FUNCTION pg_catalog.months_between( p_ts1 timestamptz
, p_ts2 timestamptz)
RETURNS numeric IMMUTABLE strict AS $$
-- SELECT (extract(year from age(d1,d2))*12 + extract(month from age(d1,d2)))::integer
select (case when dd1 = dd2 or (m1 = dd1 and m2 = dd2)
then yd * 12 + mmd
else yd * 12 + mmd
+ (dd1 - dd2) / 31
+ hd / 31 / 24
+ md / 31 / 24 / 60
+ sd / 31 / 24 / 60 / 60
end)::numeric
from (select extract('day' from date_trunc('month', p_ts1) + interval '1 month -1 day') as m1
, extract('day' from date_trunc('month', p_ts2) + interval '1 month -1 day') as m2
, extract('year' from p_ts1) - extract('year' from p_ts2) as yd
, extract('month' from p_ts1) - extract('month' from p_ts2) as mmd
, extract('day' from p_ts1) as dd1
, extract('day' from p_ts2) as dd2
, extract('hour' from p_ts1) - extract('hour' from p_ts2) as hd
, extract('minute' from p_ts1) - extract('minute' from p_ts2) as md
, extract('second' from p_ts1) - extract('second' from p_ts2) as sd
) as x;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('months_between(''2004-03-31 12:12:12'', ''2004-02-29'')', '1') into l_result;
select compat_tools.f_unit_test('round(months_between(''2004-03-30 12:12:12'', ''2004-02-29''),14)', '1.04866039426523') into l_result;
select compat_tools.f_unit_test('months_between(''2004-03-29 12:12:12'', ''2004-02-29'')', '1') into l_result;
select compat_tools.f_unit_test('round(months_between(''2004-06-01 12:12:12'', ''2004-04-30''),14)', '1.08091845878136') into l_result;
select compat_tools.f_unit_test('round(months_between(''2004-05-01 12:12:12'', ''2004-03-31''),14)', '1.04866039426523') into l_result;
select compat_tools.f_unit_test('round(months_between(''2014-12-01 12:12:12'', ''2004-03-31''),12)', '128.048660394265') into l_result;
select compat_tools.f_unit_test('months_between(''2014-12-31 12:12:12'', ''2004-03-31'')', '129') into l_result;
-- =========================================================================
-- =========================================================================
-- sys_guid()
-- =========================================================================
if compat_tools.drop_compat_function('function', 'sys_guid()', '1.0')
then
CREATE or replace FUNCTION pg_catalog.sys_guid()
RETURNS varchar
AS $$
select upper(md5(random()::text || clock_timestamp()::text));
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('sys_guid()', 'NOT NULL', 'IS','A') into l_result;
-- =========================================================================
-- =========================================================================
-- wm_concat(text)
-- =========================================================================
-- 变更历史：
-- 2022-06-08 1.1 版本号增加，以便能创建 wmsys.wm_concat 同义词
-- =========================================================================
if compat_tools.drop_compat_function('aggregate', 'wm_concat(text)', '1.1', 'internal')
then
CREATE OR REPLACE FUNCTION pg_catalog.vm_concat_state_func (results text, val text)
RETURNS text
LANGUAGE sql COST 50 IMMUTABLE
AS $$ select results || ',' ||val; $$;
CREATE OR REPLACE FUNCTION pg_catalog.vm_concat_final_func (results text)
RETURNS text
LANGUAGE sql COST 111 IMMUTABLE
AS $$ select substr(results, 2); $$;
CREATE AGGREGATE pg_catalog.wm_concat(text)
(
sfunc = pg_catalog.vm_concat_state_func,
stype = text,
initcond = '',
FINALFUNC = pg_catalog.vm_concat_final_func
);
-- 创建同义词 wmsys.wm_concat, 以便兼容 Oracle 中对应的用法
select count(*) into l_cnt
from pg_namespace
where nspname = 'wmsys';
if l_cnt = 0
then
create schema wmsys;
end if;
create or replace synonym wmsys.wm_concat for pg_catalog.wm_concat;
end if;
-- =========================================================================
-- nullif(anyelement,anyelement)
-- =========================================================================
-- 备注： Oracle 本身就有，无需支持
-- if compat_tools.drop_compat_function('function', 'nullif(anyelement,anyelement)', '1.0')
-- then
-- CREATE or replace FUNCTION pg_catalog.nullif(p_elem1 anyelement, p_elem2 anyelement)
-- RETURNS anyelement
-- AS $$
-- select case when p_elem1 = p_elem2 then null else p_elem1 end;
-- $$ LANGUAGE sql;
-- end if;
-- =========================================================================
-- nvl2(anyelement,anyelement,anyelement)
-- nvl2(text,text,text)
-- nvl2(numeric,numeric,numeric)
-- nvl2(timestamptz,timestamptz,timestamptz)
-- =========================================================================
--if exists nvl2("any", anyelement, anyelement) and nvl2("any", text, text) then skip
select count(1) into l_cnt from pg_proc where proname ='nvl2'
and '"any"'::regtype = any(case when proallargtypes is null
then proargtypes else proallargtypes end);
if l_cnt<2 then
if compat_tools.drop_compat_function('function', 'nvl2(anyelement,anyelement,anyelement)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.nvl2(p_elem anyelement, p_elem_not_null anyelement, p_elem_null anyelement)
RETURNS anyelement
IMMUTABLE
AS $$
select case when p_elem is null then p_elem_null else p_elem_not_null end;
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'nvl2(text,text,text)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.nvl2(p_elem text, p_elem_not_null text, p_elem_null text)
RETURNS text
IMMUTABLE
AS $$
select case when p_elem is null then p_elem_null else p_elem_not_null end;
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'nvl2(numeric,numeric,numeric)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.nvl2(p_elem numeric, p_elem_not_null numeric, p_elem_null numeric)
RETURNS numeric
IMMUTABLE
AS $$
select case when p_elem is null then p_elem_null else p_elem_not_null end;
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'nvl2(timestamptz,timestamptz,timestamptz)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.nvl2(p_elem timestamptz, p_elem_not_null timestamptz, p_elem_null timestamptz)
RETURNS timestamptz
IMMUTABLE
AS $$
select case when p_elem is null then p_elem_null else p_elem_not_null end;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('nvl2(1, 2, 3)', '2') into l_result;
select compat_tools.f_unit_test('nvl2(null, 2, 3)', '3') into l_result;
select compat_tools.f_unit_test('nvl2(1, null, 3)', 'NULL', 'IS') into l_result;
select compat_tools.f_unit_test('nvl2(''asda''::text, ''x'', ''y'')', '''x''') into l_result;
-- =========================================================================
end if;
-- =========================================================================
-- trunc(timestamp,text)
-- trunc(timestamptz,text)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'trunc(timestamp,text)', '1.1')
then
CREATE or replace FUNCTION pg_catalog.trunc(p_date timestamp, p_fmt text)
RETURNS timestamp without time zone
IMMUTABLE
AS $$
select (case upper(p_fmt)
-- when '' then 'microseconds'
-- when '' then 'milliseconds'
when 'SS' then date_trunc('second', p_date)
WHEN 'MI' then date_trunc('minute', p_date)
WHEN 'HH' then date_trunc('hour', p_date)
WHEN 'HH12' then date_trunc('hour', p_date)
WHEN 'HH24' then date_trunc('hour', p_date)
when 'DAY' then date_trunc('week', p_date) - interval '1 day' -- 一周的第一天（从周日开始）
when 'DY' then date_trunc('week', p_date) - interval '1 day' -- 一周的第一天（从周日开始）
when 'D' then date_trunc('week', p_date) - interval '1 day' -- 一周的第一天（从周日开始）
when 'DDD' then date_trunc('day', p_date)
when 'DD' then date_trunc('day', p_date)
when 'J' then date_trunc('day', p_date)
when 'W' then date_trunc('day', p_date) - interval '1day' * (extract('day' from p_date) % 7 - 1) -- 历史上最近的与当月1号相同星期时间的时间
when 'IW' then date_trunc('week', p_date) -- 一周的第一天（从周一开始）
when 'WW' then date_trunc('day', p_date) - interval '1day' * (extract('doy' from p_date) % 7 - 1) -- 历史上最近的与当年1号相同星期时间的时间
when 'MONTH' then date_trunc('month', p_date)
when 'MON' then date_trunc('month', p_date)
when 'MM' then date_trunc('month', p_date)
when 'RM' then date_trunc('month', p_date)
when 'Q' then date_trunc('quarter', p_date)
when 'IYYY' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7 -- 当年第一个周一
when 'IYY' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7 -- 当年第一个周一
when 'IY' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7 -- 当年第一个周一
when 'I' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7 -- 当年第一个周一
when 'SYYYY' then date_trunc('year', p_date)
when 'YYYY' then date_trunc('year', p_date)
when 'YEAR' then date_trunc('year', p_date)
when 'SYEAR' then date_trunc('year', p_date)
when 'YYY' then date_trunc('year', p_date)
when 'YY' then date_trunc('year', p_date)
when 'Y' then date_trunc('year', p_date)
-- when '' then 'decade'
when 'CC' then date_trunc('century', p_date)
when 'SCC' then date_trunc('century', p_date)
-- when '' then 'millennium'
else date_trunc(p_fmt, p_date) end)::timestamp without time zone;
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'trunc(timestamptz,text)', '1.1')
then
CREATE or replace FUNCTION pg_catalog.trunc(p_date timestamptz, p_fmt text)
RETURNS timestamp without time zone
IMMUTABLE
AS $$
select (case upper(p_fmt)
-- when '' then 'microseconds'
-- when '' then 'milliseconds'
when 'SS' then date_trunc('second', p_date)
WHEN 'MI' then date_trunc('minute', p_date)
WHEN 'HH' then date_trunc('hour', p_date)
WHEN 'HH12' then date_trunc('hour', p_date)
WHEN 'HH24' then date_trunc('hour', p_date)
when 'DAY' then date_trunc('week', p_date) - interval '1 day' -- 一周的第一天（从周日开始）
when 'DY' then date_trunc('week', p_date) - interval '1 day' -- 一周的第一天（从周日开始）
when 'D' then date_trunc('week', p_date) - interval '1 day' -- 一周的第一天（从周日开始）
when 'DDD' then date_trunc('day', p_date)
when 'DD' then date_trunc('day', p_date)
when 'J' then date_trunc('day', p_date)
when 'W' then date_trunc('day', p_date) - interval '1day' * (extract('day' from p_date) % 7 - 1) -- 历史上最近的与当月1号相同星期时间的时间
when 'IW' then date_trunc('week', p_date) -- 一周的第一天（从周一开始）
when 'WW' then date_trunc('day', p_date) - interval '1day' * (extract('doy' from p_date) % 7 - 1) -- 历史上最近的与当年1号相同星期时间的时间
when 'MONTH' then date_trunc('month', p_date)
when 'MON' then date_trunc('month', p_date)
when 'MM' then date_trunc('month', p_date)
when 'RM' then date_trunc('month', p_date)
when 'Q' then date_trunc('quarter', p_date)
when 'IYYY' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7 -- 当年第一个周一
when 'IYY' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7 -- 当年第一个周一
when 'IY' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7 -- 当年第一个周一
when 'I' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7 -- 当年第一个周一
when 'SYYYY' then date_trunc('year', p_date)
when 'YYYY' then date_trunc('year', p_date)
when 'YEAR' then date_trunc('year', p_date)
when 'SYEAR' then date_trunc('year', p_date)
when 'YYY' then date_trunc('year', p_date)
when 'YY' then date_trunc('year', p_date)
when 'Y' then date_trunc('year', p_date)
-- when '' then 'decade'
when 'CC' then date_trunc('century', p_date)
when 'SCC' then date_trunc('century', p_date)
-- when '' then 'millennium'
else date_trunc(p_fmt, p_date) end)::timestamp without time zone;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- 只针对 3.0.0 以下版本测试， 3.0.0 版本自带的 trunc 用法与 Oracle 不一致
-- ----------
if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '3.0.0') = -1
then
-- select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''SS'' )', '''2012-12-12 12:12:12''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''MI'' )', '''2012-12-12 12:12:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''HH'' )', '''2012-12-12 12:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''HH12'' )', '''2012-12-12 12:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''HH24'' )', '''2012-12-12 12:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''DAY'' )', '''2012-12-09 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''DY'' )', '''2012-12-09 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''D'' )', '''2012-12-09 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''DDD'' )', '''2012-12-12 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''DD'' )', '''2012-12-12 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''J'' )', '''2012-12-12 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''W'' )', '''2012-12-08 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''IW'' )', '''2012-12-10 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''WW'' )', '''2012-12-09 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''MONTH'')', '''2012-12-01 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''MON'' )', '''2012-12-01 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''MM'' )', '''2012-12-01 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''RM'' )', '''2012-12-01 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''Q'' )', '''2012-10-01 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''IYYY'' )', '''2012-01-02 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''IYY'' )', '''2012-01-02 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''IY'' )', '''2012-01-02 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''I'' )', '''2012-01-02 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''SYYYY'')', '''2012-01-01 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''YYYY'' )', '''2012-01-01 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''YEAR'' )', '''2012-01-01 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''SYEAR'')', '''2012-01-01 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''YYY'' )', '''2012-01-01 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''YY'' )', '''2012-01-01 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''Y'' )', '''2012-01-01 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''CC'' )', '''2001-01-01 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''SCC'' )', '''2001-01-01 00:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 02:12:12.123456''::timestamp, ''HH'' )', '''2012-12-12 02:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 02:12:12.123456''::timestamp, ''HH12'' )', '''2012-12-12 02:00:00''::timestamp') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 02:12:12.123456''::timestamp, ''HH24'' )', '''2012-12-12 02:00:00''::timestamp') into l_result;
-- select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''SS'' )', '''2012-12-12 12:12:12''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''MI'' )', '''2012-12-12 12:12:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''HH'' )', '''2012-12-12 12:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''HH12'' )', '''2012-12-12 12:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''HH24'' )', '''2012-12-12 12:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''DAY'' )', '''2012-12-09 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''DY'' )', '''2012-12-09 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''D'' )', '''2012-12-09 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''DDD'' )', '''2012-12-12 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''DD'' )', '''2012-12-12 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''J'' )', '''2012-12-12 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''W'' )', '''2012-12-08 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''IW'' )', '''2012-12-10 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''WW'' )', '''2012-12-09 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''MONTH'')', '''2012-12-01 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''MON'' )', '''2012-12-01 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''MM'' )', '''2012-12-01 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''RM'' )', '''2012-12-01 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''Q'' )', '''2012-10-01 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''IYYY'' )', '''2012-01-02 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''IYY'' )', '''2012-01-02 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''IY'' )', '''2012-01-02 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''I'' )', '''2012-01-02 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''SYYYY'')', '''2012-01-01 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''YYYY'' )', '''2012-01-01 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''YEAR'' )', '''2012-01-01 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''SYEAR'')', '''2012-01-01 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''YYY'' )', '''2012-01-01 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''YY'' )', '''2012-01-01 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''Y'' )', '''2012-01-01 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''CC'' )', '''2001-01-01 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''SCC'' )', '''2001-01-01 00:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 02:12:12.123456''::timestamptz, ''HH'' )', '''2012-12-12 02:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 02:12:12.123456''::timestamptz, ''HH12'' )', '''2012-12-12 02:00:00''::timestamptz') into l_result;
select compat_tools.f_unit_test('trunc(''2012-12-12 02:12:12.123456''::timestamptz, ''HH24'' )', '''2012-12-12 02:00:00''::timestamptz') into l_result;
end if;
-- =========================================================================
-- =========================================================================
-- nanvl(numeric,numeric)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'nanvl(numeric,numeric)', '1.0')
then
CREATE or replace FUNCTION pg_catalog.nanvl(p_num1 numeric, p_num2 numeric)
RETURNS numeric
IMMUTABLE
AS $$
select case when p_num1 = 'NaN' then p_num2 else p_num1 end;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('nanvl(1,1)' , '1') into l_result;
select compat_tools.f_unit_test('nanvl(2,3)' , '2') into l_result;
select compat_tools.f_unit_test('nanvl(3,null)', '3') into l_result;
select compat_tools.f_unit_test('nanvl(null,4)', 'NULL', 'IS') into l_result;
-- =========================================================================
-- =========================================================================
-- regexp_substr(text,text,int8)
-- regexp_substr(text,text,int8,int8,text,int8)
-- 注意： p_flag 只支持 Oracle 中的 i 和 c 模式，分别表示：
-- i = 大小写不敏感
-- c = 大小写敏感，默认模式
-- =========================================================================
-- 变更历史：
-- 3.0 : 参数中的 int4 类型调整为 int8, 适用性更广
-- =========================================================================
select compat_tools.drop_compat_function('function', 'regexp_substr(text,text,int4)', '3.0') into l_result;
if compat_tools.drop_compat_function('function', 'regexp_substr(text,text,int8)', '3.0')
then
CREATE or replace FUNCTION pg_catalog.regexp_substr ( p_source text
, p_pattern text
, p_position int8)
RETURNS text
IMMUTABLE
AS $$
select regexp_substr(case when p_position > 1 then substr(p_source, p_position) else p_source end, p_pattern);
$$ LANGUAGE sql;
end if;
SELECT compat_tools.drop_compat_function('function', 'regexp_substr(text,text,int4,int4,text,int4)', '3.0', 'plpgsql') into l_result;
if compat_tools.drop_compat_function('function', 'regexp_substr(text,text,int8,int8,text,int8)', '3.0', 'plpgsql')
then
CREATE or replace FUNCTION pg_catalog.regexp_substr ( p_source text
, p_pattern text
, p_position int8
, p_occurrence int8
, p_flag text default 'c'
, p_subexpr int8 default 0)
RETURNS text
IMMUTABLE
AS $$
declare
l_result text;
begin
if p_position < 1
then
raise exception 'ERROR: Argument "p_position" must be greater than 0 (p_position [%] > 0)', p_position;
elsif p_occurrence < 1
then
raise exception 'ERROR: Argument "p_occurrence" must be greater than 0 (p_occurrence [%] > 0)', p_occurrence;
elsif p_subexpr < 0
then
raise exception 'ERROR: Argument "p_subexpr" must not be smaller than 0 (p_subexpr [%] >= 0)', p_subexpr;
end if;
select max(match_array[p_subexpr + 1]) into l_result
from (SELECT rownum as id
, regexp_matches( substr(p_source, p_position)
, '('||p_pattern||')'
, case lower(p_flag) when 'i' then 'gi' else 'g' end
) as match_array
) as t
where id = p_occurrence;
return l_result;
end;
$$ LANGUAGE plpgsql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('regexp_substr(''abcd.AaBbCcDd.1234567890'', ''a.*c'' , 1)' , '''abcd.AaBbCc''') into l_result;
select compat_tools.f_unit_test('regexp_substr(''abcd.AaBbCcDd.1234567890'', ''a.*?c'', 1)' , '''abc''') into l_result;
select compat_tools.f_unit_test('regexp_substr(''abcd.AaBbCcDd.1234567890'', ''a.*c'' , 3)' , '''aBbCc''') into l_result;
select compat_tools.f_unit_test('regexp_substr(''abcd.AaBbCcDd.1234567890'', ''a.*c'' , 8)' , 'NULL', 'IS') into l_result;
select compat_tools.f_unit_test('regexp_substr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 1, ''c'')' , 'NULL', 'IS') into l_result;
select compat_tools.f_unit_test('regexp_substr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 1, ''i'')' , '''bequebazil''') into l_result;
select compat_tools.f_unit_test('regexp_substr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 1, 1, ''i'')' , '''barbeque''') into l_result;
select compat_tools.f_unit_test('regexp_substr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 1, 2, ''i'')' , '''bazilbarf''') into l_result;
select compat_tools.f_unit_test('regexp_substr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, ''i'')' , '''barfbonk''') into l_result;
select compat_tools.f_unit_test('regexp_substr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, ''i'', 0)' , '''barfbonk''') into l_result;
select compat_tools.f_unit_test('regexp_substr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, ''i'', 1)' , '''barf''') into l_result;
select compat_tools.f_unit_test('regexp_substr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, ''i'', 2)' , '''bonk''') into l_result;
select compat_tools.f_unit_test('regexp_substr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, ''i'', 3)' , 'NULL', 'IS') into l_result;
-- =========================================================================
-- =========================================================================
-- regexp_instr(text,text,int8,int8,int8,text,int8)
-- 注意： p_flag 只支持 Oracle 中的 i 和 c 模式，分别表示：
-- i = 大小写不敏感
-- c = 大小写敏感，默认模式
-- 注意： 最后一个参数 p_subexpr 暂时不支持，即不能确定匹配字符串中某个子串的位置
-- =========================================================================
-- 变更历史：
-- 2.0 : 参数中的 int4 类型调整为 int8, 适用性更广
-- =========================================================================
select compat_tools.drop_compat_function('function', 'regexp_instr(text,text,int4,int4,int4,text,int4)', '2.0', 'plpgsql') into l_result;
if compat_tools.drop_compat_function('function', 'regexp_instr(text,text,int8,int8,int8,text,int8)', '2.0', 'plpgsql')
then
CREATE or replace FUNCTION pg_catalog.regexp_instr ( p_source text
, p_pattern text
, p_position int8
, p_occurrence int8
, p_return_opt int8 default 0
, p_flag text default 'c'
, p_subexpr int8 default 0)
RETURNS int
IMMUTABLE
AS $$
declare
l_result int := 0;
i_position int;
i_occurrence int;
i_return_opt int;
i_subexpr int;
begin
i_position := p_position::int;
i_occurrence := p_occurrence::int;
i_return_opt := p_return_opt::int;
i_subexpr := p_subexpr::int;
if i_position < 1
then
raise exception 'ERROR: Argument "i_position" must be greater than 0 (p_position [%] > 0)', i_position;
elsif i_occurrence < 1
then
raise exception 'ERROR: Argument "i_occurrence" must be greater than 0 (p_occurrence [%] > 0)', i_occurrence;
elsif i_return_opt < 0
then
raise exception 'ERROR: Argument "i_return_opt" must not be smaller than 0 (p_return_opt [%] >= 0)', i_return_opt;
elsif i_subexpr < 0
then
raise exception 'ERROR: Argument "i_subexpr" must not be smaller than 0 (p_subexpr [%] >= 0)', i_subexpr;
end if;
select case i_return_opt
-- i_return_opt = 0, 匹配字符串开头位置
when 0 then instr(repeat(' ', i_position - 1)
||pg_catalog.regexp_replace(source_str,
p_pattern,
signal_str||'\&'||signal_str,
case lower(p_flag) when 'i' then 'gi' else 'g' end
),
signal_str,
i_position ,
i_occurrence * 2 - 1
) - (2 * (i_occurrence - 1)) * 3
-- i_return_opt != 0, 匹配字符串结束位置的下一个位置
else instr(repeat(' ', i_position - 1)
||pg_catalog.regexp_replace(source_str,
p_pattern,
signal_str||'\&'||signal_str,
case lower(p_flag) when 'i' then 'gi' else 'g' end
),
signal_str,
i_position ,
i_occurrence * 2
) - (2 * i_occurrence - 1) * 3
end into l_result
from (select substr(p_source, i_position) as source_str
, chr(30)||chr(26)||chr(25) as signal_str) as t;
return coalesce(l_result, 0);
end;
$$ LANGUAGE plpgsql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 1, 0, ''c'')' , 0 ) into l_result;
select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 1, 0, ''i'')' , 7 ) into l_result;
select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 1, 1, ''i'')' , 17 ) into l_result;
select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 1, 1, 0, ''i'')' , 4 ) into l_result;
select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 1, 2, 0, ''i'')' , 12 ) into l_result;
select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, 0, ''i'')' , 17 ) into l_result;
select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, 0, ''i'', 0)' , 17 ) into l_result;
select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, 0, ''i'', 1)' , 17 ) into l_result;
-- select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, 0, ''i'', 2)' , 21 ) into l_result; -- 不支持最后一个参数 p_subexpr
-- select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, 0, ''i'', 3)' , 0 ) into l_result; -- 不支持最后一个参数 p_subexpr
-- 以下测试用例在 openGauss 中与 Oracle 有差异：
-- Oracle 返回第二个 Oracle 的位置： 8, 14, 21, 21
-- openGauss 中由 instr 返回的是第一个满足的位置： 8, 8, 8, 8
-- SELECT regexp_instr('Oracle.begin.Oracle.Oracle.end', 'begin.*(Oracle).*(Oracle)', 1, 1, 0, 'i', 0) as c1
-- , regexp_instr('Oracle.begin.Oracle.Oracle.end', 'begin.*(Oracle).*(Oracle)', 1, 1, 0, 'i', 1) as c1
-- , regexp_instr('Oracle.begin.Oracle.Oracle.end', 'begin.*(Oracle).*(Oracle)', 1, 1, 0, 'i', 2) as c2
-- , regexp_instr('Oracle.begin.Oracle.Oracle.end', 'begin.*Oracle.*(Oracle)', 1, 1, 0, 'i', 1) as c3
-- from dual;
-- SELECT regexp_instr('begin.Oracle.Oracle.end', 'Or.', 1, 1, 0, 'i') as c1
-- , regexp_instr('begin.Oracle.Oracle.end', 'Or.', 1, 2, 0, 'i') as c2
-- , regexp_instr('begin.Oracle.Orbcle.end', 'Or.', 1, 1, 0, 'i') as c3
-- , regexp_instr('begin.Oracle.Orbcle.end', 'Or.', 1, 2, 0, 'i') as c4
-- FROM DUAL;
-- =========================================================================
-- =========================================================================
-- regexp_replace(text,text,text,int8,int8,text)
-- 注意： p_flag 只支持 Oracle 中的 i 和 c 模式，分别表示：
-- i = 大小写不敏感
-- c = 大小写敏感，默认模式
-- =========================================================================
-- 变更历史：
-- 2.0 : 参数中的 int4 类型调整为 int8, 适用性更广
-- =========================================================================
select compat_tools.drop_compat_function('function', 'regexp_replace(text,text,text,int4,int4,text)', '2.0', 'plpgsql') into l_result;
if compat_tools.drop_compat_function('function', 'regexp_replace(text,text,text,int8,int8,text)', '2.0', 'plpgsql')
then
CREATE or replace FUNCTION pg_catalog.regexp_replace ( p_source text
, p_pattern text
, p_replacement text
, p_position int8
, p_occurrence int8 default 0
, p_flag text default 'c')
RETURNS text
IMMUTABLE
AS $$
declare
l_result text := 0;
begin
if p_position < 1
then
raise exception 'ERROR: Argument "p_position" must be greater than 0 (p_position [%] > 0)', p_position;
elsif p_occurrence < 0
then
raise exception 'ERROR: Argument "p_occurrence" must not be smaller than 0 (p_occurrence [%] >= 0)', p_occurrence;
end if;
if p_occurrence = 0
then
select pre_str
|| regexp_replace(source_str,
p_pattern,
p_replacement,
case lower(p_flag) when 'i' then 'gi' else 'g' end
)
into l_result
from (select substr(p_source, 1, p_position - 1) as pre_str
, substr(p_source, p_position) as source_str
, chr(30)||chr(26)||chr(25) as signal_str) as t;
else
select string_agg(case when id % 3 = 2 and p_occurrence = round(id/3.0) then '' -- 原始值的取舍
when id % 3 = 0 and p_occurrence != round(id/3.0) then '' -- 替换值的取舍
else partial_str
end,
''
) into l_result
from (select rownum as id
, unnest(string_to_array( pre_str
|| regexp_replace(source_str,
p_pattern,
signal_str||'\&'||signal_str||p_replacement||signal_str, -- 同时保留原字符串与替换后的字符串，以便在外层再按照 p_occurrence 进行选择
case lower(p_flag) when 'i' then 'gi' else 'g' end
),
signal_str
)
) as partial_str
from (select substr(p_source, 1, p_position - 1) as pre_str
, substr(p_source, p_position) as source_str
, chr(30)||chr(26)||chr(25) as signal_str) as t) as x;
end if;
return l_result;
END;
$$ LANGUAGE plpgsql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('regexp_replace(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', ''XXX'', 5, 0, ''i'')' , '''foobarXXXXXX''') into l_result;
select compat_tools.f_unit_test('regexp_replace(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', ''#\1#'', 5, 0, ''i'')' , '''foobar#beque##barf#''') into l_result;
select compat_tools.f_unit_test('regexp_replace(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', ''XXX'', 5, 1, ''i'')' , '''foobarXXXbarfbonk''') into l_result;
select compat_tools.f_unit_test('regexp_replace(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', ''XXX'', 5, 2, ''i'')' , '''foobarbequebazilXXX''') into l_result;
select compat_tools.f_unit_test('regexp_replace(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', ''XXX'', 5, 3, ''i'')' , '''foobarbequebazilbarfbonk''') into l_result;
-- =========================================================================
-- =========================================================================
-- regexp_count(text,text,int8,text)
-- regexp_count(text,text,int8)
-- regexp_count(text,text)
-- 注意： p_flag 只支持 Oracle 中的 i 和 c 模式，分别表示：
-- i = 大小写不敏感
-- c = 大小写敏感，默认模式
-- =========================================================================
-- 变更历史：
-- 3.0 : 参数中的 int4 类型调整为 int8, 适用性更广
-- 4.0 : 改为无默认值，根据参数个数重载三个函数
-- =========================================================================
select compat_tools.drop_compat_function('function', 'regexp_count(text,text,int4,text)', '3.0', 'sql') into l_result;
if compat_tools.drop_compat_function('function', 'regexp_count(text,text,int8,text)', '3.0', 'sql')
then
CREATE or replace FUNCTION pg_catalog.regexp_count ( p_source text
, p_pattern text
, p_position int8
, p_flag text)
RETURNS int
IMMUTABLE
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_position) as source_str) as x;
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'regexp_count(text,text,int8)', '3.0', 'sql')
then
CREATE or replace FUNCTION pg_catalog.regexp_count ( p_source text
, p_pattern text
, p_position int8)
RETURNS int
IMMUTABLE
AS $$
select length(pg_catalog.regexp_replace(source_str, p_pattern, '#\&', 'g'))
- length(source_str)
from (select substr(p_source, p_position) as source_str) as x;
$$ LANGUAGE sql;
end if;
if compat_tools.drop_compat_function('function', 'regexp_count(text,text)', '3.0', 'sql')
then
CREATE or replace FUNCTION pg_catalog.regexp_count ( p_source text
, p_pattern text)
RETURNS int
IMMUTABLE
AS $$
select length(pg_catalog.regexp_replace(source_str, p_pattern, '#\&', 'g'))
- length(source_str)
from (select substr(p_source, 1) as source_str) as x;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('regexp_count(''abcdA123'', ''a.'')', 1) into l_result;
select compat_tools.f_unit_test('regexp_count(''abcdA123'', ''a.'', 1, ''i'')', 2) into l_result;
select compat_tools.f_unit_test('regexp_count(''abcdA123'', ''a.'', 3)', 0) into l_result;
select compat_tools.f_unit_test('regexp_count(''abcdA123'', ''a.'', 3, ''i'')', 1) into l_result;
-- =========================================================================
-- =========================================================================
-- replace(text,text)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'replace(text,text)', '1.0', 'sql')
then
CREATE or replace FUNCTION pg_catalog.replace ( p_source text
, p_pattern text)
RETURNS text IMMUTABLE AS $$
select pg_catalog.replace(p_source, p_pattern, '');
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('replace(''abcdAbcdasd'', ''a'')', '''bcdAbcdsd''') into l_result;
select compat_tools.f_unit_test('replace(''abcdAbcdasd'', ''s'')', '''abcdAbcdad''') into l_result;
select compat_tools.f_unit_test('replace(''abcdAbcdasd'', null)', '''abcdAbcdasd''') into l_result;
-- =========================================================================
-- =========================================================================
-- ora_hash(anyelement,int8,int8)
-- 测试用例：
-- select ora_hash(relname, 10) from pg_class limit 10;
-- select ora_hash(object_name, 10) from dba_objects where rownum < 11;
-- =========================================================================
-- 变更记录：
-- v1.1 => 解决默认 p_buckets 参数为零时，除数为零的错误，以及桶数量少一个的问题
-- v2.0 => 参数中的 int4 类型调整为 int8, 适用性更广
-- =========================================================================
select compat_tools.drop_compat_function('function', 'ora_hash(anyelement,int4,int4)', '2.0', 'plpgsql') into l_result;
if compat_tools.drop_compat_function('function', 'ora_hash(anyelement,int8,int8)', '2.0', 'plpgsql')
then
CREATE or replace FUNCTION pg_catalog.ora_hash ( p_data anyelement
, p_buckets int8 default 0
, p_seed int8 default 0)
RETURNS bigint
AS $$
declare
l_data_type text;
l_data_result text;
begin
l_data_type := pg_typeof(p_data)::text;
if l_data_type = 'aclitem' then l_data_result := hash_aclitem(p_data);
elsif l_data_type = 'anyarray' then l_data_result := hash_array(p_data);
elsif l_data_type = 'numeric' then l_data_result := hash_numeric(p_data);
elsif l_data_type = 'anyrange' then l_data_result := hash_range(p_data);
elsif l_data_type = 'character' then l_data_result := hashbpchar(p_data);
elsif l_data_type = '"char"' then l_data_result := hashchar(p_data);
elsif l_data_type = 'anyenum' then l_data_result := hashenum(p_data);
elsif l_data_type = 'real' then l_data_result := hashfloat4(p_data);
elsif l_data_type = 'double precision' then l_data_result := hashfloat8(p_data);
elsif l_data_type = 'inet' then l_data_result := hashinet(p_data);
elsif l_data_type = 'tinyint' then l_data_result := hashint1(p_data);
elsif l_data_type = 'smallint' then l_data_result := hashint2(p_data);
elsif l_data_type = 'int2vector' then l_data_result := hashint2vector(p_data);
elsif l_data_type = 'integer' then l_data_result := hashint4(p_data);
elsif l_data_type = 'bigint' then l_data_result := hashint8(p_data);
elsif l_data_type = 'macaddr' then l_data_result := hashmacaddr(p_data);
elsif l_data_type = 'name' then l_data_result := hashname(p_data);
elsif l_data_type = 'oid' then l_data_result := hashoid(p_data);
elsif l_data_type = 'oidvector' then l_data_result := hashoidvector(p_data);
elsif l_data_type = 'text' then l_data_result := hashtext(p_data);
elsif l_data_type = 'internal' then l_data_result := hashvarlena(p_data);
elsif l_data_type = 'interval' then l_data_result := interval_hash(p_data);
elsif l_data_type = 'smalldatetime' then l_data_result := smalldatetime_hash(p_data);
elsif l_data_type = 'time without time zone' then l_data_result := time_hash(p_data);
elsif l_data_type = 'timestamp without time zone' then l_data_result := timestamp_hash(p_data);
elsif l_data_type = 'time with time zone' then l_data_result := timetz_hash(p_data);
elsif l_data_type = 'uuid' then l_data_result := uuid_hash(p_data);
else
raise exception 'Unsupported data type [%]', l_data_type;
end if;
if p_buckets = 0
then
return abs(l_data_result)::bigint;
else
return abs(mod(l_data_result + p_seed, p_buckets + 1));
end if;
end;
$$ LANGUAGE plpgsql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('ora_hash(''abcdAbcdasd''::text)', '0', '>') into l_result;
-- =========================================================================
-- =========================================================================
-- show(text)
-- show_parameter(text)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'show(text)', '1.0', 'sql')
then
CREATE or replace FUNCTION pg_catalog.show (p_keyword text)
RETURNS setof text
IMMUTABLE
AS $$
with matched_params as (select name
, setting||case when '('||unit||')' = '()' then '' else ' ('||unit||')' end as setting
, short_desc
from pg_settings
where name like '%'||p_keyword||'%'
),
max_size as (select max(lengthb(name)) as name_len
, max(lengthb(setting)) as setting_len
, max(lengthb(short_desc)) as desc_len
from matched_params
)
select rpad('name', name_len)||' | '||rpad('setting', setting_len)||' | '||'description'
from max_size
union all
select rpad('-', name_len, '-')||'-+-'||rpad('-', setting_len, '-')||'-+-'||rpad('-', desc_len, '-')
from max_size
union all
select rpad(name, name_len)||' | '||rpad(setting, setting_len)||' | '||short_desc
from matched_params, max_size;
$$ LANGUAGE sql;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('(select count(*) from (select pg_catalog.show(''log_connection'')))', '2', '>') into l_result;
-- =========================================================================
end if;
if compat_tools.drop_compat_function('function', 'show_parameter(text)', '1.0', 'sql')
then
CREATE or replace FUNCTION pg_catalog.show_parameter (p_keyword text)
RETURNS setof text
IMMUTABLE
AS $$
with matched_params as (select name
, setting||case when '('||unit||')' = '()' then '' else ' ('||unit||')' end as setting
, short_desc
from pg_settings
where name like '%'||p_keyword||'%'
),
max_size as (select max(lengthb(name)) as name_len
, max(lengthb(setting)) as setting_len
, max(lengthb(short_desc)) as desc_len
from matched_params
)
select rpad('name', name_len)||' | '||rpad('setting', setting_len)||' | '||'description'
from max_size
union all
select rpad('-', name_len, '-')||'-+-'||rpad('-', setting_len, '-')||'-+-'||rpad('-', desc_len, '-')
from max_size
union all
select rpad(name, name_len)||' | '||rpad(setting, setting_len)||' | '||short_desc
from matched_params, max_size;
$$ LANGUAGE sql;
end if;
-- =========================================================================
/*
-- =========================================================================
-- dump(anynonarray)
-- 参考： http://blog.itpub.net/6906/viewspace-2653368/
-- 测试用例：
-- select dump('红叶'::text);
-- select dump('Hongye'::text);
-- =========================================================================
if compat_tools.drop_compat_function('function', 'dump(anynonarray)', '1.0', 'plpgsql')
then
CREATE OR REPLACE FUNCTION pg_catalog.dump(anynonarray)
RETURNS TEXT
IMMUTABLE
AS $$
DECLARE
v_hexstr TEXT;
v_hexbyte TEXT;
v_tmp TEXT;
i INT;
v_len INT;
BEGIN
SELECT octet_length($1) into v_len;
v_hexstr := 'Len=' || v_len || ' ';
v_tmp := ',';
FOR i in 1..v_len LOOP
select to_hex(get_byte($1::bytea, i-1)) into v_hexbyte;
if i = v_len then
v_tmp := '';
end if;
v_hexstr := v_hexstr || v_hexbyte || v_tmp;
END LOOP;
RETURN v_hexstr;
END;
$$ LANGUAGE plpgsql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('(select count(*) from (select show(''log_connection'')))', '2', '>') into l_result;
-- =========================================================================
*/
-- =========================================================================
-- dump(anyelement,numeric,int8,int8)
-- 参考： https://gitee.com/darkathena/opengauss-oracle/blob/main/oracle-function/dump.sql
-- 测试用例：
-- select dump(1234);
-- select dump(1234.5678);
-- select dump('abcd'::text);
-- select dump('abcd'::VARCHAR);
-- select dump('FFFF'::RAW);
-- select dump('FFFF'::BLOB);
-- select dump(DATE'2022-01-23');
-- select dump(current_timestamp);
-- select dump('abcd'::text,1016);
-- select dump('abcd'::text,1016,2,2);
/*
with t as (select '测试' b from dual)
select dump(b,1),
dump(b,0),
dump(b,null),
dump(b,9),
dump(b,7),
dump(b,10),
dump(b,11),
dump(b,1016),
dump(b,15.8),
dump(b,17),
dump(b,-1),
dump(b,10,2,2),
dump(b,10,2)
from t;
*/
-- =========================================================================
-- 变更历史：
-- 3.0 : 参数中的 int4 类型调整为 int8, 适用性更广
-- 4.0 : 修复文本类型中带 \ 无法转换的问题
-- =========================================================================
select compat_tools.drop_compat_function('function', 'dump(anynonarray)', '1.0') into l_result;
delete from compat_tools.compat_version where object_name = 'pg_catalog.dump(anynonarray)' and object_version = '1.0';
delete from temp_result where object_name = 'pg_catalog.dump(anynonarray)';
select compat_tools.drop_compat_function('function', 'dump(anyelement,numeric,int4,int4)', '3.0', 'plpgsql') into l_result;
if compat_tools.drop_compat_function('function', 'dump(anyelement,numeric,int8,int8)', '4.0', 'plpgsql')
then
CREATE OR REPLACE FUNCTION pg_catalog.dump(anyelement,numeric default 10,int8 default null,int8 default null)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $function$
declare
v_typsend text;
v_type text;
v_bytea bytea;
v_hexstr TEXT;
v_hexbyte TEXT;
v_tmp TEXT;
i INT;
v_len INT;
v_oid int;
v_charset text :='';
v_typcategory text;
begin
select typsend,typname,oid ,typcategory
into v_typsend,v_type,v_oid ,v_typcategory
from pg_type
where oid= pg_typeof($1);
if v_type='blob' then
v_typsend:='rawsend';
elsif v_type='unknown' then
v_typsend:='textsend';
end if;
if v_typcategory='S' then
v_bytea:=replace($1,'\','\\')::text::bytea;
else
EXECUTE 'select '||v_typsend||'(:1)' into v_bytea using $1;
end if;
if $3 is not null and $4 is not null then
v_bytea:=substr(v_bytea,$3::int,$4::int);
elsif $3 is not null and $4 is null then
v_bytea:=substr(v_bytea,$3::int);
end if;
SELECT length(v_bytea) into v_len;
$2:=floor($2);
if $2>1000 and v_typcategory='S' then
select pg_encoding_to_char(encoding) into v_charset
from pg_database where datname=current_database();
v_charset:=' CharacterSet='||v_charset;
$2:=$2-1000;
end if;
v_hexstr := 'Typ='||v_oid||' Len=' || v_len || v_charset||': ';
v_tmp := ',';
if $2>=16 or $2<0 then
FOR i in 1..v_len LOOP
select to_hex(get_byte(v_bytea, i-1)) into v_hexbyte;
if i = v_len then
v_tmp := '';
end if;
v_hexstr := v_hexstr || v_hexbyte || v_tmp;
END LOOP;
elsif $2<>8 or $2 is null then
FOR i in 1..v_len LOOP
select get_byte(v_bytea, i-1) into v_hexbyte;
if i = v_len then
v_tmp := '';
end if;
v_hexstr := v_hexstr || v_hexbyte || v_tmp;
END LOOP;
elsif $2=8 then
raise 'unsupport Octal!';
end if;
RETURN v_hexstr;
END;
$function$;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('dump(1234)', '''Typ=23 Len=4: 0,0,4,210''', '=') into l_result;
select compat_tools.f_unit_test('dump(1234.5678)', '''Typ=1700 Len=12: 0,2,0,0,0,0,0,4,4,210,22,46''', '=') into l_result;
select compat_tools.f_unit_test('dump(''abcd''::text)', '''Typ=25 Len=4: 97,98,99,100''', '=') into l_result;
select compat_tools.f_unit_test('dump(''FFFF''::RAW)', '''Typ=86 Len=2: 255,255''', '=') into l_result;
select compat_tools.f_unit_test('dump(''FFFF''::blob)', '''Typ=88 Len=2: 255,255''', '=') into l_result;
select compat_tools.f_unit_test('dump(to_timestamp(''2022-01-01'',''yyyy-mm-dd'') )', '''Typ=1114 Len=8: 0,2,119,120,188,193,128,0''', '=') into l_result;
select compat_tools.f_unit_test('dump(''abcdefg''::text,1016)', '''Typ=25 Len=7 CharacterSet=%: 61,62,63,64,65,66,67''', 'like') into l_result;
select compat_tools.f_unit_test('dump(''abcdefg''::text,16,2,2)', '''Typ=25 Len=2: 62,63''', '=') into l_result;
-- =========================================================================
-- =========================================================================
-- instrb(text,text,int8,int8)
-- =========================================================================
-- 变更历史：
-- 2.0 : 参数中的 int4 类型调整为 int8, 适用性更广
-- =========================================================================
select compat_tools.drop_compat_function('function', 'instrb(text,text,int4,int4)', '2.0', 'plpgsql') into l_result;
if compat_tools.drop_compat_function('function', 'instrb(text,text,int8,int8)', '2.0', 'plpgsql')
then
CREATE OR REPLACE FUNCTION pg_catalog.instrb( p_source text
, p_search text
, p_position int8 default 1
, p_occurrence int8 default 1)
RETURNS int4 IMMUTABLE strict AS $$
DECLARE
l_return int;
BEGIN
-- 异常参数识别
if p_position <= 0
then
raise exception 'argument ''%'' is out of range', p_position;
end if;
if p_occurrence <= 0
then
raise exception 'argument ''%'' is out of range', p_occurrence;
end if;
-- 从头开始搜索
if p_position = 1
then
-- 定位搜索串的字符位置，然后从开头截取到字符位置前的所有字符，再将字符转为16进制串，转换后每2位一个字节，所以除以2的正数，即为字节数
SELECT case char_instr when 0 then 0 else (length(rawtohex(substr(p_source, 1, char_instr - 1))) / 2)::int + 1 end
INTO l_return
FROM (select instr(p_source, p_search, p_position::int, p_occurrence::int) as char_instr) as t;
-- 从指定位置开始搜索
else
SELECT case when search_in_pos = 0 then 0
else (length(rawtohex(substr( p_source
, 1
, search_in_pos - 1 + length(substrb(p_source, 1, p_position::int - 1)) -- 指定位置之后的搜索下标（字符位置），加上指定位置本身的字符数
)
) -- rawtohex将字符转换为十六进制，转换后每两位一个字节
) / 2)::int + 1 -- 将转换后的十六进制串的长度除以2，即得到字节长度
end into l_return
from (select instr( substrb(p_source, p_position::int) -- substrb截取指定位置之后的子串，instr搜索指定位置之后的字符位置
, p_search
, 1
, p_occurrence::int
) as search_in_pos) as t;
end if;
-- 返回结果，没有搜索到内容，则返回0
if l_return is null
then
return 0;
else
return l_return;
end if;
END;
$$ LANGUAGE plpgsql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
if l_db_encoding='GBK' then
select compat_tools.f_unit_test('instrb(''hhh红ongye'', ''on'')', '6') into l_result;
select compat_tools.f_unit_test('instrb(''hongye'', ''on'')', '2') into l_result;
select compat_tools.f_unit_test('instrb(''hongye'', ''xx'')', '0') into l_result;
select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 6)', '6') into l_result;
select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 6)', '6') into l_result;
select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 7)', '12') into l_result;
select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 6, 1)', '6') into l_result;
select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 6, 2)', '12') into l_result;
select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 6, 3)', '0') into l_result;
else
select compat_tools.f_unit_test('instrb(''hhh红ongye'', ''on'')', '7') into l_result;
select compat_tools.f_unit_test('instrb(''hongye'', ''on'')', '2') into l_result;
select compat_tools.f_unit_test('instrb(''hongye'', ''xx'')', '0') into l_result;
select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 7)', '7') into l_result;
select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 7)', '7') into l_result;
select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 8)', '13') into l_result;
select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 7, 1)', '7') into l_result;
select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 7, 2)', '13') into l_result;
select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 7, 3)', '0') into l_result;
end if;
-- select instrb('hhh红ongye on xxxx', 'on', 7, 0); -- Error
-- =========================================================================
-- =========================================================================
-- vsize(anyelement)
-- =========================================================================
select count(*) into l_cnt
from pg_settings
where name = 'application_name'
and setting = 'checkMe';
if l_cnt = 0
then
create or replace synonym pg_catalog.vsize for pg_catalog.pg_column_size;
-- 记录版本信息
insert into compat_tools.compat_version
values ('synonym', 'vsize', '1.0')
ON DUPLICATE KEY UPDATE object_version = '1.0';
end if;
-- 在 checkMe 脚本中，如果已经存在同义词，则不记录结果
select count(*) + (select count(*)
from pg_settings
where name = 'application_name'
and setting = 'checkMe')
into l_cnt
from pg_synonym
where synname = 'vsize'
and synnamespace = (select oid
from pg_namespace
where nspname = 'pg_catalog');
-- 记录当前结果
if l_cnt < 2
then
insert into temp_result
select 'synonym' as object_type
, 'vsize' as object_name
, max(object_version) as local_version
, '1.0' as script_version
, '' as local_language
, '' as script_language
, 'Create'
from compat_tools.compat_version
where object_name = 'vsize'
and object_type = 'synonym';
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('pg_catalog.vsize(123456789)', 4) into l_result;
select compat_tools.f_unit_test('pg_catalog.vsize(''hongye'')', 7) into l_result;
if l_db_encoding='GBK' then
select compat_tools.f_unit_test('pg_catalog.vsize(''红叶'')', 5) into l_result;
else
select compat_tools.f_unit_test('pg_catalog.vsize(''红叶'')', 7) into l_result;
end if;
select compat_tools.f_unit_test('pg_catalog.vsize(now())', 8) into l_result;
select compat_tools.f_unit_test('pg_catalog.vsize(sysdate)', 8) into l_result;
-- =========================================================================
-- =========================================================================
-- tz_offset(text)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'tz_offset(text)', '1.0', 'plpgsql')
then
CREATE OR REPLACE FUNCTION pg_catalog.tz_offset(text)
RETURNS text IMMUTABLE strict AS $$
DECLARE
l_tz_offset text;
BEGIN
-- 直接输出时间偏移量
if $1 ~ '^[+-]?\d{2}:\d{2}(:\d{2})?$'
then
return $1;
end if;
-- 时区名转偏移量
SELECT max(utc_offset)
INTO l_tz_offset
FROM pg_timezone_names
WHERE name = $1
OR abbrev = $1;
-- 返回时区偏移量，未找到则触发异常
if l_tz_offset is not null
then
return l_tz_offset;
else
raise exception 'timezone region not found';
end if;
END;
$$ LANGUAGE plpgsql;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('pg_catalog.tz_offset(''EST'')', '''-05:00:00''') into l_result;
select compat_tools.f_unit_test('pg_catalog.tz_offset(''Asia/Shanghai'')', '''08:00:00''') into l_result;
select compat_tools.f_unit_test('pg_catalog.tz_offset(''08:00'')', '''08:00''') into l_result;
select compat_tools.f_unit_test('pg_catalog.tz_offset(''-08:11'')', '''-08:11''') into l_result;
select compat_tools.f_unit_test('pg_catalog.tz_offset(''+08:11:34'')', '''+08:11:34''') into l_result;
-- =========================================================================
end if;
-- =========================================================================
-- =========================================================================
-- sys_context(text,text,int8)
-- =========================================================================
-- 变更历史：
-- 2.0 : 参数中的 int4 类型调整为 int8, 适用性更广
-- 2.1 : 修改IP_ADDRESS/NETWORK_PROTOCOL的取值方式
-- 2.2 : 修改SESSIONID的取值方式
-- =========================================================================
select compat_tools.drop_compat_function('function', 'sys_context(text,text,int4)', '2.1', 'sql') into l_result;
if compat_tools.drop_compat_function('function', 'sys_context(text,text,int8)', '2.2', 'sql')
then
CREATE OR REPLACE FUNCTION pg_catalog.sys_context(namespace text, parameter text, length int8 default 256)
RETURNS text IMMUTABLE strict AS $$
select substr( case upper(namespace)
when 'SYS_SESSION_ROLES' then (select case when count(*) = 0 then 'FALSE' else 'TRUE' end
from pg_catalog.pg_auth_members
where roleid = (select oid from pg_roles where rolname = (case when parameter = upper(parameter) then lower(parameter) else parameter end))
and member = (select oid from pg_roles where rolname = current_user))
when 'USERENV' then case upper(parameter)
when 'CLIENT_IDENTIFIER' then current_setting('application_name')
when 'CLIENT_INFO' then current_setting('application_name')
when 'CLIENT_PROGRAM_NAME' then current_setting('application_name')
when 'CDB_NAME' then current_database()::text
when 'CON_ID' then '1'
when 'CON_NAME' then current_database()::text
when 'CURRENT_SCHEMA' then current_schema()::text
when 'CURRENT_SCHEMAID' then (select oid from pg_namespace where nspname = current_schema())::text
when 'CURRENT_USER' then current_user::text
when 'CURRENT_USERID' then (select oid from pg_roles where rolname = current_user)::text
when 'DATABASE_ROLE' then (case when pg_is_in_recovery() then 'PHYSICAL STANDBY' else 'PRIMARY' end)
when 'DB_NAME' then current_database()::text
when 'DB_UNIQUE_NAME' then current_database()::text
when 'HOST' then get_hostname()
when 'INSTANCE' then '1'
when 'INSTANCE_NAME' then current_setting('pgxc_node_name')
when 'IP_ADDRESS' then inet_out(inet_client_addr())::text
when 'ISDBA' then (select case when rolsystemadmin then 'TRUE' else 'FALSE' end from pg_roles where rolname = current_user)
when 'LANG' then (select datcollate from pg_database where datname = current_database())
when 'LANGUAGE' then (select datctype from pg_database where datname = current_database())
when 'MODULE' then current_setting('application_name')
when 'NETWORK_PROTOCOL' then (case when inet_client_addr() is null then 'LOCAL' else 'TCP' end)
when 'NLS_DATE_FORMAT' then current_setting('nls_timestamp_format')
when 'ORACLE_HOME' then current_setting('data_directory')
-- when 'OS_USER'
when 'PID' then pg_backend_pid()::text
when 'SERVER_HOST' then current_setting('listen_addresses')
when 'SERVICE_NAME' then current_database()::text
when 'SESSION_USER' then current_user::text
when 'SESSION_USERID' then (select oid from pg_roles where rolname = current_user)::text
when 'SESSIONID' then (select lwpid from pg_stat_get_thread() gt where gt.pid=pg_backend_pid())::text
when 'SID' then pg_backend_pid()::text
else ''
end
else ''
end
, 1, t.len)
from (select case when length between 1 and 4000 then length else 256 end as len) as t;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('sys_context(''USERENV'', ''CURRENT_SCHEMA'')', 'current_schema()') into l_result;
select compat_tools.f_unit_test('length(sys_context(''USERENV'', ''ORACLE_HOME'', 2))', '2') into l_result;
select compat_tools.f_unit_test('sys_context(''NO_NAMESPACE'', ''NO_PARAMETER'')||''|''', '''|''') into l_result;
-- =========================================================================
-- =========================================================================
-- userenv(text)
-- =========================================================================
-- 变更历史：
-- 1.1 : 修改SESSIONID的取值方式
-- =========================================================================
if compat_tools.drop_compat_function('function', 'userenv(text)', '1.1', 'sql')
then
CREATE OR REPLACE FUNCTION pg_catalog.userenv(parameter text)
RETURNS text IMMUTABLE strict AS $$
select case upper(parameter)
when 'CLIENT_IDENTIFIER' then current_setting('application_name')
when 'CLIENT_INFO' then current_setting('application_name')
when 'CLIENT_PROGRAM_NAME' then current_setting('application_name')
when 'CDB_NAME' then current_database()::text
when 'CON_ID' then '1'
when 'CON_NAME' then current_database()::text
when 'CURRENT_SCHEMA' then current_schema()::text
when 'CURRENT_SCHEMAID' then (select oid from pg_namespace where nspname = current_schema())::text
when 'CURRENT_USER' then current_user::text
when 'CURRENT_USERID' then (select oid from pg_roles where rolname = current_user)::text
when 'DATABASE_ROLE' then (case when pg_is_in_recovery() then 'PHYSICAL STANDBY' else 'PRIMARY' end)
when 'DB_NAME' then current_database()::text
when 'DB_UNIQUE_NAME' then current_database()::text
when 'HOST' then get_hostname()
when 'INSTANCE' then '1'
when 'INSTANCE_NAME' then current_setting('pgxc_node_name')
when 'IP_ADDRESS' then (select remote_ip from comm_client_info() where tid = pg_backend_pid())::text
when 'ISDBA' then (select case when rolsystemadmin then 'TRUE' else 'FALSE' end from pg_roles where rolname = current_user)
when 'LANG' then (select datcollate from pg_database where datname = current_database())
when 'LANGUAGE' then (select datctype from pg_database where datname = current_database())
when 'MODULE' then current_setting('application_name')
when 'NETWORK_PROTOCOL' then (select case when remote_ip = '[local]' then 'LOCAL' else 'TCP' end from comm_client_info() where tid = pg_backend_pid())
when 'NLS_DATE_FORMAT' then current_setting('nls_timestamp_format')
when 'ORACLE_HOME' then current_setting('data_directory')
-- when 'OS_USER'
when 'PID' then pg_backend_pid()::text
when 'SERVER_HOST' then current_setting('listen_addresses')
when 'SERVICE_NAME' then current_database()::text
when 'SESSION_USER' then current_user::text
when 'SESSION_USERID' then (select oid from pg_roles where rolname = current_user)::text
when 'SESSIONID' then (select lwpid from pg_stat_get_thread() gt where gt.pid=pg_backend_pid())::text
when 'SID' then pg_backend_pid()::text
else ''
end;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('userenv(''CURRENT_SCHEMA'')', 'current_schema()') into l_result;
select compat_tools.f_unit_test('userenv(''NO_PARAMETER'')||''|''', '''|''') into l_result;
-- =========================================================================
-- =========================================================================
-- round(timestamptz,text)
-- todo: 'IYYY', 'IYY', 'IY', 'I' 格式未实现
-- =========================================================================
-- break on round_value skip
-- select round(to_date('20191231121212','yyyymmddhh24miss') + 366 * level, 'SCC') round_value
-- , to_date('20191231','yyyymmdd') + 366 * level
-- from dual connect by 1=1 and level < 400
-- order by 2;
if compat_tools.drop_compat_function('function', 'round(timestamptz,text)', '1.0', 'sql')
then
CREATE or replace FUNCTION pg_catalog.round(p_date timestamptz, p_fmt text default 'DD')
RETURNS timestamp without time zone IMMUTABLE
AS $$
select (case when upper(p_fmt) in ('DDD', 'DD', 'J') then date_trunc('day', p_date + interval '12 hours')
when upper(p_fmt) in ('HH', 'HH12', 'HH24') then date_trunc('hour', p_date + interval '30 minutes')
when upper(p_fmt) = 'MI' then date_trunc('minute', p_date + interval '30 seconds')
when upper(p_fmt) in ('SYYYY', 'YYYY', 'YEAR', 'SYEAR', 'YYY', 'YY', 'Y') then date_trunc('year', p_date + interval '6 months')
when upper(p_fmt) = 'SS' then date_trunc('second', p_date + interval '500000 microseconds')
-- 规约到周日（'DAY', 'DY', 'D'）的逻辑如下：
-- dow = extract('dow' from p_date) 取一周内的天数，从 0 ~ 6，分别表示 周日 ~ 周六
-- 以周日为中心，前面取 4 天，后面取 2 天
-- 1. 周日不变； 0: -0
-- 2. 周日后面的周一至周二，调整到当前周日： 1: -1, 2: -2
-- 3. 周日前面的周三至周六，调整到当前周日： 3: +4, 4: +3, 5: +2, 6: +1
when upper(p_fmt) in ('DAY', 'DY', 'D') then case when extract('dow' from p_date) <= 2 then date_trunc('day', p_date - interval '1 days' * extract('dow' from p_date))
else date_trunc('day', p_date + interval '1 days' * (7 - extract('dow' from p_date)))
end
-- 规约到周一（'IW'）的逻辑如下：
-- dow = extract('dow' from p_date) 取一周内的天数，从 0 ~ 6，分别表示 周日 ~ 周六
-- 以周一为中心，前面取 4 天，后面取 2 天
-- 1. 周一不变； 1: -0
-- 2. 周一后面的周二至周三，调整到当前周一： 2: -1, 3: -2;
-- 3. 周一前面的周四至周日，调整到当前周一： 4: +4, 5: +3, 6: +2, 7: +1
when upper(p_fmt) = 'IW' then case when extract('dow' from p_date) <= 3 then date_trunc('day', p_date - interval '1 days' * (extract('dow' from p_date) - 1))
else date_trunc('day', p_date + interval '1 days' * (8 - extract('dow' from p_date)))
end
-- 规约到当月1号的星期数（'W'）的逻辑如下：
-- day = extract('day' from p_date) 取一个月内的天数
-- 最终规约结果： 1 = 1~3, 8 = 4~10, 15 = 11 ~ 17, 22: 18 ~ 24, 29: 25 ~ 31
-- 映射函数： 7 * ceil((day - 3) / 7)
when upper(p_fmt) = 'W' then date_trunc('month', p_date) + interval '1 day' * (7 * ceil((extract('day' from p_date) - 3) / 7))
-- 规约到当年1号的星期数（'WW'）的逻辑如下：
-- doy = extract('doy' from p_date) 取一年内的天数
-- 最终规约结果： 1 = 1~3, 8 = 4~10, 15 = 11 ~ 17, 22: 18 ~ 24, 29: 25 ~ 31 ...
-- 映射函数： 7 * ceil((doy - 3) / 7)
when upper(p_fmt) = 'WW' then date_trunc('year', p_date) + interval '1 day' * (7 * ceil((extract('doy' from p_date) - 3) / 7))
when upper(p_fmt) in ('MONTH', 'MON', 'MM', 'RM') then date_trunc('month', p_date + interval '15 days')
when upper(p_fmt) = 'Q' then case when to_char(p_date, 'MMDD') >= '1116' then date_trunc('year', p_date) + interval '1 year'
when to_char(p_date, 'MMDD') >= '0816' then date_trunc('year', p_date) + interval '9 months'
when to_char(p_date, 'MMDD') >= '0516' then date_trunc('year', p_date) + interval '6 months'
when to_char(p_date, 'MMDD') >= '0216' then date_trunc('year', p_date) + interval '3 months'
else date_trunc('year', p_date)
end
-- when upper(p_fmt) in ('IYYY', 'IYY', 'IY', 'I') then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7 -- 当年第一个周一
when upper(p_fmt) in ('CC', 'SCC') then date_trunc('century', p_date + interval '49 years')
else null end)::timestamp without time zone;
$$ LANGUAGE sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
-- select round(to_timestamp('1999-01-02 01:02:03', 'YYYY-MM-DD HH24:MI:SS')) R_DEFAULT_1
-- , round(to_timestamp('1999-01-02 01:02:03', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY') as R_YYYY_1
-- , round(to_timestamp('1999-01-02 01:02:03', 'YYYY-MM-DD HH24:MI:SS'), 'MM') as R_MM_1
-- , round(to_timestamp('1999-01-02 01:02:03', 'YYYY-MM-DD HH24:MI:SS'), 'DD') as R_DD_1
-- , round(to_timestamp('1999-01-02 01:02:03', 'YYYY-MM-DD HH24:MI:SS'), 'HH24') as R_HH24_1
-- , round(to_timestamp('1999-01-02 01:02:03', 'YYYY-MM-DD HH24:MI:SS'), 'MI') as R_MI_1
-- , round(to_timestamp('2012-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) R_DEFAULT_2
-- , round(to_timestamp('2012-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY') as R_YYYY_2
-- , round(to_timestamp('2012-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'), 'MM') as R_MM_2
-- , round(to_timestamp('2012-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'), 'DD') as R_DD_2
-- , round(to_timestamp('2012-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'), 'HH24') as R_HH24_2
-- , round(to_timestamp('2012-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'), 'MI') as R_MI_2
-- FROM DUAL;
-- Oracle:
-- R_DEFAULT_1 R_YYYY_1 R_MM_1 R_DD_1 R_HH24_1 R_MI_1
-- ------------------- ------------------- ------------------- ------------------- ------------------- -------------------
-- R_DEFAULT_2 R_YYYY_2 R_MM_2 R_DD_2 R_HH24_2 R_MI_2
-- ------------------- ------------------- ------------------- ------------------- ------------------- -------------------
-- 1999-01-02 00:00:00 1999-01-01 00:00:00 1999-01-01 00:00:00 1999-01-02 00:00:00 1999-01-02 01:00:00 1999-01-02 01:02:00
-- 2013-01-01 00:00:00 2013-01-01 00:00:00 2013-01-01 00:00:00 2013-01-01 00:00:00 2013-01-01 00:00:00 2013-01-01 00:00:00
-- openGauss:
-- r_default_1 | r_yyyy_1 | r_mm_1 | r_dd_1 | r_hh24_1 | r_mi_1 | r_default_2 | r_yyyy_2 | r_mm_2 | r_dd_2 | r_hh24_2 | r_mi_2
-- ---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------
-- 1999-01-02 00:00:00 | 1999-01-01 00:00:00 | 1999-01-01 00:00:00 | 1999-01-02 00:00:00 | 1999-01-02 01:00:00 | 1999-01-02 01:02:00 | 2013-01-01 00:00:00 | 2013-01-01 00:00:00 | 2013-01-01 00:00:00 | 2013-01-01 00:00:00 | 2013-01-01 00:00:00 | 2013-01-01 00:00:00
-- (1 row)
select compat_tools.f_unit_test('round(''1999-01-02 01:02:03''::timestamp)::text', '''1999-01-02 00:00:00''') into l_result;
select compat_tools.f_unit_test('round(''1999-01-02 01:02:03''::timestamp, ''YYYY'')::text', '''1999-01-01 00:00:00''') into l_result;
select compat_tools.f_unit_test('round(''1999-01-02 01:02:03''::timestamp, ''MM'')::text', '''1999-01-01 00:00:00''') into l_result;
select compat_tools.f_unit_test('round(''1999-01-02 01:02:03''::timestamp, ''DD'')::text', '''1999-01-02 00:00:00''') into l_result;
select compat_tools.f_unit_test('round(''1999-01-02 01:02:03''::timestamp, ''HH24'')::text', '''1999-01-02 01:00:00''') into l_result;
select compat_tools.f_unit_test('round(''1999-01-02 01:02:03''::timestamp, ''MI'')::text', '''1999-01-02 01:02:00''') into l_result;
select compat_tools.f_unit_test('round(''2012-12-31 23:59:59''::timestamp)::text', '''2013-01-01 00:00:00''') into l_result;
select compat_tools.f_unit_test('round(''2012-12-31 23:59:59''::timestamp, ''YYYY'')::text', '''2013-01-01 00:00:00''') into l_result;
select compat_tools.f_unit_test('round(''2012-12-31 23:59:59''::timestamp, ''MM'')::text', '''2013-01-01 00:00:00''') into l_result;
select compat_tools.f_unit_test('round(''2012-12-31 23:59:59''::timestamp, ''DD'')::text', '''2013-01-01 00:00:00''') into l_result;
select compat_tools.f_unit_test('round(''2012-12-31 23:59:59''::timestamp, ''HH24'')::text', '''2013-01-01 00:00:00''') into l_result;
select compat_tools.f_unit_test('round(''2012-12-31 23:59:59''::timestamp, ''MI'')::text', '''2013-01-01 00:00:00''') into l_result;
-- =========================================================================
-- =========================================================================
-- bitor(int8,int8)
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/bitor.sql
-- =========================================================================
if compat_tools.drop_compat_function('function', 'bitor(int8,int8)', '1.0')
then
CREATE OR REPLACE FUNCTION pg_catalog.bitor(int8, int8)
RETURNS int8 IMMUTABLE STRICT
LANGUAGE sql
AS $$ select $1 | $2 $$;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('bitor(1, 1)', '1') into l_result;
select compat_tools.f_unit_test('bitor(1, 0)', '1') into l_result;
select compat_tools.f_unit_test('bitor(12, 6)', '14') into l_result;
select compat_tools.f_unit_test('bitor(3, 6)', '7') into l_result;
-- =========================================================================
-- =========================================================================
-- bitor(int8,int8)
-- 参考 ：https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/bitxor.sql
-- =========================================================================
if compat_tools.drop_compat_function('function', 'bitxor(int8,int8)', '1.0')
then
CREATE OR REPLACE FUNCTION pg_catalog.bitxor(int8, int8)
RETURNS int8 IMMUTABLE STRICT
LANGUAGE sql
AS $$ select $1 # $2 $$;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('bitxor(1, 1)', '0') into l_result;
select compat_tools.f_unit_test('bitxor(1, 0)', '1') into l_result;
select compat_tools.f_unit_test('bitxor(12, 6)', '10') into l_result;
select compat_tools.f_unit_test('bitxor(3, 6)', '5') into l_result;
-- =========================================================================
-- =========================================================================
-- bit_and_agg(numeric)
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/bit_and_agg.sql
-- =========================================================================
if compat_tools.drop_compat_function('aggregate', 'bit_and_agg(numeric)', '1.0', 'internal')
then
CREATE OR REPLACE FUNCTION pg_catalog.bit_and_agg_state_func(results int, val numeric)
RETURNS int
LANGUAGE sql
COST 50 IMMUTABLE
AS $$
select (case when results is null then val else results&val end)::int;
$$;
CREATE AGGREGATE pg_catalog.bit_and_agg(numeric)
(
sfunc = pg_catalog.bit_and_agg_state_func,
stype = int
);
end if;
-- =========================================================================
-- =========================================================================
-- bit_or_agg(numeric)
-- 参考 https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/bit_or_agg.sql
-- =========================================================================
if compat_tools.drop_compat_function('aggregate', 'bit_or_agg(numeric)', '1.0', 'internal')
then
CREATE OR REPLACE FUNCTION pg_catalog.bit_or_agg_state_func(results int, val numeric)
RETURNS int
LANGUAGE sql
COST 50 IMMUTABLE
AS $$
select (case when results is null then val else results|val end)::int;
$$;
CREATE AGGREGATE pg_catalog.bit_or_agg(numeric)
(
sfunc = pg_catalog.bit_or_agg_state_func,
stype = int
);
end if;
-- =========================================================================
-- =========================================================================
-- bit_xor_agg(numeric)
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/bit_xor_agg.sql
-- =========================================================================
if compat_tools.drop_compat_function('aggregate', 'bit_xor_agg(numeric)', '1.0', 'internal')
then
CREATE OR REPLACE FUNCTION pg_catalog.bit_xor_agg_state_func(results int, val numeric)
RETURNS int
LANGUAGE sql
COST 50 IMMUTABLE
AS $$
select (case when results is null then val else results # val end)::int;
$$;
CREATE AGGREGATE pg_catalog.bit_xor_agg(numeric)
(
sfunc = pg_catalog.bit_xor_agg_state_func,
stype = int
);
end if;
-- =========================================================================
-- =========================================================================
-- bin_to_num(int4[])
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/bin_to_num.sql
-- 2.1.0数据库版本,A兼容模式，array_to_string第二个参数传空值会输出异常，因此暂时指定分隔符再去掉
-- https://gitee.com/opengauss/openGauss-server/issues/I4SFRR?from=project-issue
-- =========================================================================
if compat_tools.drop_compat_function('function', 'bin_to_num(int4[])', '1.0')
then
CREATE OR REPLACE FUNCTION pg_catalog.bin_to_num(VARIADIC integer [])
RETURNS int
LANGUAGE sql
NOT FENCED NOT SHIPPABLE
AS $$
select int8(replace(array_to_string($1,','),',')::varbit)::int;
$$;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('bin_to_num(1,0,1,0)', '10') into l_result;
select compat_tools.f_unit_test('bin_to_num(1,1,1,1)', '15') into l_result;
-- =========================================================================
-- =========================================================================
-- any_value(anyelement)
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/any_value.sql
-- 备注 :仅数据库2.1.0及以上版本可用
-- =========================================================================
if regexp_substr((string_to_array(regexp_substr(version(), '\([^\)]+\)'),' '))[2],'\d[^\.]*?\.\d[^\.]*?')::NUMERIC>=2.1 then
if compat_tools.drop_compat_function('aggregate', 'any_value(anyelement)', '1.0', 'internal')
then
create aggregate pg_catalog.any_value(anyelement) (
sfunc = first_transition,
stype = anyelement
);
end if;
end if;
-- =========================================================================
-- =========================================================================
-- sinh(numeric)
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/sinh.sql
-- =========================================================================
if compat_tools.drop_compat_function('function', 'sinh(numeric)', '1.0')
then
CREATE OR REPLACE FUNCTION pg_catalog.sinh(numeric)
returns numeric
LANGUAGE sql
as $$
select (exp($1)-exp(-$1))/2 $$;
end if;
-- =========================================================================
-- =========================================================================
-- cosh(numeric)
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/cosh.sql
-- =========================================================================
if compat_tools.drop_compat_function('function', 'cosh(numeric)', '1.0')
then
CREATE OR REPLACE FUNCTION pg_catalog.cosh(numeric)
returns numeric
LANGUAGE sql
as $$
select (exp($1)+exp(-$1))/2 $$;
end if;
-- =========================================================================
-- =========================================================================
-- tanh(numeric)
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/tanh.sql
-- =========================================================================
if compat_tools.drop_compat_function('function', 'tanh(numeric)', '1.0')
then
CREATE OR REPLACE FUNCTION pg_catalog.tanh(numeric)
returns numeric
LANGUAGE sql
as $$
select (exp($1)-exp(-$1))/(exp($1)+exp(-$1)) $$;
end if;
-- =========================================================================
-- =========================================================================
-- lnnvl(bool)
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/lnnvl.sql
-- =========================================================================
if compat_tools.drop_compat_function('function', 'lnnvl(bool)', '1.0')
then
create or replace function pg_catalog.lnnvl(bool)
returns BOOl
LANGUAGE sql
as $$ select case when $1 is null or $1=false then true else false end $$;
end if;
-- =========================================================================
-- =========================================================================
-- numtoyminterval(numeric,text)
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/numtoyminterval.sql
-- =========================================================================
if compat_tools.drop_compat_function('function', 'numtoyminterval(numeric,text)', '1.0')
then
CREATE OR REPLACE FUNCTION pg_catalog.numtoyminterval(numeric,text)
returns interval
LANGUAGE sql
as $$
select NUMTODSINTERVAL($1,$2) $$;
end if;
-- =========================================================================
-- =========================================================================
-- systimestamp()
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/systimestamp.sql
-- =========================================================================
if compat_tools.drop_compat_function('function', 'systimestamp()', '1.0')
then
CREATE OR REPLACE FUNCTION pg_catalog."systimestamp"()
RETURNS timestamp with time zone LANGUAGE sql
AS $$
select current_timestamp $$;
end if;
-- =========================================================================
-- =========================================================================
-- remainder(numeric,numeric)
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/remainder.sql
-- =========================================================================
if compat_tools.drop_compat_function('function', 'remainder(numeric,numeric)', '1.0')
then
create or replace function pg_catalog.remainder(numeric,numeric)
returns numeric
LANGUAGE sql
as $$
select $1-$2*round($1/$2) $$;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('remainder(5.5,2)', '-0.5') into l_result;
select compat_tools.f_unit_test('remainder(8,2)', '0') into l_result;
-- =========================================================================
-- =========================================================================
-- round_ties_to_even(numeric,int8)
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/round_ties_to_even.sql
-- =========================================================================
-- 变更历史：
-- 2.0 : 参数中的 int4 类型调整为 int8, 适用性更广
-- =========================================================================
--移除单参数版本
select compat_tools.drop_compat_function('function', 'round_ties_to_even(numeric)', '1.0') into l_result;
delete from compat_tools.compat_version where object_name = 'pg_catalog.round_ties_to_even(numeric)' and object_version = '1.0';
delete from temp_result where object_name = 'pg_catalog.round_ties_to_even(numeric)';
select compat_tools.drop_compat_function('function', 'round_ties_to_even(numeric,int4)', '2.0','plpgsql') into l_result;
if compat_tools.drop_compat_function('function', 'round_ties_to_even(numeric,int8)', '2.0','plpgsql')
then
create or replace function pg_catalog.round_ties_to_even(n NUMERIC,places int8 DEFAULT 0)
RETURNS numeric
LANGUAGE plpgsql
IMMUTABLE STRICT NOT FENCED NOT SHIPPABLE COST 1
AS $$
declare
l_ret numeric;
l_dif numeric;
begin
l_ret := round(n,places::int);
l_dif := l_ret-n;
if abs(l_dif)*(10^places) = 0.5 then
if not (l_ret * (10^places)) % 2 = 0 then
l_ret := round(n-l_dif,places::int);
end if;
end if;
return l_ret;
end;
$$;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('round_ties_to_even(4.5)', '4') into l_result;
select compat_tools.f_unit_test('round_ties_to_even(4.6)', '5.0') into l_result;
-- =========================================================================
-- =========================================================================
-- soundex(text)
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/soundex.sql
-- =========================================================================
if compat_tools.drop_compat_function('function', 'soundex(text)', '1.0')
then
CREATE OR REPLACE FUNCTION pg_catalog.soundex(text)
returns TEXT
LANGUAGE sql
as $$
select rpad(regexp_replace(string_agg(x, ''), '(\w)\1{1,}', '\1'),4,'0')
from (select case
when rownum = 1 then
UPPER(c)
when c in('b', 'f', 'p', 'v') then
'1'
when c in('c', 'g', 'j', 'k', 'q', 's', 'x', 'z') then
'2'
when c in('d', 't') then
'3'
when c in('l') then
'4'
when c in('m', 'n') then
'5'
when c in('r') then
'6'
end x
from (select unnest(string_to_array(lower($1), null) ) c ) ) $$;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('soundex(''smith'')', '''S530''') into l_result;
select compat_tools.f_unit_test('soundex(''OPENGAUSS'')', '''O152''') into l_result;
-- =========================================================================
-- =========================================================================
-- kurtosis_pop(numeric)
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/kurtosis_pop.sql
-- =========================================================================
if compat_tools.drop_compat_function('aggregate', 'kurtosis_pop(numeric)', '1.1', 'internal')
then
CREATE OR REPLACE FUNCTION pg_catalog.kurtosis_pop_final_func(results numeric [])
RETURNS numeric
LANGUAGE plpgsql
COST 111 IMMUTABLE
AS $$
DECLARE
av numeric;
stp numeric;
ct numeric;
avsp numeric;
kurt_s numeric;
begin
select avg(s) :: numeric, power(stddev(s), 4) :: numeric, count(s) :: numeric into av, stp, ct
from (select unnest(results) s );
select sum(power(s - av, 4)) into avsp
from (select unnest(results) s );
kurt_s := ct * (ct + 1) * avsp / ((ct - 1) * (ct - 2) * (ct - 3) * stp) -
3 * power((ct - 1), 2) / ((ct - 2) * (ct - 3));
return (kurt_s*(ct-2)*(ct-3)/(ct-1)-6)/(ct+1);
EXCEPTION WHEN OTHERS THEN RETURN 0;
end; $$;
CREATE AGGREGATE pg_catalog.kurtosis_pop(numeric)
(
sfunc = array_append,
stype = numeric[],
FINALFUNC = pg_catalog.kurtosis_pop_final_func
);
end if;
-- =========================================================================
-- =========================================================================
-- kurtosis_samp(numeric)
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/kurtosis_samp.sql
-- =========================================================================
if compat_tools.drop_compat_function('aggregate', 'kurtosis_samp(numeric)', '1.1', 'internal')
then
CREATE OR REPLACE FUNCTION pg_catalog.kurtosis_samp_final_func(results numeric [])
RETURNS numeric
LANGUAGE plpgsql
COST 111 IMMUTABLE
AS $$
DECLARE
av numeric;
stp numeric;
ct numeric;
avsp numeric;
begin
select avg(s) :: numeric, power(stddev(s), 4) :: numeric, count(s) :: numeric into av, stp, ct
from (select unnest(results) s );
select sum(power(s - av, 4)) into avsp
from (select unnest(results) s );
return ct * (ct + 1) * avsp / ((ct - 1) * (ct - 2) * (ct - 3) * stp) -
3 * power((ct - 1), 2) / ((ct - 2) * (ct - 3));
EXCEPTION WHEN OTHERS THEN RETURN 0;
end; $$;
CREATE AGGREGATE pg_catalog.kurtosis_samp(numeric)
(
sfunc = array_append,
stype = numeric[],
FINALFUNC = pg_catalog.kurtosis_samp_final_func
);
end if;
-- =========================================================================
-- =========================================================================
-- skewness_pop(numeric)
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/skewness_pop.sql
-- =========================================================================
if compat_tools.drop_compat_function('aggregate', 'skewness_pop(numeric)', '1.1', 'internal')
then
CREATE OR REPLACE FUNCTION pg_catalog.skewness_pop_final_func(results numeric [])
RETURNS numeric
LANGUAGE plpgsql
COST 111 IMMUTABLE
AS $$
DECLARE
av numeric;
stp numeric;
ct numeric;
avsp numeric;
skew_s numeric;
begin
select avg(s) :: numeric, power(stddev(s), 3) :: numeric, count(s) :: numeric into av, stp, ct
from (select unnest(results) s );
select sum(power(s - av, 3)) into avsp
from (select unnest(results) s );
skew_s := ct * (avsp / ((ct - 1) * (ct - 2) * stp));
return skew_s*(ct-2)/sqrt(ct*(ct-1));
EXCEPTION WHEN OTHERS THEN RETURN 0;
end; $$;
CREATE AGGREGATE pg_catalog.skewness_pop(numeric)
(
sfunc = array_append,
stype = numeric[],
FINALFUNC = pg_catalog.skewness_pop_final_func
);
end if;
-- =========================================================================
-- =========================================================================
-- skewness_samp(numeric)
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/skewness_samp.sql
-- =========================================================================
if compat_tools.drop_compat_function('aggregate', 'skewness_samp(numeric)', '1.1', 'internal')
then
CREATE OR REPLACE FUNCTION pg_catalog.skewness_samp_final_func(results numeric [])
RETURNS numeric
LANGUAGE plpgsql
COST 111 IMMUTABLE
AS $$
DECLARE
av numeric;
stp numeric;
ct numeric;
avsp numeric;
begin
select avg(s) :: numeric, power(stddev(s), 3) :: numeric, count(s) :: numeric into av, stp, ct
from (select unnest(results) s );
select sum(power(s - av, 3)) into avsp
from (select unnest(results) s );
return ct * (avsp / ((ct - 1) * (ct - 2) * stp));
EXCEPTION WHEN OTHERS THEN RETURN 0;
end; $$;
CREATE AGGREGATE pg_catalog.skewness_samp(numeric)
(
sfunc = array_append,
stype = numeric[],
FINALFUNC = pg_catalog.skewness_samp_final_func
);
end if;
-- =========================================================================
-- =========================================================================
-- asciistr(text)
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/asciistr.sql
-- 2023-07-01 ： 3.0 版本，调整ascii扩展集也转换成2字节
-- =========================================================================
if compat_tools.drop_compat_function('function', 'asciistr(text)', '3.0')
then
CREATE OR REPLACE FUNCTION pg_catalog.asciistr(text)
RETURNS text
LANGUAGE sql
NOT FENCED NOT SHIPPABLE
AS $$
select string_agg( (case when ascii(s)<=127 and s!='\' then s else
'\'||lpad(upper(to_hex(ascii(s))::text),4,'0') end ),'')
from (select unnest(string_to_array($1, null) ) s);
$$;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('asciistr(''abc测试?+12*&@u'')', '''abc\6D4B\8BD5?+12*&@u''') into l_result;
select compat_tools.f_unit_test('asciistr(''AB。CDE'')', '''AB\3002CDE''') into l_result;
-- =========================================================================
-- =========================================================================
-- unistr(text)
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/unistr.sql
-- =========================================================================
if compat_tools.drop_compat_function('function', 'unistr(text)', '1.0','plpgsql')
then
CREATE OR REPLACE FUNCTION pg_catalog.unistr(text)
RETURNS text
LANGUAGE plpgsql
NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
r text;
begin
IF LENGTH(instr($1,'\'))>0 THEN
EXECUTE left(REPLACE(REPLACE('select '||'U&'''||quote_nullable($1)||'''','U&''E''','U&'''),'\\','\'),-1) into r;
ELSe r:=$1;
END IF;
return r;
end; $$;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('unistr(''\6D4B\8BD5'')', '''测试''') into l_result;
-- 防注入测试
select compat_tools.f_unit_test('unistr(''\6D4B\8BD5'''';select ''''1 '')', '''测试'''';select ''''1 ''') into l_result;
-- =========================================================================
-- =========================================================================
-- to_blob(raw)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'to_blob(raw)', '1.0')
then
create or replace function pg_catalog.to_blob(raw)
returns blob
LANGUAGE sql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
SELECT $1::blob;
$$;
end if;
-- =========================================================================
-- =========================================================================
-- convert(text,text,text)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'convert(text,text,text)', '1.0')
then
CREATE OR REPLACE FUNCTION pg_catalog.CONVERT(TEXT,TEXT,TEXT DEFAULT 'utf8')
RETURNS TEXT
LANGUAGE sql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
SELECT convert_from(convert_to($1,$2),$3);
$$;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('CONVERT(''娴嬭瘯'', ''gbk'', ''utf8'')', '''测试''') into l_result;
-- =========================================================================
-- =========================================================================
-- nls_charset_name(int8)
-- =========================================================================
-- 变更历史：
-- 2.0 : 参数中的 int4 类型调整为 int8, 适用性更广
-- =========================================================================
if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '2.0.0') = 1
then
select compat_tools.drop_compat_function('function', 'nls_charset_name(int4)', '2.0') into l_result;
if compat_tools.drop_compat_function('function', 'nls_charset_name(int8)', '2.0')
then
create or replace function pg_catalog.nls_charset_name(int4)
RETURNS TEXT
LANGUAGE sql
IMMUTABLE NOT FENCED NOT SHIPPABLE
as $$
select ('{
"1":"US7ASCII","2":"WE8DEC","3":"WE8HP","4":"US8PC437","5":"WE8EBCDIC37",
"6":"WE8EBCDIC500","7":"WE8EBCDIC1140","8":"WE8EBCDIC285","9":"WE8EBCDIC1146","10":"WE8PC850",
"11":"D7DEC","12":"F7DEC","13":"S7DEC","14":"E7DEC","15":"SF7ASCII",
"16":"NDK7DEC","17":"I7DEC","18":"NL7DEC","19":"CH7DEC","20":"YUG7ASCII",
"21":"SF7DEC","22":"TR7DEC","23":"IW7IS960","25":"IN8ISCII","27":"WE8EBCDIC1148",
"28":"WE8PC858","31":"WE8ISO8859P1","32":"EE8ISO8859P2","33":"SE8ISO8859P3","34":"NEE8ISO8859P4",
"35":"CL8ISO8859P5","36":"AR8ISO8859P6","37":"EL8ISO8859P7","38":"IW8ISO8859P8","39":"WE8ISO8859P9",
"40":"NE8ISO8859P10","41":"TH8TISASCII","42":"TH8TISEBCDIC","43":"BN8BSCII","44":"VN8VN3",
"45":"VN8MSWIN1258","46":"WE8ISO8859P15","47":"BLT8ISO8859P13","48":"CEL8ISO8859P14","49":"CL8ISOIR111",
"50":"WE8NEXTSTEP","51":"CL8KOI8U","52":"AZ8ISO8859P9E","61":"AR8ASMO708PLUS","70":"AR8EBCDICX",
"72":"AR8XBASIC","81":"EL8DEC","82":"TR8DEC","90":"WE8EBCDIC37C","91":"WE8EBCDIC500C",
"92":"IW8EBCDIC424","93":"TR8EBCDIC1026","94":"WE8EBCDIC871","95":"WE8EBCDIC284","96":"WE8EBCDIC1047",
"97":"WE8EBCDIC1140C","98":"WE8EBCDIC1145","99":"WE8EBCDIC1148C","100":"WE8EBCDIC1047E","101":"WE8EBCDIC924",
"110":"EEC8EUROASCI","113":"EEC8EUROPA3","114":"LA8PASSPORT","140":"BG8PC437S","150":"EE8PC852",
"152":"RU8PC866","153":"RU8BESTA","154":"IW8PC1507","155":"RU8PC855","156":"TR8PC857",
"158":"CL8MACCYRILLIC","159":"CL8MACCYRILLICS","160":"WE8PC860","161":"IS8PC861","162":"EE8MACCES",
"163":"EE8MACCROATIANS","164":"TR8MACTURKISHS","165":"IS8MACICELANDICS","166":"EL8MACGREEKS","167":"IW8MACHEBREWS",
"170":"EE8MSWIN1250","171":"CL8MSWIN1251","172":"ET8MSWIN923","173":"BG8MSWIN","174":"EL8MSWIN1253",
"175":"IW8MSWIN1255","176":"LT8MSWIN921","177":"TR8MSWIN1254","178":"WE8MSWIN1252","179":"BLT8MSWIN1257",
"180":"D8EBCDIC273","181":"I8EBCDIC280","182":"DK8EBCDIC277","183":"S8EBCDIC278","184":"EE8EBCDIC870",
"185":"CL8EBCDIC1025","186":"F8EBCDIC297","187":"IW8EBCDIC1086","188":"CL8EBCDIC1025X","189":"D8EBCDIC1141",
"190":"N8PC865","191":"BLT8CP921","192":"LV8PC1117","193":"LV8PC8LR","194":"BLT8EBCDIC1112",
"195":"LV8RST104090","196":"CL8KOI8R","197":"BLT8PC775","198":"DK8EBCDIC1142","199":"S8EBCDIC1143",
"200":"I8EBCDIC1144","201":"F7SIEMENS9780X","202":"E7SIEMENS9780X","203":"S7SIEMENS9780X","204":"DK7SIEMENS9780X",
"205":"N7SIEMENS9780X","206":"I7SIEMENS9780X","207":"D7SIEMENS9780X","208":"F8EBCDIC1147","210":"WE8GCOS7",
"211":"EL8GCOS7","221":"US8BS2000","222":"D8BS2000","223":"F8BS2000","224":"E8BS2000",
"225":"DK8BS2000","226":"S8BS2000","230":"WE8BS2000E","231":"WE8BS2000","232":"EE8BS2000",
"233":"CE8BS2000","235":"CL8BS2000","239":"WE8BS2000L5","241":"WE8DG","251":"WE8NCR4970",
"261":"WE8ROMAN8","262":"EE8MACCE","263":"EE8MACCROATIAN","264":"TR8MACTURKISH","265":"IS8MACICELANDIC",
"266":"EL8MACGREEK","267":"IW8MACHEBREW","277":"US8ICL","278":"WE8ICL","279":"WE8ISOICLUK",
"301":"EE8EBCDIC870C","311":"EL8EBCDIC875S","312":"TR8EBCDIC1026S","314":"BLT8EBCDIC1112S","315":"IW8EBCDIC424S",
"316":"EE8EBCDIC870S","317":"CL8EBCDIC1025S","319":"TH8TISEBCDICS","320":"AR8EBCDIC420S","322":"CL8EBCDIC1025C",
"323":"CL8EBCDIC1025R","324":"EL8EBCDIC875R","325":"CL8EBCDIC1158","326":"CL8EBCDIC1158R","327":"EL8EBCDIC423R",
"351":"WE8MACROMAN8","352":"WE8MACROMAN8S","353":"TH8MACTHAI","354":"TH8MACTHAIS","368":"HU8CWI2",
"380":"EL8PC437S","381":"EL8EBCDIC875","382":"EL8PC737","383":"LT8PC772","384":"LT8PC774",
"385":"EL8PC869","386":"EL8PC851","390":"CDN8PC863","401":"HU8ABMOD","500":"AR8ASMO8X",
"504":"AR8NAFITHA711T","505":"AR8SAKHR707T","506":"AR8MUSSAD768T","507":"AR8ADOS710T","508":"AR8ADOS720T",
"509":"AR8APTEC715T","511":"AR8NAFITHA721T","514":"AR8HPARABIC8T","554":"AR8NAFITHA711","555":"AR8SAKHR707",
"556":"AR8MUSSAD768","557":"AR8ADOS710","558":"AR8ADOS720","559":"AR8APTEC715","560":"AR8MSWIN1256",
"561":"AR8NAFITHA721","563":"AR8SAKHR706","565":"AR8ARABICMAC","566":"AR8ARABICMACS","567":"AR8ARABICMACT",
"590":"LA8ISO6937","798":"WE8DECTST","829":"JA16VMS","830":"JA16EUC","831":"JA16EUCYEN",
"832":"JA16SJIS","833":"JA16DBCS","834":"JA16SJISYEN","835":"JA16EBCDIC930","836":"JA16MACSJIS",
"837":"JA16EUCTILDE","838":"JA16SJISTILDE","840":"KO16KSC5601","842":"KO16DBCS","845":"KO16KSCCS",
"846":"KO16MSWIN949","850":"ZHS16CGB231280","851":"ZHS16MACCGB231280","852":"ZHS16GBK","853":"ZHS16DBCS",
"854":"ZHS32GB18030","860":"ZHT32EUC","861":"ZHT32SOPS","862":"ZHT16DBT","863":"ZHT32TRIS",
"864":"ZHT16DBCS","865":"ZHT16BIG5","866":"ZHT16CCDC","867":"ZHT16MSWIN950","868":"ZHT16HKSCS",
"870":"AL24UTFFSS","871":"UTF8","872":"UTFE","873":"AL32UTF8","992":"ZHT16HKSCS31",
"993":"ZHT32EUCTST","994":"WE16DECTST2","995":"WE16DECTST","996":"KO16TSTSET","997":"JA16TSTSET2",
"998":"JA16TSTSET","1000":"UTF16","1001":"US16TSTFIXED","1002":"TIMESTEN8","1830":"JA16EUCFIXED",
"1832":"JA16SJISFIXED","1833":"JA16DBCSFIXED","1840":"KO16KSC5601FIXED","1842":"KO16DBCSFIXED","1850":"ZHS16CGB231280FIXED",
"1852":"ZHS16GBKFIXED","1853":"ZHS16DBCSFIXED","1860":"ZHT32EUCFIXED","1863":"ZHT32TRISFIXED","1864":"ZHT16DBCSFIXED",
"1865":"ZHT16BIG5FIXED","2000":"AL16UTF16","2002":"AL16UTF16LE"
}'::json->>$1::text)::text;
$$;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('pg_catalog.nls_charset_name(873)', '''AL32UTF8''') into l_result;
-- =========================================================================
end if;
end if;
-- =========================================================================
-- =========================================================================
-- nls_charset_id(text)
-- =========================================================================
if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '2.0.0') = 1
then
if compat_tools.drop_compat_function('function', 'nls_charset_id(text)', '1.0')
then
create or replace function pg_catalog.nls_charset_id(text)
RETURNS integer
LANGUAGE sql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
select ('{
"US7ASCII":1,"WE8DEC":2,"WE8HP":3,"US8PC437":4,"WE8EBCDIC37":5,
"WE8EBCDIC500":6,"WE8EBCDIC1140":7,"WE8EBCDIC285":8,"WE8EBCDIC1146":9,"WE8PC850":10,
"D7DEC":11,"F7DEC":12,"S7DEC":13,"E7DEC":14,"SF7ASCII":15,
"NDK7DEC":16,"I7DEC":17,"NL7DEC":18,"CH7DEC":19,"YUG7ASCII":20,
"SF7DEC":21,"TR7DEC":22,"IW7IS960":23,"IN8ISCII":25,"WE8EBCDIC1148":27,
"WE8PC858":28,"WE8ISO8859P1":31,"EE8ISO8859P2":32,"SE8ISO8859P3":33,"NEE8ISO8859P4":34,
"CL8ISO8859P5":35,"AR8ISO8859P6":36,"EL8ISO8859P7":37,"IW8ISO8859P8":38,"WE8ISO8859P9":39,
"NE8ISO8859P10":40,"TH8TISASCII":41,"TH8TISEBCDIC":42,"BN8BSCII":43,"VN8VN3":44,
"VN8MSWIN1258":45,"WE8ISO8859P15":46,"BLT8ISO8859P13":47,"CEL8ISO8859P14":48,"CL8ISOIR111":49,
"WE8NEXTSTEP":50,"CL8KOI8U":51,"AZ8ISO8859P9E":52,"AR8ASMO708PLUS":61,"AR8EBCDICX":70,
"AR8XBASIC":72,"EL8DEC":81,"TR8DEC":82,"WE8EBCDIC37C":90,"WE8EBCDIC500C":91,
"IW8EBCDIC424":92,"TR8EBCDIC1026":93,"WE8EBCDIC871":94,"WE8EBCDIC284":95,"WE8EBCDIC1047":96,
"WE8EBCDIC1140C":97,"WE8EBCDIC1145":98,"WE8EBCDIC1148C":99,"WE8EBCDIC1047E":100,"WE8EBCDIC924":101,
"EEC8EUROASCI":110,"EEC8EUROPA3":113,"LA8PASSPORT":114,"BG8PC437S":140,"EE8PC852":150,
"RU8PC866":152,"RU8BESTA":153,"IW8PC1507":154,"RU8PC855":155,"TR8PC857":156,
"CL8MACCYRILLIC":158,"CL8MACCYRILLICS":159,"WE8PC860":160,"IS8PC861":161,"EE8MACCES":162,
"EE8MACCROATIANS":163,"TR8MACTURKISHS":164,"IS8MACICELANDICS":165,"EL8MACGREEKS":166,"IW8MACHEBREWS":167,
"EE8MSWIN1250":170,"CL8MSWIN1251":171,"ET8MSWIN923":172,"BG8MSWIN":173,"EL8MSWIN1253":174,
"IW8MSWIN1255":175,"LT8MSWIN921":176,"TR8MSWIN1254":177,"WE8MSWIN1252":178,"BLT8MSWIN1257":179,
"D8EBCDIC273":180,"I8EBCDIC280":181,"DK8EBCDIC277":182,"S8EBCDIC278":183,"EE8EBCDIC870":184,
"CL8EBCDIC1025":185,"F8EBCDIC297":186,"IW8EBCDIC1086":187,"CL8EBCDIC1025X":188,"D8EBCDIC1141":189,
"N8PC865":190,"BLT8CP921":191,"LV8PC1117":192,"LV8PC8LR":193,"BLT8EBCDIC1112":194,
"LV8RST104090":195,"CL8KOI8R":196,"BLT8PC775":197,"DK8EBCDIC1142":198,"S8EBCDIC1143":199,
"I8EBCDIC1144":200,"F7SIEMENS9780X":201,"E7SIEMENS9780X":202,"S7SIEMENS9780X":203,"DK7SIEMENS9780X":204,
"N7SIEMENS9780X":205,"I7SIEMENS9780X":206,"D7SIEMENS9780X":207,"F8EBCDIC1147":208,"WE8GCOS7":210,
"EL8GCOS7":211,"US8BS2000":221,"D8BS2000":222,"F8BS2000":223,"E8BS2000":224,
"DK8BS2000":225,"S8BS2000":226,"WE8BS2000E":230,"WE8BS2000":231,"EE8BS2000":232,
"CE8BS2000":233,"CL8BS2000":235,"WE8BS2000L5":239,"WE8DG":241,"WE8NCR4970":251,
"WE8ROMAN8":261,"EE8MACCE":262,"EE8MACCROATIAN":263,"TR8MACTURKISH":264,"IS8MACICELANDIC":265,
"EL8MACGREEK":266,"IW8MACHEBREW":267,"US8ICL":277,"WE8ICL":278,"WE8ISOICLUK":279,
"EE8EBCDIC870C":301,"EL8EBCDIC875S":311,"TR8EBCDIC1026S":312,"BLT8EBCDIC1112S":314,"IW8EBCDIC424S":315,
"EE8EBCDIC870S":316,"CL8EBCDIC1025S":317,"TH8TISEBCDICS":319,"AR8EBCDIC420S":320,"CL8EBCDIC1025C":322,
"CL8EBCDIC1025R":323,"EL8EBCDIC875R":324,"CL8EBCDIC1158":325,"CL8EBCDIC1158R":326,"EL8EBCDIC423R":327,
"WE8MACROMAN8":351,"WE8MACROMAN8S":352,"TH8MACTHAI":353,"TH8MACTHAIS":354,"HU8CWI2":368,
"EL8PC437S":380,"EL8EBCDIC875":381,"EL8PC737":382,"LT8PC772":383,"LT8PC774":384,
"EL8PC869":385,"EL8PC851":386,"CDN8PC863":390,"HU8ABMOD":401,"AR8ASMO8X":500,
"AR8NAFITHA711T":504,"AR8SAKHR707T":505,"AR8MUSSAD768T":506,"AR8ADOS710T":507,"AR8ADOS720T":508,
"AR8APTEC715T":509,"AR8NAFITHA721T":511,"AR8HPARABIC8T":514,"AR8NAFITHA711":554,"AR8SAKHR707":555,
"AR8MUSSAD768":556,"AR8ADOS710":557,"AR8ADOS720":558,"AR8APTEC715":559,"AR8MSWIN1256":560,
"AR8NAFITHA721":561,"AR8SAKHR706":563,"AR8ARABICMAC":565,"AR8ARABICMACS":566,"AR8ARABICMACT":567,
"LA8ISO6937":590,"WE8DECTST":798,"JA16VMS":829,"JA16EUC":830,"JA16EUCYEN":831,
"JA16SJIS":832,"JA16DBCS":833,"JA16SJISYEN":834,"JA16EBCDIC930":835,"JA16MACSJIS":836,
"JA16EUCTILDE":837,"JA16SJISTILDE":838,"KO16KSC5601":840,"KO16DBCS":842,"KO16KSCCS":845,
"KO16MSWIN949":846,"ZHS16CGB231280":850,"ZHS16MACCGB231280":851,"ZHS16GBK":852,"ZHS16DBCS":853,
"ZHS32GB18030":854,"ZHT32EUC":860,"ZHT32SOPS":861,"ZHT16DBT":862,"ZHT32TRIS":863,
"ZHT16DBCS":864,"ZHT16BIG5":865,"ZHT16CCDC":866,"ZHT16MSWIN950":867,"ZHT16HKSCS":868,
"AL24UTFFSS":870,"UTF8":871,"UTFE":872,"AL32UTF8":873,"ZHT16HKSCS31":992,
"ZHT32EUCTST":993,"WE16DECTST2":994,"WE16DECTST":995,"KO16TSTSET":996,"JA16TSTSET2":997,
"JA16TSTSET":998,"UTF16":1000,"US16TSTFIXED":1001,"TIMESTEN8":1002,"JA16EUCFIXED":1830,
"JA16SJISFIXED":1832,"JA16DBCSFIXED":1833,"KO16KSC5601FIXED":1840,"KO16DBCSFIXED":1842,"ZHS16CGB231280FIXED":1850,
"ZHS16GBKFIXED":1852,"ZHS16DBCSFIXED":1853,"ZHT32EUCFIXED":1860,"ZHT32TRISFIXED":1863,"ZHT16DBCSFIXED":1864,
"ZHT16BIG5FIXED":1865,"AL16UTF16":2000,"AL16UTF16LE":2002
}'::json->>$1)::int;
$$;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('pg_catalog.nls_charset_id(''AL32UTF8'')', '873') into l_result;
-- =========================================================================
end if;
end if;
-- =========================================================================
-- =========================================================================
-- empty_clob()
-- 1.1 版本 可返回0长度字符串
-- =========================================================================
if compat_tools.drop_compat_function('function', 'empty_clob()', '1.2')
then
create or replace function pg_catalog.empty_clob() returns clob
IMMUTABLE STRICT
language sql
as $$select encode('\x'::bytea,'hex')::clob;$$;
end if;
-- =========================================================================
-- =========================================================================
-- sys_extract_utc(timestamptz)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'sys_extract_utc(timestamptz)', '1.0')
then
create function pg_catalog.sys_extract_utc(timestamptz) returns timestamp
LANGUAGE sql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
select $1 at time zone 'UTC';
$$;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('sys_extract_utc(current_timestamp) at time zone ''UTC''', 'current_timestamp') into l_result;
select compat_tools.f_unit_test('((sys_extract_utc(current_timestamp::TIMESTAMP) at time zone ''UTC'')-current_timestamp)::numeric', '0','=') into l_result;
-- =========================================================================
-- =========================================================================
-- new_time(timestamp,text,text)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'new_time(timestamp,text,text)', '1.0')
then
create function pg_catalog.new_time(timestamp,text,text) returns timestamp
LANGUAGE sql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
select $1 at time zone $2 at time zone $3;
$$;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('to_char(NEW_TIME(TO_DATE(''11-10-09 01:23:45'', ''MM-DD-YY HH24:MI:SS''), ''AST'', ''PST''),''yyyy-mm-dd hh24:mi:ss'')', '''2009-11-09 21:23:45''','=','A') into l_result;
-- =========================================================================
-- =========================================================================
-- lower(anyelement)
-- lower(bpchar)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'lower(anyelement)', '1.0')
then
create or replace function pg_catalog.lower(anyelement) returns text IMMUTABLE STRICT as $$ select pg_catalog.lower($1::text) $$ language sql;
end if;
if compat_tools.drop_compat_function('function', 'lower(bpchar)', '1.0')
then
create or replace function pg_catalog.lower(bpchar) returns bpchar IMMUTABLE STRICT as $$ select pg_catalog.lower($1::text)::bpchar $$ language sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('lower(''Test'')', '''test''') into l_result;
select compat_tools.f_unit_test('lower(''test''::text)', '''test''') into l_result;
select compat_tools.f_unit_test('lower(''TesT''::cstring)', '''test''') into l_result;
select compat_tools.f_unit_test('lower(''TesT''::bpchar(10))=''test''::bpchar(6)', '''true''') into l_result;
select compat_tools.f_unit_test('lower(''TesT''::bpchar(10))=lower(''test''::bpchar(6))', '''true''') into l_result;
-- =========================================================================
-- =========================================================================
-- upper(bpchar)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'upper(bpchar)', '1.0')
then
create or replace function pg_catalog.upper(bpchar) returns bpchar IMMUTABLE STRICT as $$ select pg_catalog.upper($1::text)::bpchar $$ language sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('upper(''TesT''::bpchar(10))=''TEST''::bpchar(6)', '''true''') into l_result;
select compat_tools.f_unit_test('upper(''TesT''::bpchar(10))=upper(''TEST''::bpchar(6))', '''true''') into l_result;
-- =========================================================================
-- =========================================================================
-- substr(anyelement,int8)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'substr(anyelement,int8)', '1.0')
then
create or replace function pg_catalog.substr(anyelement,int8) returns text IMMUTABLE STRICT as $$ select pg_catalog.substr($1::text, $2::int) $$ language sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('substr(''Test_str'', 2)', '''est_str''') into l_result;
select compat_tools.f_unit_test('substr(''Test_str''::text, 4)', '''t_str''') into l_result;
select compat_tools.f_unit_test('substr(''Test_str''::cstring, 6)', '''str''') into l_result;
select compat_tools.f_unit_test('substr(''Test_str'', 2::bigint)', '''est_str''') into l_result;
select compat_tools.f_unit_test('substr(''Test_str''::text, 4::bigint)', '''t_str''') into l_result;
select compat_tools.f_unit_test('substr(''Test_str''::cstring, 6::bigint)', '''str''') into l_result;
select compat_tools.f_unit_test('substr(''Test_str''::cstring, 2.2)', '''est_str''') into l_result;
-- =========================================================================
-- =========================================================================
-- substr(anyelement,int8,int8)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'substr(anyelement,int8,int8)', '1.0')
then
create or replace function pg_catalog.substr(anyelement,int8,int8) returns text IMMUTABLE STRICT as $$ select pg_catalog.substr($1::text, $2::int, $3::int) $$ language sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('substr(''Test_str'', 2, 6)', '''est_st''') into l_result;
select compat_tools.f_unit_test('substr(''Test_str''::text, 4, 2)', '''t_''') into l_result;
select compat_tools.f_unit_test('substr(''Test_str''::cstring, 6, 1)', '''s''') into l_result;
select compat_tools.f_unit_test('substr(''Test_str'', 2::bigint, 6)', '''est_st''') into l_result;
select compat_tools.f_unit_test('substr(''Test_str''::text, 4, 2::bigint)', '''t_''') into l_result;
select compat_tools.f_unit_test('substr(''Test_str''::cstring, 6::bigint, 1::bigint)', '''s''') into l_result;
select compat_tools.f_unit_test('substr(''Test_str''::cstring, 2.2, 2.2::bigint)', '''es''') into l_result;
-- =========================================================================
-- =========================================================================
-- add_months(timestamp,int8)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'add_months(timestamp,int8)', '1.0')
then
CREATE OR REPLACE FUNCTION pg_catalog.add_months (timestamp, bigint)
RETURNS timestamp
LANGUAGE sql
IMMUTABLE STRICT NOT FENCED NOT SHIPPABLE
AS $function$ select pg_catalog.add_months($1, $2::int) $function$;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('add_months(''2000-01-31''::timestamp, 1)::text', '''2000-02-29 00:00:00''') into l_result;
select compat_tools.f_unit_test('add_months(''2004-01-31''::timestamp, 3)::text', '''2004-04-30 00:00:00''') into l_result;
select compat_tools.f_unit_test('add_months(''2005-01-31''::timestamp, 1)::text', '''2005-02-28 00:00:00''') into l_result;
select compat_tools.f_unit_test('add_months(''2005-01-03''::timestamp, 14)::text', '''2006-03-03 00:00:00''') into l_result;
select compat_tools.f_unit_test('add_months(''2000-01-31''::timestamp, 1::bigint)::text', '''2000-02-29 00:00:00''') into l_result;
select compat_tools.f_unit_test('add_months(''2004-01-31''::timestamp, 3::bigint)::text', '''2004-04-30 00:00:00''') into l_result;
select compat_tools.f_unit_test('add_months(''2005-01-31''::timestamp, 1::bigint)::text', '''2005-02-28 00:00:00''') into l_result;
select compat_tools.f_unit_test('add_months(''2005-01-03''::timestamp, 14::bigint)::text', '''2006-03-03 00:00:00''') into l_result;
-- =========================================================================
-- =========================================================================
-- add_months(timestamptz,int8)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'add_months(timestamptz,int8)', '1.0')
then
CREATE OR REPLACE FUNCTION pg_catalog.add_months (timestamptz, bigint)
RETURNS timestamptz
LANGUAGE sql
IMMUTABLE STRICT NOT FENCED NOT SHIPPABLE
AS $function$ select pg_catalog.add_months($1::timestamp, $2::int)::timestamptz $function$;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('add_months(''2000-01-31''::timestamptz, 1)::text', '''2000-02-29 00:00:00+00''') into l_result;
select compat_tools.f_unit_test('add_months(''2004-01-31''::timestamptz, 3)::text', '''2004-04-30 00:00:00+00''') into l_result;
select compat_tools.f_unit_test('add_months(''2005-01-31''::timestamptz, 1)::text', '''2005-02-28 00:00:00+00''') into l_result;
select compat_tools.f_unit_test('add_months(''2005-01-03''::timestamptz, 14)::text', '''2006-03-03 00:00:00+00''') into l_result;
select compat_tools.f_unit_test('add_months(''2000-01-31''::timestamptz, 1::bigint)::text', '''2000-02-29 00:00:00+00''') into l_result;
select compat_tools.f_unit_test('add_months(''2004-01-31''::timestamptz, 3::bigint)::text', '''2004-04-30 00:00:00+00''') into l_result;
select compat_tools.f_unit_test('add_months(''2005-01-31''::timestamptz, 1::bigint)::text', '''2005-02-28 00:00:00+00''') into l_result;
select compat_tools.f_unit_test('add_months(''2005-01-03''::timestamptz, 14::bigint)::text', '''2006-03-03 00:00:00+00''') into l_result;
-- =========================================================================
-- =========================================================================
-- to_timestamp(text,text,text)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'to_timestamp(text,text,text)', '1.0')
then
create function pg_catalog.to_timestamp(text,text,text) returns timestamp as $$ select pg_catalog.to_timestamp($1::text, $2::text) $$ language sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('to_timestamp(''2000-01-31'', ''YYYY-MM-DD'')::text', '''2000-01-31 00:00:00''') into l_result;
select compat_tools.f_unit_test('to_timestamp(''2000-01-31'', ''YYYY-MM-DD'', ''NLS_LANG=AMERICAN_AMERICA'')::text', '''2000-01-31 00:00:00''') into l_result;
-- =========================================================================
-- =========================================================================
-- rawtohex(anyelement)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'rawtohex(anyelement)', '1.0')
then
create function pg_catalog.rawtohex(anyelement) returns text as $$ select pg_catalog.rawtohex($1::text) $$ language sql;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('rawtohex(''HongyeDBA'')', '''486f6e677965444241''') into l_result;
select compat_tools.f_unit_test('rawtohex(''HongyeDBA''::cstring)', '''486f6e677965444241''') into l_result;
-- =========================================================================
-- =========================================================================
-- to_char(timestamp without time zone,text,text)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'to_char(timestamp without time zone,text,text)', '1.0')
then
create or replace function pg_catalog.to_char(timestamp without time zone, text, text)
RETURNS text
LANGUAGE SQL
STABLE STRICT NOT FENCED SHIPPABLE
as $function$ select to_char($1, $2) $function$;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('to_char(''2000-01-31 12:12:12''::timestamp, ''yyyy-mm-dd'')', '''2000-01-31''') into l_result;
select compat_tools.f_unit_test('to_char(''2000-12-12''::timestamp, ''yyyy-mm-dd'', ''NLS_DATE_LANGUAGE=AMERICAN'')', '''2000-12-12''') into l_result;
-- =========================================================================
-- =========================================================================
-- to_char(timestamptz,text,text)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'to_char(timestamptz,text,text)', '1.0')
then
create or replace function pg_catalog.to_char(timestamptz, text, text)
RETURNS text
LANGUAGE SQL
STABLE STRICT NOT FENCED SHIPPABLE
as $function$ select to_char($1, $2) $function$;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('to_char(''2000-01-31 12:12:12''::timestamptz, ''yyyy-mm-dd'')', '''2000-01-31''') into l_result;
select compat_tools.f_unit_test('to_char(''2000-12-12''::timestamptz, ''yyyy-mm-dd'', ''NLS_DATE_LANGUAGE=AMERICAN'')', '''2000-12-12''') into l_result;
-- =========================================================================
-- =========================================================================
-- to_lob(text)
-- =========================================================================
if compat_tools.drop_compat_function('function', 'to_lob(text)', '1.0')
then
create or replace function pg_catalog.to_lob(text) returns clob
IMMUTABLE STRICT
language sql
as $$select $1::clob;$$;
end if;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('to_lob(''abcd'')', '''abcd''') 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
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。
马建仓 AI 助手