postgresql 常用小函数

将字符串按照分隔符划分成为数组：

create or replace function tools_str2Array(
in _originStr VARCHAR(800),
in _delimeter VARCHAR(10)
) RETURNS VARCHAR(800)[]
as $$
declare _strres varchar(800);
declare _cindex INTEGER;
declare _arrIndex INTEGER;
DECLARE _arr_str VARCHAR(800)[];
DECLARE _tmp_str varchar(800);
DECLARE _debugStr VARCHAR(400);
BEGIN
_arrIndex:=1;
_strres:='';
_cindex:=1;


if _delimeter is NULL or "character_length"(_debugStr)<1 THEN
return _arr_str;
end IF;


--_strres:=_strres||'原始字符串是：'||_originStr;


while _cindex<"length"(_originStr) loop
--_strres :=_strres||'【这个是什么？】'||split_part(_originStr, _delimeter, _arrIndex);
_tmp_str:=split_part(_originStr, _delimeter, _arrIndex);
if "character_length"(_tmp_str)<1 then
exit;
end if;
_arr_str:=_arr_str|| _tmp_str;
_arrIndex:=_arrIndex+1;
END loop;
return _arr_str;
end;
$$ LANGUAGE plpgsql volatile;

强制转换字符串到整数，转换不成功就返回null

CREATE OR REPLACE FUNCTION convert_to_integer(v_input text)
RETURNS INTEGER AS $$
DECLARE v_int_value INTEGER DEFAULT NULL;
BEGIN
    BEGIN
        v_int_value := v_input::INTEGER;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'Invalid integer value: "%".  Returning NULL.', v_input;
        RETURN NULL;
    END;
RETURN v_int_value;
END;
$$ LANGUAGE plpgsql VOLATILE;

将字符串转换成为整数数组，转换不成功的就用0代替。

【补充】postgresql的数组是从1开始的，别被坑了。

create or replace function tools_str2IntArray(_originstr varchar, _delimeter varchar)
returns INT[]
as
$$
DECLARE _arr_res int[];
DECLARE _arr_str VARCHAR(800)[];
DECLARE _arrLength int;
DECLARE _tmpStr VARCHAR(800);
DECLARE _tmpInt int;
DECLARE _nowIndex int;
DECLARE __index int;
BEGIN

_arr_str:=tools_str2array(_originstr, _delimeter);
_arrLength:=array_length(_arr_str, 1);
if _arrLength<1 THEN
return _arr_res;
end if;
--raise info '当前数组：%',_arr_str;
for _nowIndex in 1.._arrLength LOOP
_tmpStr:=_arr_str[_nowIndex];
_tmpInt:=convert_to_integer(_tmpStr::TEXT);
_arr_res:=_arr_res || COALESCE(_tmpInt,0);

END LOOP;
RETURN _arr_res;
END
$$ language plpgsql VOLATILE;

将数组转换成为字符串：

create or replace function tools_stringify(_array int[],_delimeter VARCHAR)
returns TEXT
as
$$

DECLARE _res TEXT;
DECLARE _arrIndex int;
DECLARE _totalLength int;
BEGIN
_arrIndex:=1;
_totalLength:=array_length(_array, 1);
_res:='';
IF _array is NULL THEN
return '';
end if;
IF array_length(_array, 1) <1 THEN
return '';
end if;
FOR _arrIndex in 1..array_length(_array,1) loop
--raise info '循环中：%',_array[_arrIndex];
_res:=_res || (_array[_arrIndex]);

IF _arrIndex<_totalLength THEN
_res:=_res || (_delimeter::TEXT);
--raise info '当前res：%',_res;
end if;
end loop;
return _res;
END
$$ LANGUAGE plpgsql VOLATILE;

create or replace function tools_stringify(_array VARCHAR[],_delimeter VARCHAR)
returns TEXT
as
$$

DECLARE _res TEXT;
DECLARE _arrIndex int;
DECLARE _totalLength int;
BEGIN
_arrIndex:=1;
_totalLength:=array_length(_array, 1);
_res:='';
IF _array is NULL THEN
return '';
end if;
IF array_length(_array, 1) <1 THEN
return '';
end if;
FOR _arrIndex in 1..array_length(_array,1) loop
--raise info '循环中：%',_array[_arrIndex];
_res:=_res || (_array[_arrIndex]);

IF _arrIndex<_totalLength THEN
_res:=_res || (_delimeter::TEXT);
--raise info '当前res：%',_res;
end if;
end loop;
return _res;
END
$$ LANGUAGE plpgsql VOLATILE;