1.需要用到切割字符串函数:regexp_split_to_array
函数:regexp_split_to_array(string text, pattern text [, flags text ])
说明:Split string using a POSIX regular expression as the delimiter. See Section 9.7.3 for more information. 利用正则表达式将字符串分割成数组
例子:SELECT regexp_split_to_array('4,5,6,8,9', E'\\,') = {4,5,6,8,9}
2.需要用到取数组长度array_length
函数:array_length(数组名,1);
例子:SELECT array_length(array[1,2,3],1)=3;
存储过程:
CREATE OR REPLACE FUNCTION 存储过程名字(传入的参数(id) text) //参数例如:'4,5,6,8,9,10'
RETURNS void AS //无返回类型
$BODY$
DECLARE
x int; //定义一个参数来标示数组下标
count int; //记录切割完字符串存入数组中,数组中字符的个数
array1 text[]; //切割字符串存入数组
BEGIN
SELECT regexp_split_to_array(id,E'\\,') INTO array1;
x := 1;
SELECT array_length(array1,1) INTO count;
WHILE x < count LOOP //执行循环删除
DELETE
FROM 表名 WHERE 表的字段=array1[x]::int;
x := x+1;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE