CREATE OR REPLACE FUNCTION "public"."f_customer_rel_manager"()
RETURNS "pg_catalog"."void" AS $BODY$
DECLARE uid int8; c_record record; resu VARCHAR; res int;u_manager_id VARCHAR;
BEGIN
DROP SEQUENCE IF EXISTS f_customer_manager_seq;
CREATE SEQUENCE f_customer_manager_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1
CYCLE;
DELETE FROM bas_cm_customer_rel_manager;
-- 查询要操作的数据
FOR c_record in (SELECT id, pid from bas_cm_customer_rel_r) loop
SELECT manager_id ::varchar FROM bas_cm_customer where id = c_record.pid into resu;
IF resu != '' THEN
IF (position(',' in resu ) > 0) THEN
FOR u_manager_id in(SELECT UNNEST(string_to_array(resu,','))) loop
SELECT count(*) FROM bas_cm_customer_rel_manager where customer_id = c_record.id and manager_id = u_manager_id::int8 into res;
IF(res = 0) THEN
select nextval('f_customer_manager_seq') into uid;
insert into bas_cm_customer_rel_manager
(id,status,creator_id,customer_id,create_time,manager_id)
values
(uid,'1',999,c_record.id,now(),u_manager_id::int8);
END IF;
END loop;
ELSE
SELECT count(*) FROM bas_cm_customer_rel_manager where customer_id = c_record.id AND manager_id = resu::int8 into res;
IF(res = 0) THEN
select nextval('f_customer_manager_seq') into uid;
insert into bas_cm_customer_rel_manager
(id,status,creator_id,customer_id,create_time,manager_id)
values
(uid,'1',999,c_record.id,now(), resu::int8);
END IF;
END IF;
ELSE
select nextval('f_customer_manager_seq') into uid;
insert into bas_cm_customer_rel_manager
(id,status,creator_id,customer_id,create_time,manager_id)
values
(uid,'1',999,c_record.id,now(), null);
END IF;
END loop;
DROP SEQUENCE if EXISTS f_customer_manager_seq;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100;