代码拉取完成，页面将自动刷新
同步操作将从 enmotech/compat-tools 强制同步，此操作会覆盖自 Fork 仓库以来所做的任何修改，且无法恢复！！！
确定后同步将在后台操作，完成时将刷新页面，请耐心等待。
-- This scripts contains following pcakage's definition:
-- =============================================================================
-- dbms_metadata F get_ddl(varchar,varchar,varchar,varchar,varchar,varchar,bool)
-- dbms_output P disable()
-- P enable(int4)
-- P new_line()
-- P put(text)
-- P put_line(text)
-- P get_line(text,int4)
-- P get_lines(text[],int4)
-- dbms_random P initialize(int4) -- deprecated
-- F normal()
-- F random() -- deprecated
-- P seed(int4)
-- P seed(text)
-- F string(text,int4)
-- P terminate() -- deprecated
-- F value()
-- F value(numeric,numeric)
-- dbms_job P broken(int8,bool,timestamp)
-- P change(int8,text,timestamp,text,int4,bool)
-- P instance(int8,int4,bool)
-- P interval(int8,text)
-- P next_date(int8,timestamp)
-- P remove(int8)
-- P run(int8,bool)
-- P submit(int8,text,timestamp,text,bool,int4,bool)
-- P user_export(int8,text)
-- P what(int8,text)
-- dbms_lock P sleep(float8)
-- dbms_application_info P read_client_info(text)
-- P read_module(text,text)
-- P set_client_info(text)
-- P set_module(text,text)
-- dbms_utility P canonicalize(text)
-- P comma_to_table(text,int4,text[])
-- P table_to_comma(text[],int4,text)
-- P db_version(text,text)
-- P exec_ddl_statement(text)
-- dbms_obfuscation_toolkit F md5(text)
-- F md5(raw) @todo
-- utl_url F escape(text,bool,text)
-- F unescape(text,text)
-- utl_encode F base64_encode(raw)
-- F base64_decode(raw)
-- F text_encode(text,text,int4)
-- F text_decode(text,text,int4)
-- F quoted_printable_encode(raw)
-- F quoted_printable_decode(raw)
-- F mimeheader_encode(text,text,int4)
-- F mimeheader_decode(text)
-- F uuencode(raw,int1,text,text)
-- F uudecode(raw)
-- utl_raw F cast_to_varchar2
-- F cast_to_raw(text)
-- F concat(raw,raw,raw,raw,raw,raw,raw,raw,raw,raw,raw,raw)
-- F length(raw)
-- F substr(raw,int4,int4)
-- F transliterate(raw,raw,raw,raw)
-- F translate(raw,raw,raw)
-- F copies(raw,int8)
-- F overlay(raw,raw,int4,int4,raw)
-- F xrange(raw,raw)
-- F reverse(raw)
-- F compare(raw,raw,raw)
-- F convert(raw,text,text)
-- F bit_and(raw,raw)
-- F bit_or(raw,raw)
-- F bit_xor(raw,raw)
-- F bit_complement(raw)
-- F cast_to_number(raw)
-- F cast_from_number(numeric)
-- F cast_to_binary_integer(raw,int1)
-- F cast_from_binary_integer(int4,int1)
-- dbms_lob F getlength(blob)
-- F getlength(clob)
-- P open(blob,int)
-- P open(clob,int)
-- P freetemporary(blob)
-- P freetemporary(clob)
-- F substr(blob,int4,int4)
-- F substr(clob,int4,int4)
-- F instr(blob,raw,int4,int4)
-- F instr(clob,text,int4,int4)
-- P createtemporary(blob,bool,int4)
-- P createtemporary(clob,bool,int4)
-- P close(blob)
-- P close(clob)
-- P append(blob,blob)
-- P append(clob,clob)
-- F compare(blob,blob,int4,int4,int4)
-- F compare(clob,clob,int4,int4,int4)
-- P copy(blob,blob,int4,int4,int4)
-- P copy(clob,clob,int4,int4,int4)
-- P erase(blob,int4,int4)
-- P erase(clob,int4,int4)
-- P read(blob,int4,int4,raw)
-- P read(clob,int4,int4,text)
-- P trim(blob,int4)
-- P trim(clob,int4)
-- P write(blob,int4,int4,raw)
-- P write(clob,int4,int4,text)
-- P writeappend(blob,int4,raw)
-- P writeappend(clob,int4,text)
-- dbms_snapshot P refresh(text,text)
-- -----------------------------------------------------------------------------
-- p expand_sql_text @todo
-- p get_dependency @todo
-- F get_endianness @todo
-- F get_hash_value @todo
-- F get_parameter_value @todo
-- F get_sql_hash @todo
-- F get_time @todo
-- P get_tz_transitions @todo
-- F old_current_schema @todo
-- F old_current_user @todo
-- F port_string @todo
-- dbms_lob P converttoblob @todo
-- P converttoclob @todo
-- P loadblobfromfile @todo
-- P loadclobfromfile @todo
-- =============================================================================
set client_min_messages='warning';
set time zone 'UTC';
set statement_timeout = 60000;
-- =============================================================================
-- Create Schema (compat_tools)
-- =============================================================================
do $$
declare
l_cnt bigint;
begin
select count(*) into l_cnt from pg_catalog.pg_namespace where nspname = 'compat_tools';
if l_cnt = 0
then
execute '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_package(varchar, varchar, varchar, varchar, varchar);
create or replace function compat_tools.drop_compat_package( p_object_type varchar(10)
, p_package_name varchar(128)
, p_object_name varchar(128)
, p_object_version varchar(10)
, p_function_lang varchar(16) default 'plpgsql')
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)
) ON COMMIT PRESERVE ROWS;
-- 字符串参数统一转小写
p_object_type := lower(p_object_type);
p_package_name := lower(p_package_name);
p_object_name := lower(p_object_name);
p_function_lang := lower(p_function_lang);
select max(object_version) into l_version
from compat_tools.compat_version
where object_name = p_package_name||'.'||p_object_name
and compat_type = p_object_type;
-- 若指定的 Schema 不存在 (对应 Oracle 中的 Package Name)，则自动创建
select count(*) into l_depend_cnt from pg_catalog.pg_namespace where nspname = p_package_name;
if l_depend_cnt = 0
then
execute 'create schema '||p_package_name;
end if;
-- 获取已有同名同参数函数/存储过程的语言，存入 l_language 变量
select max(language) into l_language
from compat_tools.pg_function_list
where schema_name = p_package_name
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_package_name||'.'||p_object_name;
if l_language is null -- 系统中没有对应函数，必定创建
then
l_operation := 'Initial creation';
elsif l_language != p_function_lang -- 系统中函数语言与脚本中定义语言不一致，必须忽略
then
l_result = 'false';
l_operation := 'Skip due to language';
elsif l_version >= p_object_version -- 系统版本表中版本不低于脚本中的版本号，必须忽略
then
l_result = 'false';
l_operation := 'Skip due to version';
elsif compat_tools.check_extension_conflict(p_package_name||'.'||p_object_name) -- 系统中已有extension创建同名对象
then
l_result='false';
l_operation= 'Skip due to extension';
else
if l_version is null -- 版本表中无记录，可能是未创建，或者版本记录被误删除，尝试创建
then
l_operation := 'Initial creation (Ver)';
else -- 其他情况
l_operation := 'Upgrade';
end if;
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_package_name||'"."'||replace(p_object_name, '(', '"(')
|| (case when p_object_type = 'FUNCTION' then ' cascade' else '' end);
else
execute 'drop '||p_object_type||' if exists "'
|| p_package_name||'"."'||p_object_name||'"'
|| (case when p_object_type = 'FUNCTION' then ' cascade' else '' end);
end if;
insert into compat_tools.compat_version
values ( p_object_type
, p_package_name||'.'||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_package_name||'.'||p_object_name
, p_object_version)
ON DUPLICATE KEY UPDATE NOTHING;
end if;
end if;
-- 插入本次临时结果表
insert into temp_result values ( p_object_type
, p_package_name||'.'||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;
begin
if current_setting('application_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') into l_result;
-- select compat_tools.f_unit_test('f_version_compare (''2.1.1'', ''2.0.0'')', '1') into l_result;
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3'', ''1.2.3'')', '0') into l_result;
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3'', ''1.12.3'')', '-1') into l_result;
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3.4'', ''1.2.3'')', '1') into l_result;
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3'', ''1.2.3.4'')', '-1') into l_result;
-- select compat_tools.f_unit_test('f_version_compare (''1.2...3'', ''1.2.3.4'')', 'NULL', 'IS') into l_result;
-- select compat_tools.f_unit_test('f_version_compare (null, ''1.2.3.4'')', 'NULL', 'IS') into l_result;
-- select * from compat_tools.compat_testing where test_expr like 'f_version_compare%';
-- =========================================================================
-- =============================================================================
-- All creations are running in ANONYMOUS BLOCK
-- =============================================================================
do $PKG_CREATION$
declare
l_cnt bigint;
l_result text;
begin
-- =========================================================================
-- dbms_metadata.get_ddl(varchar,varchar,varchar,varchar,varchar,varchar,bool)
-- =========================================================================
-- 变更历史：
-- 2022-05-06 [2.0] 增加 package, package_body 类型对象的 DDL 语句获取
-- 2022-05-06 [2.0] 增加对 2.1 以及之后版本中的 large sequence 的支持
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'dbms_metadata', 'get_ddl(varchar,varchar,varchar,varchar,varchar,varchar,bool)', '2.0')
then
CREATE or replace FUNCTION dbms_metadata.get_ddl( object_type VARCHAR
, name VARCHAR
, schema VARCHAR DEFAULT current_schema()
, version VARCHAR DEFAULT 'COMPATIBLE' -- 暂不使用
, model VARCHAR DEFAULT 'ORACLE' -- 暂不使用
, transform VARCHAR DEFAULT 'DDL' -- 暂不使用
, debug BOOLEAN DEFAULT 'false')
RETURNS text IMMUTABLE AS $$
declare
l_oid text;
l_temp_result text;
l_temp_record record;
l_ddl text;
begin
-- 纯大写的参数转为全小写
if upper(object_type) = object_type
then
object_type := lower(object_type);
end if;
if upper(name) = name
then
name := lower(name);
end if;
if upper(schema) = schema
then
schema := lower(schema);
end if;
-- 按照对象类型生成 DDL
begin
if object_type = 'table'
then
select pg_get_tabledef(oid) into l_ddl
from pg_class
where relname = name
and relnamespace = (select oid
from pg_namespace
where nspname = schema)
and relkind = 'r';
elsif object_type = 'view'
then
select 'CREATE OR REPLACE VIEW "'||schema||'"."'||name||'" as '
|| pg_get_viewdef(oid, true) into l_ddl
from pg_class
where relname = name
and relnamespace = (select oid
from pg_namespace
where nspname = schema)
and relkind = 'v';
elsif object_type = 'index'
then
select pg_get_indexdef(oid, 0, true) into l_ddl
from pg_class
where relname = name
and relnamespace = (select oid
from pg_namespace
where nspname = schema)
and relkind in ('i', 'I');
elsif object_type in ('constraint', 'ref_constraint')
then
select 'ALTER TABLE "'||schema||'"."'||cls.relname||'" ADD CONSTRAINT "'||name||'" '
|| pg_get_constraintdef(con.oid, true) into l_ddl
from pg_constraint con
join pg_class cls on con.conrelid = cls.oid
join pg_namespace nsp on con.connamespace = nsp.oid
where con.conname = name
and nsp.nspname = schema;
elsif object_type = 'function'
then
select (pg_get_functiondef(oid)).definition into l_ddl
from pg_proc
where proname = name
and pronamespace = (select oid
from pg_namespace
where nspname = schema)
and prokind != 'p';
elsif object_type = 'procedure'
then
select (pg_get_functiondef(oid)).definition into l_ddl
from pg_proc
where proname = name
and pronamespace = (select oid
from pg_namespace
where nspname = schema)
and prokind = 'p';
elsif object_type = 'trigger'
then
select pg_get_triggerdef(t.oid, true) into l_ddl
from pg_trigger as t
join pg_class as c on t.tgrelid = c.oid
join pg_namespace as n on c.relnamespace = n.oid
where t.tgname = name
and n.nspname = schema;
elsif object_type in ('materialized_view', 'materialized view')
then
select 'CREATE MATERIALIZED VIEW "'||schema||'"."'||name||'" as '
|| pg_get_viewdef(oid, true) into l_ddl
from pg_class
where relname = name
and relnamespace = (select oid
from pg_namespace
where nspname = schema)
and relkind = 'm';
elsif object_type = 'sequence'
then
-- 直接查询 Sequence, 会在切换查询 large sequence 与 sequence 的时候遇到如下报错：
-- NOTICE: type of parameter 29 (bigint) does not match that when preparing the plan (int16)
-- NOTICE: type of parameter 29 (int16) does not match that when preparing the plan (bigint)
for l_temp_record in execute 'select start_value::text
, increment_by::text
, max_value::text
, min_value::text
, cache_value::text
, case when max_value > 2^63 then 1 else 0 end as is_large
, is_cycled
from "'||schema||'"."'||name||'"'
-- for l_temp_record in select l.cache_value
-- , p.start_value
-- , p.minimum_value
-- , p.maximum_value
-- , p.increment
-- , p.cycle_option
-- from pg_catalog.pg_sequence_last_value(('"'||schema||'"."'||name||'"')::regclass) as l -- 2.0 版本不存在这个函数
-- join pg_catalog.pg_sequence_parameters(('"'||schema||'"."'||name||'"')::regclass) as p on 1 = 1
loop
-- 获取序列关联的 serial 字段表
select max('"'||c.relname||'"."'||a.attname||'"') into l_temp_result
from pg_catalog.pg_attrdef as d
join pg_class as c on d.adrelid = c.oid
join pg_attribute as a on d.adrelid = a.attrelid and d.adnum = a.attnum
join pg_namespace as n on c.relnamespace = n.oid
where d.adsrc = 'nextval('''||name||'''::regclass)'
and n.nspname = schema;
-- 拼接序列创建语句
l_ddl := 'CREATE '||(case l_temp_record.is_large when 1 then 'LARGE ' else '' end)||'SEQUENCE "'||schema||'"."'||name||'"'
|| ' INCREMENT BY '||l_temp_record.increment_by
|| ' MINVALUE '||l_temp_record.min_value
|| ' MAXVALUE '||l_temp_record.max_value
|| ' START WITH '|| l_temp_record.start_value
|| ' CACHE '|| l_temp_record.cache_value
|| case when l_temp_record.is_cycled then ' CYCLE' else ' NOCYCLE' end
|| case when l_temp_result is null then ';' else ' OWNED BY '||l_temp_result||';' end;
-- l_ddl := 'CREATE '||(case when l_temp_record.maximum_value > 2^63 then 'LARGE ' else '' end)||'SEQUENCE "'||schema||'"."'||name||'"'
-- || ' INCREMENT BY '||l_temp_record.increment
-- || ' MINVALUE '||l_temp_record.minimum_value
-- || ' MAXVALUE '||l_temp_record.maximum_value
-- || ' START WITH '|| l_temp_record.start_value
-- || ' CACHE '|| l_temp_record.cache_value
-- || case when l_temp_record.cycle_option then ' CYCLE' else ' NOCYCLE' end
-- || case when l_temp_result is null then ';' else ' OWNED BY '||l_temp_result||';' end;
end loop;
elsif object_type = 'synonym'
then
select 'CREATE OR REPLACE SYNONYM "'||schema||'"."'||name||'" FOR "'||synobjschema||'"."'||synobjname||'";'
into l_ddl
from pg_synonym
where synname = name
and synnamespace = (select oid
from pg_namespace
where nspname = schema);
elsif object_type = 'package'
then
select 'CREATE OR REPLACE PACKAGE "'||schema||'"."'|| name ||'"'
|| (case when pkgsecdef then ' AUTHID DEFINER' else '' end)
|| ' is '
|| replace(pkgspecsrc, 'PACKAGE DECLARE', '')
|| ' ' || name ||';' into l_ddl
from gs_package
where pkgname = name
and pkgnamespace = (select oid
from pg_namespace
where nspname = schema);
elsif object_type in ('package_body', 'package body')
then
select 'CREATE OR REPLACE PACKAGE BODY "'||schema||'"."'|| name ||'"'
|| ' is '
|| chr(10)
|| regexp_replace(pkgbodydeclsrc, '(^\s*PACKAGE\s*DECLARE|\s*END\s*$)', '', 'g')
|| chr(10)
|| (case when pkgbodyinitsrc is null then 'END' else trim(replace(pkgbodyinitsrc, 'INSTANTIATION', ''), chr(10)||' ') end)
|| ' ' || name ||';' into l_ddl
from gs_package
where pkgname = name
and pkgnamespace = (select oid
from pg_namespace
where nspname = schema)
and pkgbodydeclsrc is not null;
else
raise warning 'GET_DDL for type [%] does not supported yet', object_type;
end if;
exception
when others then
if debug
then
raise notice '%', SQLERRM; -- 调试用途
end if;
raise exception '%', initcap(replace(object_type, '_', ' '))||' "'||schema||'"."'||name||'" does not exist !';
end;
return l_ddl;
end;
$$ LANGUAGE plpgsql;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_metadata.get_ddl(varchar,varchar,varchar,varchar,varchar,varchar): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_output.enable(int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_output', 'enable(int4)', '1.0')
then
execute 'CREATE OR REPLACE PROCEDURE dbms_output.enable(buffer_size int default 2000) AS
BEGIN
-- 创建临时表，用于缓存输出结果
CREATE temporary table if not exists dbms_output_buffer
(msg_content text)
ON COMMIT PRESERVE ROWS;
INSERT INTO dbms_output_buffer values ('''');
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_output.enable(buffer_size int default 2000): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_output.disable()
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_output', 'disable()', '1.1')
then
execute 'CREATE OR REPLACE PROCEDURE dbms_output.disable() AS
BEGIN
for x in select count(*) cnt
from pg_class
where relname = ''dbms_output_buffer''
and relpersistence = ''t''
and relnamespace = pg_my_temp_schema()
having count(*) >= 1
loop
drop table dbms_output_buffer;
end loop;
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_output.disable(): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_output.get_line(text,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_output', 'get_line(text,int4)', '1.1')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_output.get_line ( line OUT text
, status OUT INTEGER) AS
DECLARE
l_cnt bigint;
BEGIN
select count(*) into l_cnt
from pg_class
where relname = ''dbms_output_buffer''
and relpersistence = ''t''
and relnamespace = pg_my_temp_schema();
if l_cnt = 0 -- 缓存数据表不存在
then
line := '''';
status := 1;
else
select case when instr(msg_content, chr(10)) = 0 then msg_content
else substr(msg_content, 1, instr(msg_content, chr(10)) - 1)
end into line
from dbms_output_buffer
limit 1;
if line is null or line = ''''
then
status := 1;
else
status := 0;
end if;
-- 丢弃剩余数据，刷新缓存
truncate table dbms_output_buffer;
insert into dbms_output_buffer values ('''');
end if;
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_output.get_line(text,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_output.get_lines(text,int4)
-- CREATE temporary table if not exists dbms_output_buffer (msg_content text) ON COMMIT PRESERVE ROWS;
-- insert into dbms_output_buffer values ('');
-- update dbms_output_buffer set msg_content = '123'||chr(10)||'abc'||chr(10)||'xxx'||chr(10);
-- select lines, lines[1], lines[2], lines[2], array_length(lines, 1), numlines from dbms_output.get_lines (2);
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_output', 'get_lines(text[],int4)', '2.1')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_output.get_lines ( lines OUT text[]
, numlines IN OUT INTEGER) AS
DECLARE
l_cnt bigint;
l_buffer text;
BEGIN
select count(*) into l_cnt
from pg_class
where relname = ''dbms_output_buffer''
and relpersistence = ''t''
and relnamespace = pg_my_temp_schema();
if l_cnt = 0 -- 缓存数据表存在
then
lines := ''{}''::text[];
numlines := 0;
else
select (string_to_array( case when instr(msg_content, chr(10), 1, numlines) = 0 then msg_content
else substr(msg_content, 1, instr(msg_content, chr(10), 1, numlines) - 1)
end
, chr(10)
)
) into lines
from dbms_output_buffer
limit 1;
numlines := coalesce(array_length(lines, 1), 0);
-- if lines is null or lines = ''''
-- then
-- numlines := 0;
-- else
-- numlines := length(lines) - length(replace(lines, chr(10), ''''));
-- -- 不是一换行结尾，行数 +1 （最后一个无换行符的行，也作为一行内容）
-- if substr(lines, length(lines), 1) != chr(10)
-- then
-- numlines := numlines + 1;
-- end if;
-- end if;
-- 丢弃剩余数据，刷新缓存
truncate table dbms_output_buffer;
insert into dbms_output_buffer values ('''');
end if;
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_output.get_lines(text[],int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_output.new_line()
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_output', 'new_line()', '1.1')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_output.new_line() AS
DECLARE
l_cnt bigint;
BEGIN
select count(*) into l_cnt
from pg_class
where relname = ''dbms_output_buffer''
and relpersistence = ''t''
and relnamespace = pg_my_temp_schema();
if l_cnt > 0
then
update dbms_output_buffer
set msg_content = msg_content||chr(10);
end if;
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_output.new_line(): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_output.put(text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_output', 'put(text)', '1.2')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_output.put(item text) AS
DECLARE
l_cnt bigint;
BEGIN
select count(*) into l_cnt
from pg_class
where relname = ''dbms_output_buffer''
and relpersistence = ''t''
and relnamespace = pg_my_temp_schema();
if l_cnt > 0
then
update dbms_output_buffer
set msg_content = msg_content||item;
end if;
raise notice ''%'', item;
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_output.put(text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_output.put_line(text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_output', 'put_line(text)', '1.2')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_output.put_line(item text) AS
DECLARE
l_cnt bigint;
BEGIN
select count(*) into l_cnt
from pg_class
where relname = ''dbms_output_buffer''
and relpersistence = ''t''
and relnamespace = pg_my_temp_schema();
if l_cnt > 0
then
update dbms_output_buffer
set msg_content = msg_content||item||chr(10);
end if;
raise notice ''%'', item;
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_output.put_line(text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_random.initialize(int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_random', 'initialize(int4)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_random.initialize(val int) AS
BEGIN
raise notice ''This procedure is deprecated with Release 11gR1, it should not be used.'';
raise notice ''Nothing will be done!'';
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_random.initialize(int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_random.normal()
-- 利用 Box-Muller 变换算法通过两个平均分布的随机数生成正态分布的随机数（效率不高）
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'dbms_random', 'normal()', '1.0', 'sql')
then
execute '
CREATE OR REPLACE FUNCTION dbms_random.normal()
returns numeric AS $$
select (sqrt(-2 * ln(random())) * cos(2 * pi() * random()))::numeric;
$$ language sql;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_random.normal(): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_random.random()
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'dbms_random', 'random()', '1.0')
then
execute '
CREATE OR REPLACE FUNCTION dbms_random.random()
returns int AS $$
BEGIN
raise notice ''This function is deprecated with Release 11gR1, although currently supported, it should not be used.'';
return case when random() >= 0.5 then power(2,31) * random() else 0 - power(2,31) * random() end;
END;
$$ language plpgsql';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_random.random(): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_random.seed(int4)
-- PACKAGE 参数表明支持重载
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_random', 'seed(int4)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_random.seed(val int) PACKAGE AS
BEGIN
setseed(val / power(2,31));
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_random.seed(int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_random.seed(text)
-- PACKAGE 参数表明支持重载
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_random', 'seed(text)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_random.seed(val text) PACKAGE AS
DECLARE
l_ascii numeric;
BEGIN
if val = '''' or val is null
then
null;
else
select sum(ascii(id)) / (127 * count(*))
into l_ascii
from unnest(string_to_array(val, '''')) as id;
if l_ascii <= 1
then
setseed(l_ascii);
end if;
end if;
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_random.seed(text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_random.string(text,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'dbms_random', 'string(text,int4)', '1.0', 'sql')
then
execute '
CREATE OR REPLACE FUNCTION dbms_random.string(opt text,len int4)
returns text AS $$
select case upper(opt)
-- 全小写随机
when ''L'' then (select string_agg(chr(id), '''') from (select 96 + ceil(26 * random()) as id from generate_series(1, len)))
-- 大小写混合随机
when ''A'' then (select string_agg((array[''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'',''J'',''K'',''L'',''M'',''N'',''O'',''P'',''Q'',''R'',''S'',''T'',''U'',''V'',''W'',''X'',''Y'',''Z'',''a'',''b'',''c'',''d'',''e'',''f'',''g'',''h'',''i'',''j'',''k'',''l'',''m'',''n'',''o'',''p'',''q'',''r'',''s'',''t'',''u'',''v'',''w'',''x'',''y'',''z''])[id], '''') from (select ceil(52 * random()) as id from generate_series(1, len)))
-- 大小写+数字混合随机
when ''X'' then (select string_agg((array[''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'',''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'',''J'',''K'',''L'',''M'',''N'',''O'',''P'',''Q'',''R'',''S'',''T'',''U'',''V'',''W'',''X'',''Y'',''Z'',''a'',''b'',''c'',''d'',''e'',''f'',''g'',''h'',''i'',''j'',''k'',''l'',''m'',''n'',''o'',''p'',''q'',''r'',''s'',''t'',''u'',''v'',''w'',''x'',''y'',''z''])[id], '''') from (select ceil(62 * random()) as id from generate_series(1, len)))
-- 全可见字符随机
when ''P'' then (select string_agg(chr(id), '''') from (select 31 + ceil(95 * random()) as id from generate_series(1, len)))
-- 全大写随机
else (select string_agg(chr(id), '''') from (select 64 + ceil(26 * random()) as id from generate_series(1, len)))
end;
$$ language sql';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_random.string(text,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_random.terminate()
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_random', 'terminate()', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_random.terminate() AS
BEGIN
raise notice ''This procedure is deprecated with Release 11gR1, it should not be used.'';
raise notice ''Nothing will be done!'';
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_random.terminate(): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_random.value()
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'dbms_random', 'value()', '1.0', 'sql')
then
execute '
CREATE OR REPLACE FUNCTION dbms_random.value()
returns numeric AS $$
select random()::numeric;
$$ language sql;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_random.value(): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_random.value(numeric,numeric)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'dbms_random', 'value(numeric,numeric)', '1.0', 'sql')
then
execute '
CREATE OR REPLACE FUNCTION dbms_random.value(low numeric, high numeric)
returns numeric AS $$
select (random() * (high - low) + low)::numeric;
$$ language sql;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_random.value(numeric,numeric): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_job.broken(int8,bool,timestamp)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'broken(int8,bool,timestamp)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_job.broken ( job int8
, broken bool
, next_date timestamp default sysdate)
AS
DECLARE
l_result text;
BEGIN
select pkg_service.job_finish(job, broken, next_date) into l_result;
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_job.broken(int8,bool,timestamp): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_job.change(int8,text,timestamp,text,int4,bool)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'change(int8,text,timestamp,text,int4,bool)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_job.change ( job int8
, what text
, next_date timestamp
, "interval" text
, instance int4 default null
, force bool default ''false'')
AS
DECLARE
l_result text;
BEGIN
if instance is not null
then
raise notice ''Parameter "instance" does nothing in this procedure !'';
end if;
if force
then
raise notice ''Parameter "force" does nothing in this procedure !'';
end if;
select pkg_service.job_update(job, next_date, interval, what) into l_result;
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_job.change(int8,text,timestamp,text,int4,bool): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_job.instance(int8,int4,bool)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'instance(int8,int4,bool)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_job.instance ( job int8
, instance int4
, force bool default ''false'')
AS
BEGIN
raise notice ''Nothing to do in this procedure !'';
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_job.instance(int8,int4,bool): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_job.interval(int8,text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'interval(int8,text)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_job.interval ( job int8
, "interval" text)
AS
DECLARE
l_next_date timestamp;
l_what text;
l_result text;
BEGIN
select next_run_date into l_next_date
from pg_job
where job_id = job;
select what into l_what
from pg_job_proc
where job_id = job;
select pkg_service.job_update(job, l_next_date, interval, l_what) into l_result;
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_job.interval(int8,text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_job.next_date(int8,timestamp)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'next_date(int8,timestamp)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_job.next_date ( job int8
, next_date timestamp)
AS
DECLARE
l_interval text;
l_what text;
l_result text;
BEGIN
select interval into l_interval
from pg_job
where job_id = job;
select what into l_what
from pg_job_proc
where job_id = job;
select pkg_service.job_update(job, next_date, l_interval, l_what) into l_result;
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_job.next_date(int8,timestamp): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_job.remove(int8)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'remove(int8)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_job.remove(job int8)
AS
DECLARE
l_result text;
BEGIN
select pkg_service.job_cancel(job) into l_result;
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_job.remove(int8): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_job.run(int8,bool)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'run(int8,bool)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_job.run( job int8
, force boolean default ''false'')
AS
DECLARE
l_interval text;
l_next_date timestamp;
l_what text;
l_result text;
BEGIN
if force
then
raise notice ''Parameter "force" does nothing in this procedure !'';
end if;
select interval into l_interval
from pg_job
where job_id = job;
select what into l_what
from pg_job_proc
where job_id = job;
execute immediate ''select ''||l_interval into l_next_date;
execute immediate l_what;
select pkg_service.job_update(job, l_next_date, l_interval, l_what) into l_result;
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_job.run(int8,bool): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_job.submit(int8,text,timestamp,text,bool,int4,bool)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'submit(int8,text,timestamp,text,bool,int4,bool)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_job.submit ( job out int8
, what in text
, next_date in timestamp default sysdate
, "interval" in text default ''null''
, no_parse in bool default ''false''
, instance in int4 default null
, force in bool default ''false'')
AS
BEGIN
if no_parse
then
raise notice ''Parameter "no_parse" does nothing in this procedure !'';
end if;
if instance is not null
then
raise notice ''Parameter "instance" does nothing in this procedure !'';
end if;
if force
then
raise notice ''Parameter "force" does nothing in this procedure !'';
end if;
job := pkg_service.job_submit(null, what, next_date, interval);
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_job.submit(int8,text,timestamp,text,bool,int4,bool): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_job.user_export(int8,text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'user_export(int8,text)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_job.user_export ( job in int8
, mycall inout text)
AS
BEGIN
select ''select pkg_service.job_submit(id=>''
|| j.job_id
|| '', content=>''
|| chr(39) || p.what || chr(39)
|| '', next_time=>to_timestamp('' || chr(39) || to_char(next_run_date, ''yyyy-mm-dd hh24:mi:ss'') || chr(39)
|| '', '' || chr(39) || ''yyyy-mm-dd hh24:mi:ss'' || chr(39)
|| ''), interval_time=>''||chr(39)||interval||chr(39)||'');''
into mycall
from pg_job as j
join pg_job_proc as p on j.job_id = p.job_id
where j.job_id = job;
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_job.user_export(int8,text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_job.what(int8,text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'what(int8,text)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_job.what ( job int8
, what text)
AS
DECLARE
l_next_date timestamp;
l_interval text;
l_result text;
BEGIN
select next_run_date, interval
into l_next_date, l_interval
from pg_job
where job_id = job;
select pkg_service.job_update(job, l_next_date, l_interval, what) into l_result;
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_job.what(int8,text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_lock.sleep(float8)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lock', 'sleep(float8)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_lock.sleep(pi_seconds float8)
AS
DECLARE
l_result text;
BEGIN
select pg_sleep(pi_seconds) into l_result;
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lock.sleep(float8): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_application_info.read_client_info(text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_application_info', 'read_client_info(text)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_application_info.read_client_info(client_info out text)
AS
BEGIN
select setting
into client_info
from pg_settings
where name = ''application_name'';
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_application_info.read_client_info(text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_application_info.set_client_info(text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_application_info', 'set_client_info(text)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_application_info.set_client_info(client_info text)
AS
BEGIN
execute immediate ''alter session set application_name = ''''''||client_info||'''''''';
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_application_info.set_client_info(text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_application_info.read_module(text,text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_application_info', 'read_module(text,text)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_application_info.read_module( module_name out text
, action_name out text)
AS
BEGIN
select setting, null
into module_name, action_name
from pg_settings
where name = ''application_name'';
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_application_info.read_module(text,text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_application_info.set_module(text,text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_application_info', 'set_module(text,text)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_application_info.set_module( module_name text
, action_name text)
AS
BEGIN
execute immediate ''alter session set application_name = ''''''||module_name||'''''''';
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_application_info.set_module(text,text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- dbms_utility.canonicalize(text,text,int4)
-- =========================================================================
-- 变更历史
-- 2022-06-10 1.1 修复双引号中多个点号情况下，结果不正确的问题
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_utility', 'canonicalize(text,text,int4)', '1.1')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_utility.canonicalize( name IN text
, canon_name OUT text
, canon_len IN int4)
AS
DECLARE
l_result text := '''';
l_quota_tips int := 0;
l_cnt int := 0;
BEGIN
canon_name := '''';
-- for x in select split_part, count(*) over() as cnt
-- from (select trim(case when t.x ~ ''^"'' and t.x !~ ''"$'' and lead(t.x) over() ~ ''"$'' and lead(t.x) over() !~ ''^"'' then chr(27) -- ESC
-- when t.x ~ ''"$'' and t.x !~ ''^"'' and lag(t.x) over() ~ ''^"'' and lag(t.x) over() !~ ''"$'' then lag(t.x) over() || ''.'' || t.x
-- else t.x end, chr(9)||chr(10)||chr(11)||chr(12)||chr(13)||'' '') as split_part
-- from (select regexp_split_to_table(name, ''\.'') as x) as t)
-- where split_part != chr(27)
for x in select x as x_data
, case when x ~ ''^\s*"'' and x !~ ''"\s*$'' then -1
when x ~ ''"\s*$'' and x !~ ''^\s*"'' then 1
else 0
end as x_tip
, lead(rownum) over () as is_last
from (select regexp_split_to_table(name, ''\.'') as x)
loop
l_quota_tips := l_quota_tips + x.x_tip;
-- raise notice ''[%] [%] [%] [%]'', x.x_data, x.x_tip, x.is_last, x.cnt;
-- 单独出现的右括号，前面没有发现左括号
if l_quota_tips = 1
then
raise exception ''missing period (only right quotation)'';
-- 左右括号匹配上了，或者没有完全没有使用括号，则处理当前部分的数据
-- 或者处理到最后一条记录了: x.is_last is null
elsif l_quota_tips = 0 or x.is_last is null
then
l_cnt := l_cnt + 1;
-- 处理数据
if l_quota_tips = 0 and x.x_tip = 0 -- 完全没有使用括号
then
l_result := trim(x.x_data, chr(9)||chr(10)||chr(11)||chr(12)||chr(13)||'' ''); -- 移除首尾的空字符
else
l_result := trim(l_result||''.''||x.x_data, chr(9)||chr(10)||chr(11)||chr(12)||chr(13)||'' ''); -- 移除首尾的空字符
end if;
-- raise notice ''Process [%]'', l_result;
-- 0. 异常情况： 双引号中有双引号 ("Hon"gye")，双引号只包裹部分内容 (hon"gye")，非双引号时单词为空等 (missing period)，
if l_result is null or l_result = '''' or l_result ~ ''.".''
then
raise exception ''missing period (empty, double quotation)'';
-- 双引号空字符串
elsif l_result = ''""''
then
raise exception ''illegal zero-length identifier'';
-- 双引号单词
elsif l_result ~ ''^".+"$''
then
if l_cnt = 1 and x.is_last is null
then
canon_name := substr(l_result, 2, length(l_result) - 2);
elsif canon_name is null or canon_name = ''''
then
canon_name := l_result;
else
canon_name := canon_name || ''.'' || l_result;
end if;
-- 以下为非双引号单词
-- 异常情况： 非双引号时单词中间有空格，制表符，换行等 (missing period)，
elsif l_result ~ ''\s+''
then
raise exception ''missing period (blank, \t, \n)'';
-- 非双引号中有不合法的符号 (unexpected name string ""a".b,c.f"),
elsif l_result !~ ''^[\w\$\#]+$''
then
raise exception ''unexpected name string "%"'', name;
elsif l_cnt = 1 and x.is_last is null
then
canon_name := upper(l_result);
elsif canon_name is null or canon_name = ''''
then
canon_name := ''"'' || upper(l_result) || ''"'';
else
canon_name := canon_name || ''."'' || upper(l_result) || ''"'';
end if;
-- 初始化临时变量，准备下一个部分的数据处理
l_quota_tips := 0;
l_result := '''';
-- l_quota_tips 为负数，则需要依次往后查找对应的有括号，使得 x_tip 累加 (l_quota_tips) 为 0
else
if x.x_tip = -1 and l_quota_tips = -1 -- 遇到第一个左括号
then
l_result := x.x_data;
else
l_result := l_result||''.''||x.x_data;
end if;
-- raise notice ''Continue [%]'', l_result;
end if;
end loop;
canon_name := substrb(canon_name, 1, canon_len);
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_utility.canonicalize(text,text,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('dbms_utility.canonicalize(''HongYe'', 100)', '''HONGYE''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''"HongYe"'', 100)', '''HongYe''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''HongYe '', 100)', '''HONGYE''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''"HongYe "'', 100)', '''HongYe ''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''HongYe$'', 100)', '''HONGYE$''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''"HongYe$"'', 100)', '''HongYe$''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''HongYe$''||chr(9), 100)', '''HONGYE$''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''"HongYe$''||chr(9)||''"'', 100)', '''HongYe$'||chr(9)||'''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''HongYe$''||chr(10), 100)', '''HONGYE$''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''"HongYe$''||chr(10)||''"'', 100)', '''HongYe$'||chr(10)||'''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''HongYe.tab'', 100)', '''"HONGYE"."TAB"''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''HongYe. tab '', 100)', '''"HONGYE"."TAB"''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''"HongYe".tab'', 100)', '''"HongYe"."TAB"''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''HongYe."tab"'', 100)', '''"HONGYE"."tab"''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''"HongYe"."tab"'', 100)', '''"HongYe"."tab"''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''"Hon gYe"."ta b"'', 100)', '''"Hon gYe"."ta b"''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''"HongYe'||chr(9)||'"."t'||chr(10)||'ab"'', 100)', '''"HongYe'||chr(9)||'"."t'||chr(10)||'ab"''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''HongYe.pkg.proc'', 100)', '''"HONGYE"."PKG"."PROC"''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''"Hong.Ye".Pkg."Pr oc"'', 100)', '''"Hong.Ye"."PKG"."Pr oc"''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''"Hong.Ye".Pkg."Pr oc"'', 10)', '''"Hong.Ye".''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''"abc". "abc.de"."aaa.xxx.'', 100)', '''P0001: unexpected name string ""abc". "abc.de"."aaa.xxx."''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''"abc". "abc.de"."aaa.xxx."'', 100)', '''"abc"."abc.de"."aaa.xxx."''') into l_result;
select compat_tools.f_unit_test('dbms_utility.canonicalize(''"abc.def.ghi"'', 100)', '''abc.def.ghi''') into l_result;
-- 测试备注：
-- 没有点号的情况下：
-- 0. 异常情况： 双引号中有双引号 ("Hon"gye")，双引号只包裹部分内容 (hon"gye")，非双引号时单词中间有空格，制表符，换行，或者单词为空等 (missing period)，
-- 非双引号中有不合法的符号 (unexpected name string ""a".b,c.f"),
-- 双引号空字符串 ()
-- 1. 有双引号包裹： 保留双引号中的内容，移除首尾双引号
-- 2. 没有双引号包裹： 全转大写，移除首尾空格，制表符，换行等
-- 有点号的情况：
-- 1. 不论有没有双引号包括，结果的各个部分都有双引号包裹
-- 2. 按点号切割，每个部分按照没有点号的逻辑处理
-- declare
-- l_out varchar2(100);
-- begin
-- for x in (select rownum as id, x
-- from (select 'HongYe' as x from dual union all
-- select '"HongYe"' as x from dual union all
-- select 'HongYe ' as x from dual union all
-- select '"HongYe "' as x from dual union all
-- select 'HongYe$' as x from dual union all
-- select '"HongYe$"' as x from dual union all
-- select 'HongYe$'||chr(9) as x from dual union all
-- select '"HongYe$"'||chr(9) as x from dual union all
-- select 'HongYe$'||chr(10) as x from dual union all
-- select '"HongYe$'||chr(10)||'"' as x from dual union all
-- select 'HongYe.tab' as x from dual union all
-- select 'HongYe. tab ' as x from dual union all
-- select '"HongYe".tab' as x from dual union all
-- select 'HongYe."tab"' as x from dual union all
-- select '"HongYe"."tab"' as x from dual union all
-- select '"Hon gYe"."ta b"' as x from dual union all
-- select '"HongYe'||chr(9)||'"."t'||chr(10)||'ab"' as x from dual union all
-- select 'HongYe.pkg.proc' as x from dual union all
-- select '"Hong.Ye".Pkg."Pr oc"' as x from dual
-- ))
-- loop
-- dbms_utility.canonicalize(x.x, l_out, 100);
-- dbms_output.put_line(x.id||'. ['||x.x||'] => ['||l_out||']');
-- end loop;
-- end;
-- /
-- =========================================================================
-- =========================================================================
-- dbms_utility.comma_to_table(text,int4,text[])
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_utility', 'comma_to_table(text,int4,text[])', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_utility.comma_to_table( list IN text
, tablen OUT int4
, tab OUT text[])
AS
DECLARE
l_result text;
BEGIN
tablen := 0;
tab := ''{}''::text[];
for x in select split_part, count(*) over() as cnt, rownum as row_id
from (select case when t.x ~ ''^\s*"'' and t.x !~ ''"\s*$'' and lead(t.x) over() ~ ''"\s*$'' and lead(t.x) over() !~ ''^\s*"'' then chr(27) -- ESC
when t.x ~ ''"\s*$'' and t.x !~ ''^\s*"'' and lag(t.x) over() ~ ''^\s*"'' and lag(t.x) over() !~ ''"\s*$'' then lag(t.x) over() || ''.'' || t.x
else t.x
end as split_part
from (select regexp_split_to_table(list, '','') as x) as t)
where split_part != chr(27) or split_part is null
loop
-- 0. 异常情况： 空串，空白字符串，字符串中间有双引号
if x.split_part is null or x.split_part ~ ''^\s+$'' or instr(trim(trim(x.split_part), ''"''), ''"'') > 0 or length(trim(trim(x.split_part), ''"'')) > 30
then
if x.cnt = 1
then
raise exception ''comma-separated list invalid near %'', x.split_part;
elsif x.row_id = 1
then
raise exception ''comma-separated list invalid near %,'', x.split_part;
elsif x.row_id = x.cnt
then
raise exception ''comma-separated list invalid near ,%'', x.split_part;
else
raise exception ''comma-separated list invalid near ,%,'', x.split_part;
end if;
else
tab := array_append(tab, x.split_part);
tablen := tablen + 1;
end if;
end loop;
tab := array_append(tab, NULL);
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_utility.comma_to_table(text,int4,text[]): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('(dbms_utility.comma_to_table(''hongye'')).tablen', '1') into l_result;
select compat_tools.f_unit_test('(dbms_utility.comma_to_table(''HongYe, "HongYe","HongYe,12345678901234567890123" , HongYeasd'')).tablen', '4') into l_result;
select compat_tools.f_unit_test('(dbms_utility.comma_to_table(''hongye'')).tab::text', '''{hongye,NULL}''') into l_result;
select compat_tools.f_unit_test('(dbms_utility.comma_to_table(''HongYe, "HongYe","HongYe,12345678901234567890123" , HongYeasd'')).tab::text', '''{HongYe," \"HongYe\"","\"HongYe.12345678901234567890123\" "," HongYeasd",NULL}''') into l_result;
-- Oracle 测试代码：
-- declare
-- l_tablen number;
-- l_tab dbms_utility.uncl_array;
-- begin
-- for x in (select rownum as id, x
-- from (select 'HongYe' as x from dual union all
-- select 'HongYe, "HongYe","HongYe 12345678901234567890123" , HongYeasd' as x from dual
-- )
-- )
-- loop
-- dbms_utility.comma_to_table(x.x, l_tablen, l_tab);
-- dbms_output.put_line(x.id||'. ['||x.x||'] => ['||l_tablen||']');
-- for y in 1..(l_tablen+1)
-- loop
-- dbms_output.put_line(' ['||l_tab(y)||']');
-- end loop;
-- end loop;
-- end;
-- /
-- =========================================================================
-- =========================================================================
-- dbms_utility.table_to_comma(text[],int4,text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_utility', 'table_to_comma(text[],int4,text)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_utility.table_to_comma( tab IN text[]
, tablen OUT int4
, list OUT text)
AS
BEGIN
tablen := array_length(tab, 1) - 1;
list := trim(array_to_string(tab, '',''), '','');
END;';
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('(dbms_utility.table_to_comma(''{HongYe,NULL}''::text[])).tablen', '1') into l_result;
select compat_tools.f_unit_test('(dbms_utility.table_to_comma(''{HongYe,NULL}''::text[])).list', '''HongYe''') into l_result;
select compat_tools.f_unit_test('(dbms_utility.table_to_comma(''{HongYe," \"HongYe\"","\"HongYe.12345678901234567890123\" "," HongYeasd",NULL}''::text[])).tablen', '4') into l_result;
select compat_tools.f_unit_test('(dbms_utility.table_to_comma(''{HongYe," \"HongYe\"","\"HongYe.12345678901234567890123\" "," HongYeasd",NULL}''::text[])).list', '''HongYe, "HongYe","HongYe.12345678901234567890123" , HongYeasd''') into l_result;
-- =========================================================================
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_utility.table_to_comma(text[],int4,text): %', SQLERRM;
rollback;
end;
-- Oracle 测试代码：
-- declare
-- l_tablen number;
-- l_tab dbms_utility.uncl_array;
-- l_list varchar2(4000);
-- begin
-- for x in (select rownum as id, x
-- from (select 'HongYe' as x from dual union all
-- select 'HongYe, "HongYe","HongYe 12345678901234567890123" , HongYeasd' as x from dual
-- )
-- )
-- loop
-- dbms_utility.comma_to_table(x.x, l_tablen, l_tab);
-- dbms_output.put_line(x.id||'. ['||x.x||'] => ['||l_tablen||']');
-- for y in 1..(l_tablen+1)
-- loop
-- dbms_output.put_line(' ['||l_tab(y)||']');
-- end loop;
-- dbms_utility.table_to_comma(l_tab, l_tablen, l_list);
-- dbms_output.put_line('----> ['||l_list||']');
-- end loop;
-- end;
-- /
-- =========================================================================
-- =========================================================================
-- dbms_utility.db_version(text,text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_utility', 'db_version(text,text)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_utility.db_version( version OUT text
, compatibility OUT text)
AS
BEGIN
select trim(regexp_substr(version(), ''\([^\)]+\)''), ''()''), datcompatibility
into version, compatibility
from pg_database
where datname = current_database();
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_utility.db_version(text,text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('(dbms_utility.db_version()).version', 'NULL', 'IS NOT') into l_result;
select compat_tools.f_unit_test('(dbms_utility.db_version()).compatibility', '(''A'', ''B'', ''C'', ''PG'')', 'IN') into l_result;
-- Oracle 测试代码：
-- declare
-- l_version varchar2(1000);
-- l_comp varchar2(1000);
-- begin
-- dbms_utility.db_version(l_version, l_comp);
-- dbms_output.put_line('version: ['||l_version||']');
-- dbms_output.put_line('compatibility: ['||l_comp||']');
-- end;
-- /
-- version: [11.2.0.4.0]
-- compatibility: [11.2.0.4.0]
-- =========================================================================
-- =========================================================================
-- dbms_utility.exec_ddl_statement(text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_utility', 'exec_ddl_statement(text)', '1.0')
then
execute '
CREATE OR REPLACE PROCEDURE dbms_utility.exec_ddl_statement(parse_string text)
AS
BEGIN
execute immediate parse_string;
END;';
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_utility.exec_ddl_statement(text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
-- 注意： SQL语句中需要写明所有表对应的Schema，否则都对应到dbms_utility中了
select compat_tools.f_unit_test('dbms_utility.exec_ddl_statement(''create table if not exists public.test_exec_ddl_stmt (id int, name text);'')', 'NOT NULL', 'IS') into l_result;
select compat_tools.f_unit_test('dbms_utility.exec_ddl_statement('' truncate table public.test_exec_ddl_stmt'')', 'NOT NULL', 'IS') into l_result;
select compat_tools.f_unit_test('dbms_utility.exec_ddl_statement(''drop table if exists public.test_exec_ddl_stmt ; '')', 'NOT NULL', 'IS') into l_result;
-- Oracle 测试代码：
-- begin
-- dbms_utility.exec_ddl_statement('create table test_exec_ddl_stmt(id int)');
-- end;
-- /
-- =========================================================================
-- =========================================================================
-- dbms_obfuscation_toolkit.md5(text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_obfuscation_toolkit', 'md5(text)', '1.0', 'sql')
then
CREATE OR REPLACE FUNCTION dbms_obfuscation_toolkit.md5(input_string text)
returns raw IMMUTABLE
as $$
select hextoraw(md5(input_string));
$$ language sql;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_obfuscation_toolkit.md5(text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => ''hongye'')::text', '''AAEC711EEC7B24EE17E456EBEDBF9FBE''') into l_result;
select compat_tools.f_unit_test('rawtohex(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => ''hongye''))::text', '''AAEC711EEC7B24EE17E456EBEDBF9FBE''') into l_result;
-- Oracle 测试代码：
-- SQL> select rawtohex(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => 'hongye')) from dual;
-- RAWTOHEX(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING=>'HONGYE'))
-- --------------------------------------------------------------------------------
-- AAEC711EEC7B24EE17E456EBEDBF9FBE
-- =========================================================================
-- -- =========================================================================
-- -- dbms_obfuscation_toolkit.md5(raw)
-- -- =========================================================================
-- begin
-- if compat_tools.drop_compat_package('PROCEDURE', 'dbms_obfuscation_toolkit', 'md5(raw)', '1.0')
-- then
-- CREATE OR REPLACE FUNCTION dbms_obfuscation_toolkit.md5(input text)
-- returns raw IMMUTABLE
-- as $$
-- select hextoraw(md5(input));
-- $$ language sql;
-- end if;
-- commit;
-- exception
-- when others then
-- RAISE WARNING 'Error in dbms_obfuscation_toolkit.md5(text): %', SQLERRM;
-- rollback;
-- end;
-- -- =========================================================================
-- -- 测试用例：
-- -- ----------
-- select compat_tools.f_unit_test('DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => ''hongye'')::text', '''AAEC711EEC7B24EE17E456EBEDBF9FBE''') into l_result;
-- select compat_tools.f_unit_test('rawtohex(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => ''hongye''))::text', '''AAEC711EEC7B24EE17E456EBEDBF9FBE''') into l_result;
-- -- Oracle 测试代码：
-- -- SQL> select DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT => rawtohex('hongye')) from dual;
-- -- DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT=>RAWTOHEX('HONGYE'))
-- -- --------------------------------------------------------------------------------
-- -- AAEC711EEC7B24EE17E456EBEDBF9FBE
-- -- =========================================================================
-- =========================================================================
-- utl_url
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/main/oracle-package/utl_url.sql
-- =========================================================================
-- utl_url.escape(text,bool,text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_url', 'escape(text,bool,text)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION UTL_URL.escape(url IN TEXT, escape_reserved_chars IN BOOL DEFAULT FALSE, url_charset IN TEXT DEFAULT 'UTF8')
RETURNS TEXT
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
declare
L_TMP TEXT DEFAULT '';
L_BAD TEXT DEFAULT ' >%}\~];?@&<#{|^[`/:=$+''"';
l_reserved_chars TEXT DEFAULT ';/?:@&=+$[]';
L_CHAR TEXT;
BEGIN
IF (url IS NULL) THEN
RETURN NULL;
END IF;
if not escape_reserved_chars then
L_BAD := translate(L_BAD, l_reserved_chars, '');
end if;
FOR I IN 1..LENGTH(url) LOOP
L_CHAR := SUBSTR(url, I, 1);
IF (INSTR(L_BAD, L_CHAR) > 0 or ascii(L_CHAR) > 255) THEN
L_TMP := L_TMP || regexp_replace(upper(REPLACE(convert_TO(L_CHAR, url_charset)::TEXT,'\x','')),'(.{2})',
'%\1','g');
ELSE
L_TMP := L_TMP || L_CHAR;
END IF;
END LOOP;
RETURN L_TMP;
END; $$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_url.escape(text,bool,text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_url.escape(''http://新年好.com'')', '''http://%E6%96%B0%E5%B9%B4%E5%A5%BD.com''') into l_result;
select compat_tools.f_unit_test('utl_url.escape(''http://新年好.com'',1)', '''http%3A%2F%2F%E6%96%B0%E5%B9%B4%E5%A5%BD.com''') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_url.escape(text,bool,text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_url', 'unescape(text,text)', '1.0','sql')
then
execute $q$
CREATE OR REPLACE FUNCTION UTL_URL.unescape(url IN TEXT, url_charset IN TEXT DEFAULT 'UTF8')
RETURNS TEXT
LANGUAGE sql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
select CONVERT_FROM(string_agg(CASE
WHEN LENGTH(A) = 3 THEN
REPLACE(A, '%','\x')::bytea
ELSE
A :: bytea
END, '' :: bytea), url_charset)
from (select a
from (select (regexp_matches(url, '(%..|.)', 'g')) [ 1 ] a ) ) A;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_url.unescape(text,text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_url.unescape(''http://%E6%96%B0%E5%B9%B4%E5%A5%BD.com'')', '''http://新年好.com''') into l_result;
select compat_tools.f_unit_test('utl_url.unescape(''http%3A%2F%2F%E6%96%B0%E5%B9%B4%E5%A5%BD.com'',''utf8'')', '''http://新年好.com''') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_encode
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/main/oracle-package/utl_encode.sql
-- =========================================================================
-- utl_encode.base64_encode(raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'base64_encode(raw)', '1.0','sql')
then
execute $q$
CREATE OR REPLACE FUNCTION UTL_ENCODE.base64_encode(r IN raw)
RETURNS raw
LANGUAGE SQL
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
SELECT rawtohex(encode(rawsend(r),'base64'))::RAW;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_encode.base64_encode(raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('UTL_ENCODE.base64_encode(rawtohex(''测试'')::raw)', '''3572574C364B2B56''::raw') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_encode.base64_decode(raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'base64_decode(raw)', '1.0','sql')
then
execute $q$
CREATE OR REPLACE FUNCTION UTL_ENCODE.base64_decode(r IN raw)
RETURNS raw
LANGUAGE SQL
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
SELECT rawout(decode(encode(rawsend(r),'escape'),'base64')::BYTEA)::TEXT::RAW;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_encode.base64_decode(raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('UTL_ENCODE.base64_decode(''3572574C364B2B56'')', '''E6B58BE8AF95''::raw') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_encode.text_encode(text,text,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'text_encode(text,text,int4)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION utl_encode.text_encode(buf in text,
encode_charset in text default 'UTF8'::text,
encoding in int4 default 2::int4)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result text DEFAULT ''::text;
begin
if encoding=1 THEN
l_result:= encode(CONVERT_TO(buf,encode_charset), 'base64') ;
elsif encoding=2 THEN
select string_agg( (case when ascii(s)<=255 AND s!='=' then s else
regexp_replace(upper(REPLACE(convert_TO(s, encode_charset)::TEXT,'\x','')),'(.{2})',
'=\1','g') end ),'') into l_result
from (select unnest(string_to_array(buf, null) ) s);
else
raise 'invaild encoding!';
end if;
return l_result;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_encode.text_encode(text,text,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_encode.text_encode(''往1234\as df=AB'',encode_charset => ''GBK'')', '''=CD=F91234\as df=3DAB''') into l_result;
select compat_tools.f_unit_test('utl_encode.text_encode(''往1234\as df=AB'',encode_charset => ''GBK'',encoding => 1)', '''zfkxMjM0XGFzIGRmPUFC''') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_encode.text_decode(text,text,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'text_decode(text,text,int4)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION utl_encode.text_decode(buf in text,
encode_charset in text default 'UTF8'::text,
encoding in int4 default 2::int4)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result text DEFAULT ''::text;
begin
if encoding=1 THEN
l_result:= CONVERT_FROM(decode(buf, 'base64'),encode_charset) ;
elsif encoding=2 THEN
select CONVERT_FROM(string_agg(CASE
WHEN LENGTH(A) = 3 THEN
REPLACE(A, '=', '\x') :: bytea
when a = '\' then
'\\' :: bytea
ELSE
A :: bytea
END, '' :: bytea), encode_charset) into l_result
from (select a
from (select (regexp_matches(buf, '(=..|.)', 'g')) [ 1 ] a ) ) A;
else
raise 'invaild encoding!';
end if;
return l_result;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_encode.text_decode(text,text,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_encode.text_decode(''=CD=F91234\as df=3DAB'',encode_charset => ''GBK'',encoding => 2)', '''往1234\as df=AB''') into l_result;
select compat_tools.f_unit_test('utl_encode.text_decode(''zfkxMjM0XGFzIGRmPUFC'',encode_charset => ''GBK'',encoding => 1)', '''往1234\as df=AB''') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_encode.quoted_printable_encode(raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'quoted_printable_encode(raw)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION utl_encode.quoted_printable_encode(r in raw)
RETURNS raw
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result raw DEFAULT ''::raw;
l_def_charset text DEFAULT 'utf8';
begin
select pg_encoding_to_char(encoding) into l_def_charset from pg_database where datname=current_database();
select rawtohex(string_agg((case
when ascii(s) <= 255 AND s not in('=', '\') then
s
when s = '\' then
'\\'
else
regexp_replace(upper(REPLACE(convert_TO(s, l_def_charset) :: TEXT, '\x', '')), '(.{2})', '=\1', 'g')
end), '')) :: raw into l_result
from (select unnest(string_to_array(convert_from(rawsend(r), l_def_charset), null) ) s );
return l_result;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_encode.quoted_printable_encode(raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_encode.quoted_printable_encode( ''E5BE80313233345C61732064663D4142'')', '''3D45353D42453D3830313233345C61732064663D33444142''') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_encode.quoted_printable_decode(raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'quoted_printable_decode(raw)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION utl_encode.quoted_printable_decode(r in raw)
RETURNS raw
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result raw DEFAULT ''::raw;
l_def_charset text DEFAULT 'utf8';
begin
select pg_encoding_to_char(encoding) into l_def_charset from pg_database where datname=current_database();
select rawout(string_agg(CASE
WHEN LENGTH(A) = 3 THEN
REPLACE(A, '=', '\x') :: bytea
when a = '\' then
'\\' :: bytea
ELSE
A :: bytea
END, '' :: bytea))::text::raw into l_result
from (select a
from (select (regexp_matches(convert_from(rawsend(r), l_def_charset), '(=..|.)', 'g')) [ 1 ] a ) ) A;
return l_result;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_encode.quoted_printable_decode(raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_encode.quoted_printable_decode( ''3D45353D42453D3830313233345C61732064663D33444142'')', '''E5BE80313233345C61732064663D4142''') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_encode.mimeheader_encode(text,text,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'mimeheader_encode(text,text,int4)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION utl_encode.mimeheader_encode(buf in text,
encode_charset in text default 'UTF8'::text,
encoding in int4 default 2::int4)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result text DEFAULT ''::text;
begin
if encoding=1 THEN
l_result:= encode(CONVERT_TO(buf,encode_charset), 'base64') ;
elsif encoding=2 THEN
select string_agg( (case when ascii(s)<=255 AND s NOT IN ('=',' ','?') then s else
regexp_replace(upper(REPLACE(convert_TO(s, encode_charset)::TEXT,'\x','')),'(.{2})',
'=\1','g') end ),'') into l_result
from (select unnest(string_to_array(buf, null) ) s);
else
raise 'invaild encoding!';
end if;
return '=?'||encode_charset||'?'||CASE WHEN encoding=2 THEN 'Q' ELSE 'B' END||'?'||l_result||'?=';
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_encode.mimeheader_encode(text,text,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('UTL_ENCODE.MIMEHEADER_ENCODE(''What is the date 王 =20 / \?'',encode_charset =>''GBK'' )', '''=?GBK?Q?What=20is=20the=20date=20=CD=F5=20=3D20=20/=20\=3F?=''') into l_result;
select compat_tools.f_unit_test('UTL_ENCODE.MIMEHEADER_ENCODE(''What is the date 王 =20 / \?'',encoding => 2 )', '''=?UTF8?Q?What=20is=20the=20date=20=E7=8E=8B=20=3D20=20/=20\=3F?=''') into l_result;
select compat_tools.f_unit_test('UTL_ENCODE.MIMEHEADER_ENCODE(''What is the date 王 =20 / \?'',encode_charset =>''UTF8'' ,encoding => 1)', '''=?UTF8?B?V2hhdCBpcyB0aGUgZGF0ZSDnjosgPTIwIC8gXD8=?=''') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_encode.mimeheader_decode(text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'mimeheader_decode(text)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION utl_encode.mimeheader_decode(buf in text)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_encode_charset text;
l_encoding text;
l_result text DEFAULT ''::text;
l_buf text;
begin
l_encode_charset:=REGEXP_SUBSTR(buf,'(?<=\=\?)(.+?)(?=\?.\?)');
l_encoding:=REGEXP_SUBSTR(buf,'(?<=\?)(.?)(?=\?)');
l_buf:=REGEXP_SUBSTR(buf,'(?<=\?.\?)(.+?)(?=\?\=)');
if l_encoding='B' THEN
l_result:= CONVERT_FROM(decode(l_buf, 'base64'),l_encode_charset) ;
elsif l_encoding='Q' THEN
select CONVERT_FROM(string_agg(CASE
WHEN LENGTH(A) = 3 THEN
REPLACE(A, '=', '\x') :: bytea
when a = '\' then
'\\' :: bytea
ELSE
A :: bytea
END, '' :: bytea), l_encode_charset) into l_result
from (select a
from (select (regexp_matches(l_buf, '(=..|.)', 'g')) [ 1 ] a ) ) A;
else
raise 'invaild encoding!';
end if;
return l_result;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_encode.mimeheader_decode(text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('UTL_ENCODE.mimeheader_decode(''=?UTF-8?Q?What=20is=20the=20date=20=E7=8E=8B=20=3D20=20/=20\=3F?='')', '''What is the date 王 =20 / \?''') into l_result;
select compat_tools.f_unit_test('UTL_ENCODE.mimeheader_decode(''=?UTF8?B?V2hhdCBpcyB0aGUgZGF0ZSDnjosgPTIwIC8gXD8=?='')', '''What is the date 王 =20 / \?''') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_encode.uuencode(raw,int1,text,text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'uuencode(raw,int1,text,text)', '2.0')
then
execute $q$
create or replace function UTL_ENCODE.uuencode(r in raw,
type in int1 default 1::int1,
filename in text default 'uuencode.txt',
permission in text default '0')
RETURNS raw
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
/*Oracle自带函数有BUG(doc id 2197134.1),未遵循uuencode标准,官方不建议使用
而本函数按uuencode标准生成 -- DarkAthena 2022-02-14*/
l_result text DEFAULT ''::text;
l_pos int4 DEFAULT 1;
l_3_bytes text DEFAULT ''::text;
l_new_4_chr text DEFAULT ''::text;
l_full_str text DEFAULT ''::text;
l_line_num int4 DEFAULT 1;
l_line_str text DEFAULT ''::text;
l_line_len int4 DEFAULT 60::int4;
begin
if type not in (1,2,3,4) THEN
RAISE 'input type error!';
end if;
loop
l_3_bytes:=substring(r from l_pos*6-5 for 6);
if length(l_3_bytes)=0 or l_3_bytes is null THEN
exit;
elsif length(l_3_bytes)!=6 THEN
l_3_bytes:=rpad(l_3_bytes,6,'0');
end if;
select
chr(SUBSTRING(a from 1 for 6)::int +32)||
chr(SUBSTRING(a from 7 for 6)::int+32)||
chr(SUBSTRING(a from 13 for 6)::int+32)||
chr(SUBSTRING(a from 19 for 6)::int+32) into l_new_4_chr
from
(select to_number(l_3_bytes,'xxxxxx')::int::bit(24) a);
l_full_str:=l_full_str||l_new_4_chr;
l_pos:=l_pos+1;
end loop;
loop
l_line_str:=substring(l_full_str from l_line_num*(l_line_len)-(l_line_len-1) for l_line_len);
if length(l_line_str)=0 or l_line_str is null then
exit;
end if;
l_result:=l_result||chr((length(l_line_str))/4*3+32)||l_line_str||chr(13)||chr(10);
l_line_num:=l_line_num+1;
end loop;
l_result:=replace(l_result,' ','`');
l_result:=substr(l_result,1,length(l_result)-2);
if type in (1,2) then
l_result:='begin '||permission||' '||filename||chr(13)||chr(10)||l_result;
end if;
if type in (1,4) then
l_result:=l_result||chr(13)||chr(10)||'`'||chr(13)||chr(10)||'end';
end if;
return rawtohex(replace(l_result,'\','\\'))::raw;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_encode.uuencode(raw,int1,text,text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('UTL_ENCODE.uuencode(''6162636465666731323334''::raw)', '''626567696E2030207575656E636F64652E7478740D0A2C3836294339263546395324522C5330600D0A600D0A656E64''::raw') into l_result;
select compat_tools.f_unit_test('UTL_ENCODE.uuencode(''6162636465666731323334'',3)', '''2C3836294339263546395324522C533060''') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_encode.uudecode(raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'uudecode(raw)', '2.0')
then
execute $q$
create or replace function UTL_ENCODE.uudecode(r in raw)
RETURNS raw
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
/*Oracle自带函数有BUG(doc id 2197134.1),未遵循uuencode标准,官方不建议使用
而本函数按uuencode标准解析-- DarkAthena 2022-02-14*/
l_ori_str text DEFAULT ''::text;
l_result text DEFAULT ''::text;
l_pos int4 DEFAULT 1;
l_3_bytes text DEFAULT ''::text;
l_new_4_chr text DEFAULT ''::text;
l_full_str text DEFAULT ''::text;
l_line_num int4 DEFAULT 1;
l_line_str text DEFAULT ''::text;
l_line_len int4 DEFAULT 60::int4;
begin
SELECT convert_from(rawsend(r),
(select pg_encoding_to_char(encoding) as encoding from pg_database where datname=current_database()))
into l_ori_str;
l_ori_str:=replace(l_ori_str,chr(13)||chr(10)||'`'||chr(13)||chr(10)||'end','');
if substr(l_ori_str,1,5) ='begin' THEN
l_ori_str:=substr(l_ori_str,instr(l_ori_str,chr(10))+1);
end if ;
LOOP
l_line_str:=substr(l_ori_str,2+(l_line_len+3)*(l_line_num-1),l_line_len);
if length(l_line_str)=0 or l_line_str is null then exit;
end if;
l_full_str:=l_full_str||l_line_str;
l_line_num:=l_line_num+1;
end loop;
l_full_str:=replace(l_full_str,'`',' ');
LOOP
l_new_4_chr:=substring(l_full_str from 1+4*(l_pos-1) for 4);
if length(l_new_4_chr)=0 or l_new_4_chr is null then exit;
end if;
select to_char((listagg(substring( (ascii(a)-32)::bit(8) from 3 for 6)::text) within group(order by 1))::bit(24)::int,'fmxxxxxx')::raw
into l_3_bytes
from (select unnest(string_to_array(l_new_4_chr,null)) a);
l_result:=l_result||l_3_bytes;
l_pos:=l_pos+1;
end LOOP;
l_result:=regexp_replace(l_result,'(00)+$','');
return l_result::raw;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_encode.uudecode(raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_encode.uudecode(rawtohex('',86)C9&5F9S$R,S0`'')::raw)', '''6162636465666731323334''') into l_result;
select compat_tools.f_unit_test('utl_encode.uudecode(''626567696E2030207575656E636F64652E7478740D0A2C3836294339263546395324522C5330600D0A600D0A656E64''::raw)', '''6162636465666731323334''') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw
-- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/main/oracle-package/utl_raw.sql
-- =========================================================================
-- utl_raw.cast_to_varchar2(raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'cast_to_varchar2(raw)', '1.0','sql')
then
execute $q$
CREATE OR REPLACE FUNCTION UTL_RAW.cast_to_varchar2(r IN raw)
RETURNS text
LANGUAGE SQL
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
SELECT convert_from(rawsend(r),(select pg_encoding_to_char(encoding) as encoding from pg_database where datname=current_database()));
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.cast_to_varchar2(raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.cast_to_varchar2(''43616D65726F6E'')', '''Cameron''') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.cast_to_raw(text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'cast_to_raw(text)', '1.0','sql')
then
execute $q$
CREATE OR REPLACE FUNCTION UTL_RAW.cast_to_raw(c IN text)
RETURNS raw
LANGUAGE SQL
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
SELECT rawtohex(c)::raw;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.cast_to_raw(text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.cast_to_raw(''Cameron'')', '''43616D65726F6E''::raw') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.concat(raw,raw,raw,raw,raw,raw,raw,raw,raw,raw,raw,raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'concat(raw,raw,raw,raw,raw,raw,raw,raw,raw,raw,raw,raw)', '1.0','sql')
then
execute $q$
CREATE OR REPLACE FUNCTION UTL_RAW.concat(
r1 IN raw DEFAULT ''::raw,
r2 IN raw DEFAULT ''::raw,
r3 IN raw DEFAULT ''::raw,
r4 IN raw DEFAULT ''::raw,
r5 IN raw DEFAULT ''::raw,
r6 IN raw DEFAULT ''::raw,
r7 IN raw DEFAULT ''::raw,
r8 IN raw DEFAULT ''::raw,
r9 IN raw DEFAULT ''::raw,
r10 IN raw DEFAULT ''::raw,
r11 IN raw DEFAULT ''::raw,
r12 IN raw DEFAULT ''::raw
)
RETURNS raw
LANGUAGE SQL
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
SELECT (r1||r2||r3||r4||r5||r6||r7||r8||r9||r10||r11||r12)::raw;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.concat(raw,raw,raw,raw,raw,raw,raw,raw,raw,raw,raw,raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.concat( ''9'', ''0102'', ''ff'', ''0a2b'' )', '''090102FF0A2B''::raw') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.length(raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'length(raw)', '1.0','sql')
then
execute $q$
CREATE OR REPLACE FUNCTION UTL_RAW.length(r IN raw)
RETURNS int4
LANGUAGE SQL
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
SELECT (pg_catalog.length(r)/2)::int4;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.length(raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('UTL_RAW.length(''FF''::raw)', '1') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.substr(raw,int4,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'substr(raw,int4,int4)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION UTL_RAW.substr(r IN raw,pos in int4,len in int4 DEFAULT null)
RETURNS raw
LANGUAGE plpgSQL
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
BEGIN
if pos>0 then
return (case when len is not null
then substring(r from pos*2-1 for len*2)
else substring(r from pos*2-1) end )::raw ;
ELSE
return (case when len is not null
then pg_catalog.substr(r , pos*2 , len*2)
else pg_catalog.substr(r , pos*2) end )::raw ;
end if;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.substr(raw,int4,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.substr( ''0102030405'', 3, 2 )', '''0304''::raw') into l_result;
select compat_tools.f_unit_test('utl_raw.substr( ''0102030405'', -2, 1 )', '''04''::raw') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.transliterate(raw,raw,raw,raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'transliterate(raw,raw,raw,raw)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION UTL_RAW.transliterate(
r IN raw,
to_set in raw DEFAULT ''::raw,
from_set in raw DEFAULT ''::raw,
pad IN raw DEFAULT '00'::raw )
RETURNS raw
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_tmp text;
begin
if pg_catalog.length(pad)/2>1 then
RAISE 'Error in utl_raw.transliterate: pad must be 1 byte or null!';
end if;
l_tmp:=regexp_replace(r::TEXT, '(..)','=\1', 'g');
for rec in (
select fs_str,nvl(ts_str,pad::TEXT) ts_str from
(select row_number() over() fs_pos,fs_str from (
select (regexp_matches(from_set::TEXT, '(..)', 'g')) [ 1 ] fs_str)) f
left join
(select row_number() over() ts_pos,ts_str from (
select (regexp_matches(to_set::TEXT, '(..)', 'g')) [ 1 ] ts_str)) t
on f.fs_pos=t.ts_pos
) loop
l_tmp:=replace(l_tmp,'='||rec.fs_str,rec.ts_str);
end loop;
return replace(l_tmp,'=','')::raw;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.transliterate(raw,raw,raw,raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.transliterate( ''010203040502'', ''0809'', ''01020304'', ''0a'' )', '''08090A0A0509''::raw') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.translate(raw,raw,raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'translate(raw,raw,raw)', '1.0','sql')
then
execute $q$
CREATE OR REPLACE FUNCTION UTL_RAW.translate(r IN raw,from_set in raw,to_set in raw )
RETURNS raw
LANGUAGE sql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
select UTL_RAW.transliterate(r,to_set,from_set,''::raw);
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.translate(raw,raw,raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.translate( ''0102030405'', ''0203'', ''06'' )', '''01060405''::raw') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.copies(raw,int8)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'copies(raw,int8)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION UTL_RAW.copies(
r IN raw,
n IN int8)
RETURNS raw
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result raw DEFAULT ''::raw;
begin
if n<0 then
RAISE 'n must be equal or greater than 1!';
end if;
if pg_catalog.length(r)/2<1 then
RAISE 'r is missing, null and/or 0 length!';
end if;
for i in 1..n LOOP
l_result:=l_result||r;
end loop;
return l_result;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.copies(raw,int8): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.copies( ''010203'', 4 )', '''010203010203010203010203''::raw') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.overlay(raw,raw,int4,int4,raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'overlay(raw,raw,int4,int4,raw)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION UTL_RAW.overlay(
overlay_str IN raw,
target IN raw,
pos IN int4 DEFAULT 1,
len IN int4 DEFAULT NULL,
pad IN raw DEFAULT '00'::raw)
RETURNS raw
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result raw;
l_overlay_str raw;
l_len int4;
l_pad raw;
l_pos_over_str raw DEFAULT ''::raw;
begin
l_overlay_str:=overlay_str;
if UTL_RAW.length(pad)>1 THEN
l_pad:=UTL_RAW.substr(pad , 1 , 1);
ELSE
l_pad:=pad;
end if;
if len is null then
l_len:=UTL_RAW.length(overlay_str);
else
l_len:=len;
end if;
if UTL_RAW.length(l_overlay_str)>l_len THEN
l_overlay_str:=UTL_RAW.substr(l_overlay_str , 1 , l_len);
elsif UTL_RAW.length(l_overlay_str)<l_len THEN
l_overlay_str:=l_overlay_str||UTL_RAW.copies(l_pad,l_len-UTL_RAW.length(l_overlay_str) );
end if;
if pos>utl_raw.length(target) THEN
l_pos_over_str:=UTL_RAW.copies(pad,pos-utl_raw.length(target)-1);
end if;
l_result:=UTL_RAW.substr(target , 1 , pos-1)||l_pos_over_str||l_overlay_str||UTL_RAW.substr(target , pos+l_len);
return l_result;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.overlay(raw,raw,int4,int4,raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.overlay( ''aabb'', ''010203'' )', '''AABB03''::raw') into l_result;
select compat_tools.f_unit_test('utl_raw.overlay( ''aabb'', ''010203'',2 )', '''01AABB''::raw') into l_result;
select compat_tools.f_unit_test('utl_raw.overlay( ''aabb'', ''010203'',5 )', '''01020300AABB''::raw') into l_result;
select compat_tools.f_unit_test('utl_raw.overlay( ''aabb'', ''010203'',2,1 )', '''01AA03''::raw') into l_result;
select compat_tools.f_unit_test('utl_raw.overlay( ''aabb'', ''010203'',5,1,''FF'' )', '''010203FFAA''::raw') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.xrange(raw,raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'xrange(raw,raw)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION UTL_RAW.xrange(
start_byte IN raw,
end_byte IN raw)
RETURNS raw
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result raw DEFAULT ''::raw;
l_start_int int4;
l_end_int int4;
l_tmp raw;
begin
if UTL_RAW.length(start_byte)!=1 or UTL_RAW.length(end_byte)!=1 then
RAISE 'start_byte and end_byte must be single byte!';
end if;
l_start_int:=to_number(start_byte,'xx');
l_end_int:=to_number(end_byte,'xx');
LOOP
l_tmp:=utl_raw.substr(to_hex(l_start_int)::raw,-1,1);
l_result:=l_result||l_tmp;
if l_tmp=utl_raw.substr(end_byte,-1) then exit;
end if;
l_start_int:=l_start_int+1;
end loop;
return l_result;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.xrange(raw,raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.xrange( ''01'', ''11'' )', '''0102030405060708090A0B0C0D0E0F1011''::raw') into l_result;
select compat_tools.f_unit_test('utl_raw.xrange( ''FA'', ''06'' )', '''FAFBFCFDFEFF00010203040506''::raw') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.reverse(raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'reverse(raw)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION UTL_RAW.reverse(
r IN raw)
RETURNS raw
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result raw DEFAULT ''::raw;
begin
for i in reverse UTL_RAW.length(r)..1 LOOP
l_result:=l_result||UTL_RAW.substr(r , i , 1);
end loop;
return l_result;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.reverse(raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.reverse( ''0102030405'' )', '''0504030201''::raw') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.compare(raw,raw,raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'compare(raw,raw,raw)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION UTL_RAW.compare(
r1 IN raw,
r2 IN raw,
pad IN raw DEFAULT '00'::raw)
RETURNS int4
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result int4 DEFAULT 0;
l_r1 raw DEFAULT ''::raw;
l_r2 raw DEFAULT ''::raw;
begin
for i in 1..greatest(UTL_RAW.length(r1),UTL_RAW.length(r2)) LOOP
l_r1:=UTL_RAW.substr(r1 , i , 1);
l_r2:=UTL_RAW.substr(r2 , i , 1);
if l_r1!=l_r2 THEN
if l_r1=''::raw then
l_r1:=pad;
end if;
if l_r2=''::raw then
l_r2:=pad;
end if;
if l_r1!=l_r2 then
l_result:=i;
EXIT;
end if;
end if;
end loop;
return l_result;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.compare(raw,raw,raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.compare( ''010203'', ''01020304'', ''04'' )', '0') into l_result;
select compat_tools.f_unit_test('utl_raw.compare( ''01050304'', ''01020304'' )', '2') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.convert(raw,text,text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'convert(raw,text,text)', '1.0','sql')
then
execute $q$
CREATE OR REPLACE FUNCTION UTL_RAW.convert(r IN raw,
to_charset IN text,
from_charset IN text)
RETURNS raw
LANGUAGE sql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
select rawout(convert_to(convert_from(rawsend(r),from_charset),to_charset))::text::raw;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.convert(raw,text,text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.convert(rawout(''测试''::BYTEA)::text::raw,''GBK'',''UTF8'')', '''B2E2CAD4''::raw') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.bit_and(raw,raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'bit_and(raw,raw)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION utl_raw.bit_and(r1 raw, r2 raw)
RETURNS raw
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result bytea DEFAULT ''::bytea;
l_r1 raw;
l_r2 raw;
l_r1_bytea bytea;
l_r2_bytea bytea;
l_r1_len int8;
l_r2_len int8;
begin
l_r1_len:=utl_raw.length(r1);
l_r2_len:=utl_raw.length(r2);
l_r1:=r1;
l_r2:=r2;
if l_r1_len>l_r2_len then
l_r2:=utl_raw.copies('00'::raw,l_r1_len-l_r2_len)||l_r2;
elsif l_r1_len<l_r2_len then
l_r1:=utl_raw.copies('00'::raw,l_r2_len-l_r1_len)||l_r2;
end if;
l_r1_bytea:=rawsend(l_r1);
l_r2_bytea:=rawsend(l_r2);
l_result:=rawsend(utl_raw.copies('00'::raw,GREATEST(l_r1_len,l_r2_len)));
for i in 0..bit_length(l_result)-1 LOOP
l_result:=set_bit(l_result,i,get_bit(l_r1_bytea,i)&get_bit(l_r2_bytea,i));
end loop;
return rawout(l_result)::text::raw;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.bit_and(raw,raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.bit_and(''1234ffdd'',''1234ffee'')', '''1234FFCC''::raw') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.bit_or(raw,raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'bit_or(raw,raw)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION utl_raw.bit_or(r1 raw, r2 raw)
RETURNS raw
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result bytea DEFAULT ''::bytea;
l_r1 raw;
l_r2 raw;
l_r1_bytea bytea;
l_r2_bytea bytea;
l_r1_len int8;
l_r2_len int8;
begin
l_r1_len:=utl_raw.length(r1);
l_r2_len:=utl_raw.length(r2);
l_r1:=r1;
l_r2:=r2;
if l_r1_len>l_r2_len then
l_r2:=utl_raw.copies('00'::raw,l_r1_len-l_r2_len)||l_r2;
elsif l_r1_len<l_r2_len then
l_r1:=utl_raw.copies('00'::raw,l_r2_len-l_r1_len)||l_r2;
end if;
l_r1_bytea:=rawsend(l_r1);
l_r2_bytea:=rawsend(l_r2);
l_result:=rawsend(utl_raw.copies('00'::raw,GREATEST(l_r1_len,l_r2_len)));
for i in 0..bit_length(l_result)-1 LOOP
l_result:=set_bit(l_result,i,get_bit(l_r1_bytea,i)|get_bit(l_r2_bytea,i));
end loop;
return rawout(l_result)::text::raw;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.bit_or(raw,raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.bit_or(''1234ffdd'',''1234ffee'')', '''1234FFFF''::raw') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.bit_xor(raw,raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'bit_xor(raw,raw)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION utl_raw.bit_xor(r1 raw, r2 raw)
RETURNS raw
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result bytea DEFAULT ''::bytea;
l_r1 raw;
l_r2 raw;
l_r1_bytea bytea;
l_r2_bytea bytea;
l_r1_len int8;
l_r2_len int8;
begin
l_r1_len:=utl_raw.length(r1);
l_r2_len:=utl_raw.length(r2);
l_r1:=r1;
l_r2:=r2;
if l_r1_len>l_r2_len then
l_r2:=utl_raw.copies('00'::raw,l_r1_len-l_r2_len)||l_r2;
elsif l_r1_len<l_r2_len then
l_r1:=utl_raw.copies('00'::raw,l_r2_len-l_r1_len)||l_r2;
end if;
l_r1_bytea:=rawsend(l_r1);
l_r2_bytea:=rawsend(l_r2);
l_result:=rawsend(utl_raw.copies('00'::raw,GREATEST(l_r1_len,l_r2_len)));
for i in 0..bit_length(l_result)-1 LOOP
l_result:=set_bit(l_result,i,get_bit(l_r1_bytea,i)#get_bit(l_r2_bytea,i));
end loop;
return rawout(l_result)::text::raw;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.bit_xor(raw,raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.bit_xor(''1234ffdd'',''1234ffee'')', '''00000033''::raw') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.bit_complement(raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'bit_complement(raw)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION utl_raw.bit_complement(r raw)
RETURNS raw
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result bytea DEFAULT ''::bytea;
begin
l_result:=rawsend(r);
for i in 0..bit_length(l_result)-1 LOOP
l_result:=set_bit(l_result,i,case when get_bit(l_result,i)=1 then 0 else 1 end );
end loop;
return rawout(l_result)::text::raw;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.bit_complement(raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('UTL_raw.bit_complement(''1122FF'')', '''EEDD00''::raw') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.cast_to_number(raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'cast_to_number(raw)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION utl_raw.cast_to_number(r raw)
RETURNS NUMERIC
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
/*2022-02-08 memo:未对输入参数的正确性进行校验，错误的输入会带来错误的输出（by:DarkAthena）*/
DECLARE
l_result NUMERIC DEFAULT 0::numeric;
l_first_byte int4;
l_len int8;
begin
l_len:=utl_raw.length(r);
l_first_byte:=to_number(utl_raw.substr(r,1,1),'xx');
if l_first_byte>128 then
for i in 1..l_len-1 LOOP
l_result:=l_result+(to_number(utl_raw.substr(r,i+1,1),'xx')-1)*(100^(l_first_byte-193-(i-1)));
end loop;
elsif l_first_byte<128 then
for i in 1..l_len-2 LOOP
l_result:=l_result-(101-to_number(utl_raw.substr(r,i+1,1),'xx'))*(100^(62-l_first_byte-(i-1)));
end loop;
elsif l_first_byte=128 then
l_result:=0;
else
RAISE 'raw format error!';
end if;
return l_result;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.cast_to_number(raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.cast_TO_number(''C1020B'')', '1.1') into l_result;
select compat_tools.f_unit_test('utl_raw.cast_TO_number(''3E645B66'')', '-1.1') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.cast_from_number(numeric)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'cast_from_number(numeric)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION utl_raw.cast_from_number(n NUMERIC)
RETURNS raw
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result raw DEFAULT ''::raw;
l_len int4;
l_x NUMERIC;
l_pos int4 DEFAULT 1::int4;
begin
l_len:=ceil((pg_catalog.length(ceil(abs(n)))/2))::int8;
if n>0 then
l_result:=to_hex(193+(l_len-1))::raw;
LOOP
l_x:=trunc(n,-(l_len-l_pos)*2)-trunc(n,-(l_len-l_pos+1)*2);
l_result:=rawcat(l_result,to_hex((l_x/(100^(l_len-l_pos))+1)::int4)::raw);
if trunc(n,-(l_len-l_pos)*2)=n then
exit;
end if;
l_pos:=l_pos+1;
end loop;
elsif n<0 then
l_result:=to_hex(62-(l_len-1))::raw;
LOOP
l_x:=trunc(n,-(l_len-l_pos)*2)-trunc(n,-(l_len-l_pos+1)*2);
l_result:=rawcat(l_result,to_hex(101+(l_x/(100^(l_len-l_pos)))::int4)::raw);
if trunc(n,-(l_len-l_pos)*2)=n then
exit;
end if;
l_pos:=l_pos+1;
end loop;
l_result:=rawcat(l_result,'66'::raw);
elsif n=0 then
l_result:='80'::raw;
else
RAISE 'NUMERIC error!';
end if;
return l_result;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.cast_from_number(numeric): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.cast_from_number(1.1)', '''C1020B''::raw') into l_result;
select compat_tools.f_unit_test('utl_raw.cast_from_number(-1.1)', '''3E645B66''::raw') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.cast_to_binary_integer(raw,int1)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'cast_to_binary_integer(raw,int1)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION utl_raw.cast_to_binary_integer(r IN RAW,
endianess IN int1 DEFAULT 1)
RETURNS int4
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result int4;
begin
if endianess in (1,3) then
l_result:=to_number(r::text,'xxxxxxxx');
elsif endianess =2 then
l_result:=to_number(utl_raw.reverse(r)::text,'xxxxxxxx');
else
RAISE 'invaild endianess!';
end if;
return l_result;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.cast_to_binary_integer(raw,int1): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.cast_to_binary_integer(''FF00'')', '65280') into l_result;
select compat_tools.f_unit_test('utl_raw.cast_to_binary_integer(''FF00'',2)', '255') into l_result;
-- =========================================================================
-- =========================================================================
-- utl_raw.cast_from_binary_integer(int4,int1)
-- =========================================================================
begin
--移除int8版本
select compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'cast_from_binary_integer(int8,int1)', '2.0') into l_result;
delete from compat_tools.compat_version where object_name = 'utl_raw.cast_from_binary_integer(int8,int1)';
delete from temp_result where object_name = 'utl_raw.cast_from_binary_integer(int8,int1)';
if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'cast_from_binary_integer(int4,int1)', '2.0')
then
execute $q$
CREATE OR REPLACE FUNCTION utl_raw.cast_from_binary_integer(n IN int4,
endianess IN int1 DEFAULT 1)
RETURNS raw
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result raw DEFAULT ''::raw;
begin
if endianess in (1,3) then
l_result:=lpad(to_char(n,'fmxxxxxxxx'),8,'0')::raw;
elsif endianess =2 then
l_result:=utl_raw.reverse(replace(lpad(to_char(n,'fmxxxxxxxx'),8,'0'),' ','0')::raw);
else
RAISE 'invaild endianess!';
end if;
return l_result;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in utl_raw.cast_from_binary_integer(int4,int1): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- 测试用例：
-- ----------
select compat_tools.f_unit_test('utl_raw.cast_from_binary_integer(65280)', '''0000FF00''::RAW') into l_result;
select compat_tools.f_unit_test('utl_raw.cast_from_binary_integer(65280,2)', '''00FF0000''::RAW') into l_result;
select compat_tools.f_unit_test('utl_raw.cast_from_binary_integer(-652800)', '''FFF60A00''::RAW') into l_result;
-- =========================================================================
-- =========================================================================
-- dbms_lob
-- 参考:https://gitee.com/darkathena/opengauss-oracle/blob/main/oracle-package/dbms_lob.sql
-- =========================================================================
-- dbms_lob.file_readonly
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'file_readonly()', '1.0','sql')
then
execute $q$
CREATE OR REPLACE FUNCTION dbms_lob.file_readonly()
returns int4
LANGUAGE sql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
SELECT 0::int4;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.file_readonly: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.lob_readonly
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'lob_readonly()', '1.0','sql')
then
execute $q$
CREATE OR REPLACE FUNCTION DBMS_LOB.lob_readonly()
returns int4
LANGUAGE sql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
SELECT 0::int4;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.lob_readonly: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.lob_readwrite
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'lob_readwrite()', '1.0','sql')
then
execute $q$
CREATE OR REPLACE FUNCTION DBMS_LOB.lob_readwrite()
returns int4
LANGUAGE sql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
SELECT 1::int4;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.lob_readwrite: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.lobmaxsize
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'lobmaxsize()', '1.0','sql')
then
execute $q$
CREATE OR REPLACE FUNCTION DBMS_LOB.lobmaxsize()
returns int4
LANGUAGE sql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
--SELECT 18446744073709551615::int8; --只能int4
SELECT 2147483647::int4;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.lobmaxsize: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.getlength(blob)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'getlength(blob)', '1.0','sql')
then
execute $q$
CREATE OR REPLACE FUNCTION DBMS_LOB.getlength(lob_loc in BLOB)
RETURNS INT4
LANGUAGE SQL
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
SELECT pg_catalog.length(rawsend(lob_loc))::INT4;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.getlength(blob): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.getlength(clob)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'getlength(clob)', '1.0','sql')
then
execute $q$
CREATE OR REPLACE FUNCTION DBMS_LOB.getlength(lob_loc in clob)
RETURNS INT4
LANGUAGE SQL
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
SELECT pg_catalog.length(lob_loc)::INT4;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.getlength(clob): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.open(blob,int)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'open(blob,int4)', '1.0')
then
execute $q$
CREATE OR REPLACE PROCEDURE DBMS_LOB.open(lob_loc IN OUT BLOB,
open_mode IN int4) package
AS
BEGIN
null;
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.open(blob,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.open(clob,int)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'open(clob,int4)', '1.0')
then
execute $q$
CREATE OR REPLACE PROCEDURE DBMS_LOB.open(lob_loc IN OUT CLOB,
open_mode IN int4) package
AS
BEGIN
null;
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.open(clob,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.freetemporary(blob)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'freetemporary(blob)', '1.0')
then
execute $q$
CREATE OR REPLACE PROCEDURE DBMS_LOB.freetemporary(lob_loc IN OUT BLOB)
package
AS
BEGIN
null;
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.freetemporary(blob): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.freetemporary(clob)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'freetemporary(clob)', '1.0')
then
execute $q$
CREATE OR REPLACE PROCEDURE DBMS_LOB.freetemporary(lob_loc IN OUT CLOB)
package
AS
BEGIN
null;
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.freetemporary(clob): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.substr(blob,int4,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'substr(blob,int4,int4)', '1.0','sql')
then
execute $q$
CREATE OR REPLACE FUNCTION DBMS_LOB.substr (lob_loc IN BLOB,
amount IN int4 := 32767,
p_offset IN int4 := 1)
returns RAW
LANGUAGE sql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
SELECT rawout(pg_catalog.SUBSTR(rawsend(lob_loc),p_offset,amount))::text::raw;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.substr(blob,int4,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.substr(clob,int4,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'substr(clob,int4,int4)', '1.0','sql')
then
execute $q$
CREATE OR REPLACE FUNCTION DBMS_LOB.substr (lob_loc IN CLOB,
amount IN int4 := 32767,
p_offset IN int4 := 1)
returns TEXT
LANGUAGE sql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
SELECT pg_catalog.SUBSTR(lob_loc,p_offset,amount);
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.substr(clob,int4,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.instr(blob,raw,int4,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'instr(blob,raw,int4,int4)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION DBMS_LOB.instr(lob_loc IN BLOB,
pattern IN RAW,
p_offset IN int4 := 1,
nth IN int4 := 1)
RETURNS integer
LANGUAGE plpgsql STRICT IMMUTABLE
AS $$
DECLARE
pos integer NOT NULL DEFAULT 0;
occur_number integer NOT NULL DEFAULT 0;
temp_str BYTEA;
beg integer;
i integer;
length integer;
ss_length integer;
loc bytea;
BEGIN
loc:=rawsend(lob_loc);
IF nth <= 0 THEN
RAISE 'argument ''%'' is out of range', occur_index
USING ERRCODE = '22003';
END IF;
IF p_offset > 0 THEN
beg := p_offset - 1;
FOR i IN 1..nth LOOP
temp_str := substring(loc FROM beg + 1);
pos := position(RAWSEND(pattern) IN temp_str);
IF pos = 0 THEN
RETURN 0;
END IF;
beg := beg + pos;
END LOOP;
RETURN beg;
ELSIF p_offset < 0 THEN
ss_length := pg_catalog.length(RAWSEND(pattern));
length := pg_catalog.length(loc);
beg := length + 1 + p_offset;
WHILE beg > 0 LOOP
temp_str := substring(loc FROM beg FOR ss_length);
IF RAWSEND(pattern) = temp_str THEN
occur_number := occur_number + 1;
IF occur_number = nth THEN
RETURN beg;
END IF;
END IF;
beg := beg - 1;
END LOOP;
RETURN 0;
ELSE
RETURN 0;
END IF;
END;
$$ ;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.instr(blob,raw,int4,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.instr(clob,text,int4,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'instr(clob,text,int4,int4)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION DBMS_LOB.instr(lob_loc CLOB, pattern TEXT,
p_offset integer, nth integer)
RETURNS integer
LANGUAGE plpgsql STRICT IMMUTABLE
AS $$
DECLARE
pos integer NOT NULL DEFAULT 0;
occur_number integer NOT NULL DEFAULT 0;
temp_str TEXT;
beg integer;
i integer;
length integer;
ss_length integer;
loc text;
BEGIN
loc:=lob_loc::TEXT;
IF nth <= 0 THEN
RAISE 'argument ''%'' is out of range', occur_index
USING ERRCODE = '22003';
END IF;
IF p_offset > 0 THEN
beg := p_offset - 1;
FOR i IN 1..nth LOOP
temp_str := substring(loc FROM beg + 1);
pos := position(pattern IN temp_str);
IF pos = 0 THEN
RETURN 0;
END IF;
beg := beg + pos;
END LOOP;
RETURN beg;
ELSIF p_offset < 0 THEN
ss_length := char_length(pattern);
length := char_length(loc);
beg := length + 1 + p_offset;
WHILE beg > 0 LOOP
temp_str := substring(loc FROM beg FOR ss_length);
IF pattern = temp_str THEN
occur_number := occur_number + 1;
IF occur_number = nth THEN
RETURN beg;
END IF;
END IF;
beg := beg - 1;
END LOOP;
RETURN 0;
ELSE
RETURN 0;
END IF;
END;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.instr(clob,text,int4,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.createtemporary(blob,bool,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'createtemporary(blob,bool,int4)', '1.0')
then
execute $q$
CREATE or replace PROCEDURE DBMS_LOB.createtemporary(lob_loc IN OUT BLOB,
cache IN BOOLEAN,
dur IN INT4 := 10) package
AS
BEGIN
null;
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.createtemporary(blob,bool,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.createtemporary(clob,bool,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'createtemporary(clob,bool,int4)', '1.0')
then
execute $q$
CREATE or replace PROCEDURE DBMS_LOB.createtemporary(lob_loc IN OUT CLOB,
cache IN BOOLEAN,
dur IN INT4 := 10) package
AS
BEGIN
null;
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.createtemporary(clob,bool,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.close(blob)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'close(blob)', '1.0')
then
execute $q$
CREATE or replace PROCEDURE DBMS_LOB.close(lob_loc IN OUT BLOB)
package
AS
BEGIN
null;
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.close(blob): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.close(clob)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'close(clob)', '1.0')
then
execute $q$
CREATE or replace PROCEDURE DBMS_LOB.close(lob_loc IN OUT CLOB)
package
AS
BEGIN
null;
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.close(clob): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.append(blob,blob)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'append(blob,blob)', '1.0')
then
execute $q$
CREATE or replace PROCEDURE DBMS_LOB.append(dest_lob IN OUT BLOB,
src_lob IN BLOB) package
AS
BEGIN
dest_lob:=rawout(rawsend(dest_lob)||rawsend(src_lob))::text::raw::blob;
END;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.append(blob,blob): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.append(clob,clob)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'append(clob,clob)', '1.0')
then
execute $q$
CREATE or replace PROCEDURE DBMS_LOB.append(dest_lob IN OUT CLOB,
src_lob IN CLOB ) package
AS
BEGIN
dest_lob:=(dest_lob::text||src_lob::text)::clob;
END;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.append(clob,clob): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.compare(blob,blob,int4,int4,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'compare(blob,blob,int4,int4,int4)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION DBMS_LOB.compare(
lob_1 IN BLOB,
lob_2 IN BLOB,
amount IN int4 := 2147483647,
offset_1 IN INTEGER := 1,
offset_2 IN INTEGER := 1)
RETURNS int4
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result int4 DEFAULT 0;
l_r1 bytea DEFAULT ''::bytea;
l_r2 bytea DEFAULT ''::bytea;
len_1 int4;
len_2 int4;
lob1_bytea bytea;
lob2_bytea bytea;
begin
lob1_bytea:=substr(rawsend(lob_1),offset_1,least(amount,pg_catalog.length(rawsend(lob_1))-offset_1+1));
lob2_bytea:=substr(rawsend(lob_2),offset_2,least(amount,pg_catalog.length(rawsend(lob_2))-offset_2+1));
len_1:=pg_catalog.length(lob1_bytea);
len_2:=pg_catalog.length(lob2_bytea);
if len_1<len_2 then
return -1;
elsif len_1>len_2 then
return 1;
end if;
for i in 1..greatest(len_1,len_2) LOOP
l_r1:=pg_catalog.substr(lob1_bytea , i , 1);
l_r2:=pg_catalog.substr(lob2_bytea , i , 1);
if l_r1!=l_r2 THEN
if l_r1>l_r2 then
l_result:=1;
else
l_result:=-1;
end if;
EXIT;
end if;
end loop;
return l_result;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.compare(blob,blob,int4,int4,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.compare(clob,clob,int4,int4,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'compare(clob,clob,int4,int4,int4)', '1.0')
then
execute $q$
CREATE OR REPLACE FUNCTION DBMS_LOB.compare(
lob_1 IN CLOB,
lob_2 IN CLOB,
amount IN int4 := 2147483647,
offset_1 IN INTEGER := 1,
offset_2 IN INTEGER := 1)
RETURNS int4
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
l_result int4 DEFAULT 0;
l_r1 text DEFAULT ''::text;
l_r2 text DEFAULT ''::text;
len_1 int4;
len_2 int4;
lob1_text text;
lob2_text text;
begin
lob1_text:=substr(lob_1::text,offset_1,least(amount,pg_catalog.length(lob_1::text)-offset_1+1));
lob2_text:=substr(lob_2::text,offset_2,least(amount,pg_catalog.length(lob_2::text)-offset_2+1));
len_1:=pg_catalog.length(lob1_text);
len_2:=pg_catalog.length(lob2_text);
if len_1<len_2 then
return -1;
elsif len_1>len_2 then
return 1;
end if;
for i in 1..greatest(len_1,len_2) LOOP
l_r1:=pg_catalog.substr(lob1_text , i , 1);
l_r2:=pg_catalog.substr(lob2_text , i , 1);
if l_r1!=l_r2 THEN
if l_r1::bytea>l_r2::bytea then
l_result:=1;
else
l_result:=-1;
end if;
EXIT;
end if;
end loop;
return l_result;
end;
$$;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.compare(clob,clob,int4,int4,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.copy(blob,blob,int4,int4,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'copy(blob,blob,int4,int4,int4)', '1.0')
then
execute $q$
CREATE or replace PROCEDURE DBMS_LOB.copy(dest_lob IN OUT BLOB,
src_lob IN BLOB,
amount IN INTEGER,
dest_offset IN INTEGER := 1,
src_offset IN INTEGER := 1) package
AS
DECLARE
dest_bytea bytea;
end_bytea bytea DEFAULT ''::bytea;
null_bytea bytea DEFAULT '\x00'::bytea;
dest_bytea_length int4;
pad_bytea bytea DEFAULT ''::bytea;
BEGIN
dest_bytea:=rawsend(dest_lob);
dest_bytea_length:=pg_catalog.length(dest_bytea);
if dest_bytea_length>dest_offset+amount-1 then
end_bytea:=substr(dest_bytea,dest_offset+amount);
end if;
if dest_bytea_length<dest_offset THEN
for i in 1..dest_offset-dest_bytea_length-1 LOOP
pad_bytea:=pad_bytea||null_bytea;
end loop;
end if;
dest_lob:=rawout(substr(dest_bytea,1,dest_offset-1)||pad_bytea||substr(rawsend(src_lob),src_offset,amount)||end_bytea)::text::raw::blob;
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.copy(blob,blob,int4,int4,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.copy(clob,clob,int4,int4,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'copy(clob,clob,int4,int4,int4)', '1.0')
then
execute $q$
CREATE or replace PROCEDURE DBMS_LOB.copy(dest_lob IN OUT CLOB,
src_lob IN CLOB,
amount IN INTEGER,
dest_offset IN INTEGER := 1,
src_offset IN INTEGER := 1) package
AS
DECLARE
dest_text text;
end_text text DEFAULT ''::text;
null_text text DEFAULT ' '::text;
dest_text_length int4;
pad_text text DEFAULT ''::text;
BEGIN
dest_text:=dest_lob::text;
dest_text_length:=pg_catalog.length(dest_text);
if dest_text_length>dest_offset+amount-1 then
end_text:=substr(dest_text,dest_offset+amount);
end if;
if dest_text_length<dest_offset THEN
for i in 1..dest_offset-dest_text_length-1 LOOP
pad_text:=pad_text||null_text;
end loop;
end if;
dest_lob:=(substr(dest_text,1,dest_offset-1)||pad_text||substr(src_lob::text,src_offset,amount)||end_text)::clob;
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.copy(clob,clob,int4,int4,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.erase(blob,int4,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'erase(blob,int4,int4)', '1.0')
then
execute $q$
CREATE or replace PROCEDURE DBMS_LOB.erase(lob_loc IN OUT BLOB,
amount IN OUT INTEGER,
p_offset IN INTEGER := 1) package
AS
DECLARE
lob_length int4;
lob_bytea bytea;
null_bytea bytea DEFAULT '\x00'::bytea;
pad_bytea bytea DEFAULT ''::bytea;
end_bytea bytea;
begin
lob_bytea:=rawsend(lob_loc);
lob_length:=pg_catalog.length(lob_bytea);
if amount<lob_length-p_offset+1 then
end_bytea:=substr(lob_bytea,p_offset+amount+1);
end if;
amount:=least(amount,lob_length-p_offset+1);
for i in 1..amount LOOP
pad_bytea:=pad_bytea||null_bytea;
end loop;
lob_loc:=rawout(pg_catalog.substr(lob_bytea,0,p_offset-1)||pad_bytea||end_bytea)::text::raw;
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.erase(blob,int4,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.erase(clob,int4,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'erase(clob,int4,int4)', '1.0')
then
execute $q$
CREATE or replace PROCEDURE DBMS_LOB.erase(lob_loc IN OUT CLOB,
amount IN OUT INTEGER,
p_offset IN INTEGER := 1) package
AS
DECLARE
lob_length int4;
lob_text text;
null_text text DEFAULT ' '::text;
pad_text text DEFAULT ''::text;
end_text text;
begin
lob_text:=lob_loc::text;
lob_length:=pg_catalog.length(lob_text);
if amount<lob_length-p_offset+1 then
end_text:=substr(lob_text,p_offset+amount+1);
end if;
amount:=least(amount,lob_length-p_offset+1);
for i in 1..amount LOOP
pad_text:=pad_text||null_text;
end loop;
lob_loc:=(pg_catalog.substr(lob_text,0,p_offset-1)||pad_text||end_text)::clob;
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.erase(clob,int4,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.read(blob,int4,int4,raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'read(blob,int4,int4,raw)', '1.0')
then
execute $q$
CREATE or replace PROCEDURE DBMS_LOB.read(lob_loc IN BLOB,
amount IN OUT INTEGER,
p_offset IN INTEGER,
buffer OUT RAW) package
as
DECLARE
tmp_bytea bytea;
BEGIN
tmp_bytea:=pg_catalog.substr(rawsend(lob_loc),p_offset,amount);
if pg_catalog.length(tmp_bytea)=0 or tmp_bytea is null
then raise NO_DATA_FOUND;
end if;
buffer:=rawout(tmp_bytea)::text::raw;
amount:=pg_catalog.length(tmp_bytea);
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.read(blob,int4,int4,raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.read(clob,int4,int4,text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'read(clob,int4,int4,text)', '1.0')
then
execute $q$
CREATE or replace PROCEDURE DBMS_LOB.read(lob_loc IN CLOB,
amount IN OUT INTEGER,
p_offset IN INTEGER,
buffer OUT TEXT) package
as
BEGIN
buffer:=pg_catalog.substr(lob_loc::text,p_offset,amount);
if pg_catalog.length(buffer)=0 or buffer is null
then raise NO_DATA_FOUND;
end if;
amount:=pg_catalog.length(buffer);
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.read(clob,int4,int4,text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.trim(blob,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'trim(blob,int4)', '1.0')
then
execute $q$
CREATE or replace PROCEDURE DBMS_LOB.trim(lob_loc IN OUT BLOB,
newlen IN INTEGER) package
as
BEGIN
lob_loc:=rawout(pg_catalog.substr(rawsend(lob_loc),1,newlen))::text::raw::blob;
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.trim(blob,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.trim(clob,int4)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'trim(clob,int4)', '1.0')
then
execute $q$
CREATE or replace PROCEDURE DBMS_LOB.trim(lob_loc IN OUT CLOB,
newlen IN INTEGER) package
as
BEGIN
lob_loc:=pg_catalog.substr(lob_loc::text,1,newlen)::clob;
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.trim(clob,int4): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.write(blob,int4,int4,raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'write(blob,int4,int4,raw)', '1.0')
then
execute $q$
CREATE or replace PROCEDURE DBMS_LOB.write(lob_loc IN OUT BLOB,
amount IN INTEGER,
p_offset IN INTEGER,
buffer IN RAW) package
AS
BEGIN
DBMS_LOB.copy(lob_loc,buffer::blob,amount,p_offset,1);
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.write(blob,int4,int4,raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.write(clob,int4,int4,text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'write(clob,int4,int4,text)', '1.0')
then
execute $q$
CREATE or replace PROCEDURE DBMS_LOB.write(lob_loc IN OUT cLOB,
amount IN INTEGER,
p_offset IN INTEGER,
buffer IN text) package
AS
BEGIN
DBMS_LOB.copy(lob_loc,buffer::text,amount,p_offset,1);
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.write(clob,int4,int4,text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.writeappend(blob,int4,raw)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'writeappend(blob,int4,raw)', '1.0')
then
execute $q$
CREATE or replace PROCEDURE DBMS_LOB.writeappend(lob_loc IN OUT BLOB,
amount IN INTEGER,
buffer IN RAW) package
AS
/*未做amount校验 by DarkAthena 2022-05-03*/
BEGIN
lob_loc:=RAWOUT(RAWSEND(lob_loc)||RAWSEND(buffer))::TEXT::RAW::BLOB;
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.writeappend(blob,int4,raw): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_lob.writeappend(clob,int4,text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'writeappend(clob,int4,text)', '1.0')
then
execute $q$
CREATE or replace PROCEDURE DBMS_LOB.writeappend(lob_loc IN OUT CLOB,
amount IN INTEGER,
buffer IN TEXT) package
AS
/*未做amount校验 by DarkAthena 2022-05-03*/
BEGIN
lob_loc:=(lob_loc::TEXT||buffer)::CLOB;
end;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_lob.writeappend(clob,int4,text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- =========================================================================
-- dbms_snapshot.refresh(text,text)
-- =========================================================================
begin
if compat_tools.drop_compat_package('PROCEDURE', 'dbms_snapshot', 'refresh(text,text)', '1.0')
then
execute $q$
create or replace PROCEDURE dbms_snapshot.refresh(list IN text,
method IN text := 'F') package
is
l_exists int;
l_search_path text;
begin
l_search_path:=current_setting('search_path');
FOR rec IN
( SELECT regexp_substr(view_name, '(.+)(?=\.)') schema_name,
CASE WHEN regexp_substr(view_name, '(.+)(?=\.)') IS NULL
THEN view_name
ELSE regexp_replace(view_name, '(.+)(\.)(.+)', '\3')
END view_name
FROM (SELECT unnest(string_to_array(list, ',')) view_name) )
loop
if rec.schema_name is null then
for rec2 in (select trim(unnest(string_to_array(l_search_path,','))) schema_name) loop
select count(1) into l_exists from pg_catalog.gs_matviews
where schemaname= case when rec2.schema_name ='"$user"' then user else rec2.schema_name end
and matviewname=rec.view_name;
if l_exists>0 then
rec.schema_name:=case when rec2.schema_name ='"$user"' then user else rec2.schema_name end;
exit;
end if;
end loop;
end if;
select count(1) into l_exists from pg_catalog.gs_matviews
where schemaname=rec.schema_name
and matviewname=rec.view_name;
if l_exists=0 then
raise 'MATERIALIZED VIEW %.% does not exist',rec.schema_name,rec.view_name;
end if;
IF UPPER(method) = 'F' THEN
EXECUTE immediate 'REFRESH INCREMENTAL MATERIALIZED VIEW '|| rec.schema_name||'.'||rec.view_name;
ELSIF UPPER(method) = 'C' THEN
EXECUTE immediate 'REFRESH MATERIALIZED VIEW '||rec.schema_name||'.'|| rec.view_name;
ELSE
raise 'illegal argument for function';
END IF;
END LOOP;
END;
$q$;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in dbms_snapshot.refresh(text,text): %', SQLERRM;
rollback;
end;
-- =========================================================================
end;
$PKG_CREATION$ language plpgsql;
-- Show result & Exit
do $RESULT_SUMMARY$
declare
l_app_name text := current_setting('application_name');
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;
end if;
end;
$RESULT_SUMMARY$ language plpgsql;
\q
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。
马建仓 AI 助手