人大金仓(KingBase)数据库自定义函数语句

1.直接上干货：

Ifnull 函数

CREATE OR REPLACE INTERNAL FUNCTION PUBLIC.IFNULL(CHARACTER VARYING, CHARACTER VARYING) 
 RETURNS CHARACTER VARYING
 LANGUAGE PLSQL
 IMMUTABLE STRICT
AS $function$
DECLARE
BEGIN
RETURN ISNULL($1,$2);
END;
$function$;

FIND_IN_SET 函数

CREATE OR REPLACE INTERNAL FUNCTION PUBLIC.FIND_IN_SET(CHARACTER VARYING, CHARACTER VARYING) 
 RETURNS CHARACTER VARYING
 LANGUAGE PLSQL
 IMMUTABLE STRICT
AS $function$
DECLARE
tokens varchar[];
BEGIN
tokens := sys_catalog.string_to_array($2, ',');
RETURN ISNULL(sys_catalog.array_position(tokens, $1),0) > 0;
END;
$function$;

JSON_EXTRACT 函数

CREATE OR REPLACE INTERNAL FUNCTION PUBLIC.JSON_EXTRACT(CHARACTER VARYING, CHARACTER VARYING) 
 RETURNS CHARACTER VARYING
 LANGUAGE PLSQL
 IMMUTABLE STRICT
AS $function$
DECLARE
BEGIN
RETURN sys_catalog.json_extract_path($1, sys_catalog.replace($2,'$.',''));
END;
$function$;

substring_index 函数

CREATE OR REPLACE INTERNAL FUNCTION PUBLIC.substring_index(CHARACTER VARYING, CHARACTER VARYING, INTEGER) 
 RETURNS CHARACTER VARYING
 LANGUAGE PLSQL
 IMMUTABLE STRICT
AS $function$
DECLARE
tokens varchar[];
length integer ;
indexnum integer;
BEGIN
tokens := sys_catalog.string_to_array($1, $2);
length := sys_catalog.array_upper(tokens, 1);
indexnum := length - ($3 * -1) + 1;
IF $3 >= 0 THEN
RETURN sys_catalog.array_to_string(tokens[1:$3], $2);
ELSE
RETURN sys_catalog.array_to_string(tokens[indexnum:length], $2);
END IF;
END;
$function$;