一、find_in_set 函数替换
V8R3替换
create or replace function find_in_set(str text,strlist text)
return int as
declare
etv int;
len1 int;
arrlen int;
coun int;
tempstr text;
temparr text[];
begin
if strlist is null or strlist=''
then return 0;
end if;
temparr=string_to_array(strlist, ',');
arrlen=array_length(temparr,1);
raise notice 'arrlen=%',arrlen;
coun=1;
loop if coun>arrlen then exit;
end if;
if str=temparr[coun] then return coun;
end if;
coun=coun+1;
end loop;
return 0;
end;
如果作为条件判断需要改写为
SELECT * FROM sjw_gxwh
WHERE pid = ANY(string_to_array('a,b,c,d',','))
V8R6替换
CREATE OR REPLACE FUNCTION find_in_set(str text, strlist text) RETURNS int
AS
DECLARE b1 VARCHAR;
begin
b1:=array_position(string_to_array($2, ','),$1);
RETURN b1;
end;
二、datediff函数替换
方法一：
适用于V8R6：
create extension kdb_date_function;
SELECT datediff('hour','1900-01-01 02:00:00','2000-12-31 02:00:00');
方法二：
适用于V8R3：
create or replace function datediff(para1 text,para2 timestamp,para3 timestamp) return bigint
as
declare
diffm bigint;
ny bigint;
nm bigint;
begin
diffm:=extract(epoch from age(para3,para2));
ny:=extract(year from age(para3,para2));
nm:=extract(month from age(para3,para2));
if para1='second' then
return diffm;
elseif para1='minute' then
return floor(diffm/60);
elseif para1='hour' then
return floor(diffm/3600);
elseif para1='day' then
return floor(diffm/86400);
elseif para1='month' then
return ny*12+nm;
elseif para1='year' then
return ny;
else
raise warning 'para1 not support "%"!',para1;
return null;
end if;
end;
三、SUBSTRING_INDEX 函数转换
CREATE OR REPLACE FUNCTION SUBSTRING_INDEX(str VARCHAR, sp VARCHAR, idx INTEGER)
RETURNS VARCHAR
LANGUAGE PLSQL
AS
DECLARE
_str_part VARCHAR DEFAULT NULL;
_str_array varchar[] NOT NULL DEFAULT ARRAY[]::VARCHAR[];
_str_array_len INTEGER DEFAULT 0;
_result_array varchar[] NOT NULL DEFAULT ARRAY[]::VARCHAR[];
_result_array_len INTEGER DEFAULT 0;
_i INTEGER DEFAULT -1;
_sj INTEGER DEFAULT 0;
_ej INTEGER DEFAULT 0;
_rs VARCHAR DEFAULT '';
BEGIN
IF idx = 0 OR sp = '' THEN
RETURN '';
END IF;
SELECT POSITION(sp IN str) INTO _i;
IF _i = 0 THEN
_str_array = array_append(_str_array,str);
end if;
while _i != 0 loop
_str_array = array_append(_str_array,SUBSTRING(str FROM 0 FOR (_i-1))::varchar);
str = SUBSTRING(str FROM _i+1);
SELECT POSITION(sp IN str) INTO _i;
IF _i = 0 THEN
_str_array = array_append(_str_array,str);
end if;
END loop;
_str_array_len = array_length(_str_array, 1);
IF idx < 0 THEN
_sj = _str_array_len + idx + 1;
_ej = _str_array_len;
ELSE
_sj = 1;
_ej = idx;
IF _ej > _str_array_len THEN
_ej = _str_array_len;
END IF;
END IF;
_result_array = _str_array[_sj:_ej];
IF _result_array IS NOT NULL THEN
_result_array_len = array_length(_result_array, 1);
FOR ix IN 1.._result_array_len loop
_rs = CONCAT(_rs, _result_array[ix]);
IF ix != _result_array_len THEN
_rs = CONCAT(_rs, sp);
END IF;
END loop;
END IF;
RETURN _rs;
END;
四、IFNULL函数转换
CREATE OR REPLACE INTERNAL FUNCTION IFNULL(IN a1 VARCHAR,IN a2 VARCHAR) RETURNS VARCHAR
AS $$
DECLARE a3 VARCHAR;
BEGIN
a3:= nvl(a1,a2);
RETURN a3;
END;
$$ LANGUAGE plsql;
五、str_to_date函数转换
create or replace function str_to_date(para1 text,para2 text) return text
as
declare
form1 text;
begin
--form1=replace(para2,'%M','Month');
form1=replace(para2,'%W','Day');
--form1=replace(form1,'%D','DDth');
form1=replace(form1,'%Y','YYYY');
--form1=replace(form1,'%y','yy');
form1=replace(form1,'%a','Dy');
form1=replace(form1,'%d','DD');
form1=replace(form1,'%e','DD');
form1=replace(form1,'%m','MM');
form1=replace(form1,'%c','MM');
form1=replace(form1,'%b','Mon');
form1=replace(form1,'%j','DDD');
form1=replace(form1,'%H','HH24');
form1=replace(form1,'%k','HH24');
--form1=replace(form1,'%h','HH');
--form1=replace(form1,'%I','HH');
form1=replace(form1,'%l','HH');
form1=replace(form1,'%i','MI');
form1=replace(form1,'%r','HH:MI:SS');
form1=replace(form1,'%T','HH24:MI:SS');
--form1=replace(form1,'%S','SS');
form1=replace(form1,'%s','SS');
form1=replace(form1,'%%','%');
if length(form1) > 10 then
return to_timestamp(para1,form1);
else
return to_date(para1,form1);
end if;
end;
V8R6转换
create extension kdb_date_function;
select str_to_date('2022-09-09','YYYY-mm-DD');
六、WEEKDAY函数转换
CREATE OR REPLACE FUNCTION WEEKDAY(dt DATE)
RETURNS INTEGER
LANGUAGE PLSQL
AS
DECLARE
_wk_num INTEGER DEFAULT NULL;
BEGIN
SELECT extract(dow from dt) INTO _wk_num;
_wk_num = _wk_num-1;
IF _wk_num = -1 THEN
_wk_num = 6;
END IF;
RETURN _wk_num;
END;
七、date_add函数转换
create or replace function date_add(v_date text , v_interval interval)
returns text as $$
declare
v_rt text;
begin
select to_char(v_date::timestamp(0) + v_interval,'yyyy-mm-dd hh24:mi:ss' ) into v_rt;
if length(v_date) = 10 and v_rt like '% 00:00:00' then
select substr(v_rt,0,10) into v_rt;
end if;
return v_rt;
end;
$$
LANGUAGE plsql;
V8R6: date_add
create extension kdb_date_function ;
select date_add('2000-10-10'::timestamp,interval '1 year');
八、date_format函数转换,V8R6自带的函数
R3：2中方法：
1.使用to_char替代
Mysql：select date_format(CURRENT_TIMSTAMP, ‘%Y-%m-%d %H:%i:%s’)
KES： select to_char (CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss')
2.创建函数
date_format函数（不敏感库）
create or replace function date_format(para1 timestamp,para2 text) return text
as
declare
form1 text;
begin
--form1=replace(para2,'%M','Month');
form1=replace(para2,'%W','Day');
--form1=replace(form1,'%D','DDth');
form1=replace(form1,'%Y','YYYY');
--form1=replace(form1,'%y','yy');
form1=replace(form1,'%a','Dy');
form1=replace(form1,'%d','DD');
form1=replace(form1,'%e','DD');
form1=replace(form1,'%m','MM');
form1=replace(form1,'%c','MM');
form1=replace(form1,'%b','Mon');
form1=replace(form1,'%j','DDD');
form1=replace(form1,'%H','HH24');
form1=replace(form1,'%k','HH24');
--form1=replace(form1,'%h','HH');
--form1=replace(form1,'%I','HH');
form1=replace(form1,'%l','HH');
form1=replace(form1,'%i','MI');
form1=replace(form1,'%r','HH:MI:SS');
form1=replace(form1,'%T','HH24:MI:SS');
--form1=replace(form1,'%S','SS');
form1=replace(form1,'%s','SS');
form1=replace(form1,'%%','%');
return to_char(para1,form1);
end;
date_format函数（敏感库）
create or replace function date_format(para1 timestamp,para2 text) return text
as
declare
form1 text;
begin
form1=replace(para2,'%M','Month');
form1=replace(form1,'%W','Day');
form1=replace(form1,'%D','DDth');
form1=replace(form1,'%Y','YYYY');
form1=replace(form1,'%y','yy');
form1=replace(form1,'%a','Dy');
form1=replace(form1,'%d','DD');
form1=replace(form1,'%e','DD');
form1=replace(form1,'%m','MM');
form1=replace(form1,'%c','MM');
form1=replace(form1,'%b','Mon');
form1=replace(form1,'%j','DDD');
form1=replace(form1,'%H','HH24');
form1=replace(form1,'%k','HH24');
form1=replace(form1,'%h','HH');
form1=replace(form1,'%I','HH');
form1=replace(form1,'%l','HH');
form1=replace(form1,'%i','MI');
form1=replace(form1,'%r','HH:MI:SS');
form1=replace(form1,'%T','HH24:MI:SS');
form1=replace(form1,'%S','SS');
form1=replace(form1,'%s','SS');
form1=replace(form1,'%%','%');
return to_char(para1,form1);
end;
九、YEAR函数转换
CREATE OR REPLACE INTERNAL FUNCTION YEAR(dtime DATE) RETURNS BIGINT AS '
BEGIN
RETURN extract(year from dtime);
END;
' LANGUAGE plsql;
十、MAKEDATE函数转换
CREATE OR REPLACE FUNCTION MAKEDATE(y INTEGER, days INTEGER)
RETURNS DATE
LANGUAGE PLSQL
AS
DECLARE
_m_date DATE DEFAULT NULL;
BEGIN
IF y >= 0 AND days > 0 THEN
IF y = 0 THEN
y = 2000;
END IF;
SELECT (concat(y,'-01-01')::date + ((days-1) || 'day')::INTERVAL)::date INTO _m_date;
END IF;
RETURN _m_date;
END;
十一、timestampdiff函数转换
create or replace function timestampdiff(para1 text,para2 timestamp,para3 timestamp) return bigint
as
declare
diff interval day to second;
diffs bigint;
diffm bigint;
diffh bigint;
diffd bigint;
nyy bigint;
ny bigint;
nm bigint;
begin
diff:=para2-para3;
diffs:=extract(second from diff);
diffm:=extract(minute from diff);
diffh:=extract(hour from diff);
diffd:=extract(day from diff);
ny:=extract(year from age(para3,para2));
nm:=extract(month from age(para3,para2));
nyy:=extract(year from para3)-extract(year from para2);
if para1='second' then
return ((diffd*24+diffh)*60+diffm)*60+diffs;
elseif para1='minute' then
return (diffd*24+diffh)*60+diffm;
elseif para1='hour' then
return diffd*24+diffh;
elseif para1='day' then
return diffd;
elseif para1='month' then
return ny*12+nm;
elseif para1='year' then
return nyy;
else
raise warning 'para1 not support "%"!',para1;
return null;
end if;
end;
十二、date_sub函数转换
create or replace function date_sub(v_date text , v_interval interval)
returns text as $$
declare
v_rt text;
begin
select to_char(v_date::timestamp(0) - v_interval,'yyyy-mm-dd hh24:mi:ss' ) into v_rt;
if length(v_date) = 10 and v_rt like '% 00:00:00' then
select substr(v_rt,0,10) into v_rt;
end if;
return v_rt;
end;
$$
LANGUAGE plsql;
十三、conv函数转换
create or replace function conv(numb text,from_base int,to_base int)
returns text
as $$
declare
ret varchar;
begin
case from_base
when 16 then
case to_base
when 10 then
select cast(cast(('x' || cast($1 as text)) as bit(8)) as int) into ret;
when 2 then
select $1::bit(8) into ret;
end case;
when 10 then
case to_base
when 2 then
select $1::int::bit(8) into ret;
when 16 then
select to_hex(cast($1 as int)) into ret;
end case;
end case;
return ret;
end;
$$ language plsql;
十四、inet_aton函数转换
CREATE FUNCTION inet_aton(text) RETURNS bigint AS
$$
SELECT split_part($1,'.',1)::bigint*16777216 + split_part($1,'.',2)::bigint*65536 +
split_part($1,'.',3)::bigint*256 + split_part($1,'.',4)::bigint;
$$ LANGUAGE SQL
IMMUTABLE RETURNS NULL ON NULL INPUT;
或者
create or replace function inet_aton(ip text) returns int8 as $$
declare
v int;
res int8 := 0;
i int := 3;
begin
foreach v in array string_to_array(ip, '.')
loop
res := res+v*(256^i);
i := i-1;
end loop;
return res;
end;
$$ language plsql;
十五、inet_ntoa函数转换
create or replace function inet_ntoa(ip int8) returns text as $$
declare
res text := '';
begin
res := res || ((ip >> 24) & (2^8-1)::int);
res := res || '.' || ((ip >> 16) & (2^8-1)::int);
res := res || '.' || ((ip >> 8) & (2^8-1)::int);
res := res || '.' || (ip & (2^8-1)::int);
return res;
end;
$$ language plsql;
十六、unix_timestamp函数转化
CREATE OR REPLACE INTERNAL FUNCTION unix_timestamp(dtime TIMESTAMP WITH TIME ZONE)
RETURNS INT AS $$
BEGIN
RETURN date_part('epoch', CAST(dtime AS TIMESTAMP WITH TIME ZONE));
END;
$$ LANGUAGE plsql;
十七、from_unixtime函数转换
CREATE OR REPLACE INTERNAL FUNCTION from_unixtime(unix_timestamp INT)
RETURNS TIMESTAMP AS $$
BEGIN
RETURN to_timestamp(unix_timestamp) + interval '8' hour;
END;
$$ LANGUAGE plsql;
十八、LOCATE(substr,str)函数转换
CREATE OR REPLACE INTERNAL FUNCTION locate(a1 varchar,a2 varchar)
RETURNS varchar AS $$
BEGIN
return position($1 in $2 );
END;
$$ LANGUAGE plsql;
十九、LOCATE(substr,str,pos)函数转换
CREATE OR REPLACE INTERNAL FUNCTION locate(a1 varchar,a2 varchar,a3 varchar)
RETURNS varchar AS $$
BEGIN
if position($1 in $2 ) then
return (position($1 in substring($2 from $3 for 100))+($3-1));
else
return 0;
end if;
END;
$$ LANGUAGE plsql;
二十、TIME_TO_SEC 函数替换
VRR6:
select extract(epoch from '00:01:06'::time);
V8R3:
SELECT floor(extract(epoch FROM '00:01:06'::TIME));
二十一、group concat函数替换
string_agg(expression,delimiter) 输入值连接成一个串，用定界符分隔
例子：
SELECT string_agg(a, ',' ORDER BY a) FROM table;
select id,string_agg(name,',' order by id) from aa group by id ;
二十二、SUBSTRING_INDEX
使用 CHARINDEX(SUBSTR,STR) 替代或者定义
kingbase需要自定义：
CREATE OR REPLACE FUNCTION SUBSTRING_INDEX(str VARCHAR, sp VARCHAR, idx INTEGER)
RETURNS VARCHAR
LANGUAGE PLSQL
AS
DECLARE
_str_part VARCHAR DEFAULT NULL;
_str_array varchar[] NOT NULL DEFAULT ARRAY[]::VARCHAR[];
_str_array_len INTEGER DEFAULT 0;
_result_array varchar[] NOT NULL DEFAULT ARRAY[]::VARCHAR[];
_result_array_len INTEGER DEFAULT 0;
_i INTEGER DEFAULT -1;
_sj INTEGER DEFAULT 0;
_ej INTEGER DEFAULT 0;
_rs VARCHAR DEFAULT '';
BEGIN
IF idx = 0 OR sp = '' THEN
RETURN '';
END IF;
SELECT POSITION(sp IN str) INTO _i;
IF _i = 0 THEN
_str_array = array_append(_str_array,str);
end if;
while _i != 0 loop
_str_array = array_append(_str_array,SUBSTRING(str FROM 0 FOR (_i-1))::varchar);
str = SUBSTRING(str FROM _i+1);
SELECT POSITION(sp IN str) INTO _i;
IF _i = 0 THEN
_str_array = array_append(_str_array,str);
end if;
END loop;
_str_array_len = array_length(_str_array, 1);
IF idx < 0 THEN
_sj = _str_array_len + idx + 1;
_ej = _str_array_len;
ELSE
_sj = 1;
_ej = idx;
IF _ej > _str_array_len THEN
_ej = _str_array_len;
END IF;
END IF;
_result_array = _str_array[_sj:_ej];
IF _result_array IS NOT NULL THEN
_result_array_len = array_length(_result_array, 1);
FOR ix IN 1.._result_array_len loop
_rs = CONCAT(_rs, _result_array[ix]);
IF ix != _result_array_len THEN
_rs = CONCAT(_rs, sp);
END IF;
END loop;
END IF;
RETURN _rs;
END;
V8R6替换
CREATE OR REPLACE FUNCTION substring_index(varchar, varchar, integer)
RETURNS varchar AS $$
DECLARE
tokens varchar[];
length integer ;
indexnum integer;
BEGIN
tokens := pg_catalog.string_to_array($1, $2);
length := pg_catalog.array_upper(tokens, 1);
indexnum := length - ($3 * -1) + 1;
IF $3 >= 0 THEN
RETURN pg_catalog.array_to_string(tokens[1:$3], $2);
ELSE
RETURN pg_catalog.array_to_string(tokens[indexnum:length], $2);
END IF;
END;
$$
IMMUTABLE STRICT LANGUAGE PLPGSQL;
二十三、WEEKDAY
CREATE OR REPLACE FUNCTION WEEKDAY(dt DATE)
RETURNS INTEGER
LANGUAGE PLSQL
AS
DECLARE
_wk_num INTEGER DEFAULT NULL;
BEGIN
SELECT extract(dow from dt) INTO _wk_num;
_wk_num = _wk_num-1;
IF _wk_num = -1 THEN
_wk_num = 6;
END IF;
RETURN _wk_num;
END;
二十四、LOCATE(substr,str,pos)
CREATE OR REPLACE INTERNAL FUNCTION locate(a1 varchar,a2 varchar,a3 varchar)
RETURNS varchar AS $$
BEGIN
if position($1 in $2 ) then
return (position($1 in substring($2 from $3 for 100))+($3-1));
else
return 0;
end if;
END;
$$ LANGUAGE plsql;
二十五、 ON DUPLICATE KEY UPDATE
insert into test(did,dname) values (1,'a') on conflict(did) do update set dname = EXCLUDED.dname;
INSERT INTO test(id,id1,id2) VALUES (2,'a',1) ON CONFLICT ON CONSTRAINT PUBLIC_test_CONSTRAINT_1 DO UPDATE SET id2 = EXCLUDED.ID2;
二十六、CURDATE() 函数替换
使用：select current_date() ;
CREATE OR REPLACE FUNCTION CURDATE()
RETURNS pg_catalog.date AS $$
BEGIN
return current_date;
end;
$$
LANGUAGE PLSQL;
二十六、insert ignore into ........
改写：
insert into elt_temp values(....) on conflict on constraint prymary_key_name do nothing
二十七、 locate 函数
CREATE OR REPLACE INTERNAL FUNCTION locate(a1 varchar,a2 varchar)
RETURNS varchar AS $$
BEGIN
return position($1 in $2 );
END;
$$ LANGUAGE plsql;
二十八、to_days 函数
create function to_days (v_time date) returns integer as
declare
v_cou integer;
begin
select trunc(v_time) - to_date('00000101','yyyymmdd') into v_cou;
return v_cou;
end;
二十九 from_days 函数
create function from_days (v_num integer) returns date as
declare
v_date date;
begin
select to_date('00000101','yyyymmdd') + v_num into v_date;
return v_date;
end;
create function from_days (v_num integer) returns date as
declare
v_date date;
begin
select to_date('00000101','yyyymmdd') + v_num into v_date;
return v_date;
end;
