PostgreSQL数据库中的find_in_set函数自定义

创建find_in_set函数

CREATE OR REPLACE FUNCTION find_in_set(
	value anyelement,
	string_list text)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
  position INTEGER;
BEGIN
	IF string_list = '' THEN 
		RETURN 0;
	ELSE
  		position := array_position(string_to_array(string_list, ','), value::TEXT);
  		RETURN position;
	END IF;
END;
$BODY$;


CREATE OR REPLACE FUNCTION find_in_set(
	search_value text,
	string_list text)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
  position INTEGER;
BEGIN
	IF string_list = '' THEN 
		RETURN 0;
	ELSE
  		position := array_position(string_to_array(string_list, ','), search_value::TEXT);
  		RETURN position;
	END IF;
END;
$BODY$;

-- CREATE OR REPLACE FUNCTION find_in_set(
-- 	search_value integer,
-- 	string_list text)
--     RETURNS integer
--     LANGUAGE 'plpgsql'
--     COST 100
--     VOLATILE PARALLEL UNSAFE
-- AS $BODY$
-- DECLARE
--   position INTEGER;
-- BEGIN
-- 	IF string_list = '' THEN 
-- 		RETURN 0;
-- 	ELSE
--   		position := array_position(string_to_array(string_list, ','), search_value::TEXT);
--   		RETURN position;
-- 	END IF;
-- END;
-- $BODY$;

-- CREATE OR REPLACE FUNCTION find_in_set(
-- 	search_value bigint,
-- 	string_list text)
--     RETURNS integer
--     LANGUAGE 'plpgsql'
--     COST 100
--     VOLATILE PARALLEL UNSAFE
-- AS $BODY$
-- DECLARE
--   position INTEGER;
-- BEGIN
-- 	IF string_list = '' THEN 
-- 		RETURN 0;
-- 	ELSE
--   		position := array_position(string_to_array(string_list, ','), search_value::TEXT);
--   		RETURN position;
-- 	END IF;
-- END;
-- $BODY$;

-- CREATE OR REPLACE FUNCTION find_in_set(
-- 	search_value boolean,
-- 	string_list text)
--     RETURNS integer
--     LANGUAGE 'plpgsql'
--     COST 100
--     VOLATILE PARALLEL UNSAFE
-- AS $BODY$
-- DECLARE
--   position INTEGER;
-- BEGIN
-- 	IF string_list = '' THEN 
-- 		RETURN 0;
-- 	ELSE
--   		position := array_position(string_to_array(string_list, ','), search_value::TEXT);
--   		RETURN position;
-- 	END IF;
-- END;
-- $BODY$;

测试

select * from t_rule;

select find_in_set(1,'1,2,3,4,5');
select find_in_set(2::bigint,'1,2,3,4,5');
select find_in_set('3','1,2,3,4,5');

select find_in_set(true,'1,2,true,4,false');

select find_in_set(3.2,'1,2,true,4,3.2');