CREATE OR REPLACE FUNCTION f_insert_app_op_assign(oldid int8, newid int8) RETURNS "pg_catalog"."void" AS
$BODY$
DECLARE c_record record; num int8;
BEGIN
for c_record in (SELECT id, app_ver_id, op_id, role_id, is_inner FROM app_op_assign where op_id in(SELECT id from app_op_security where id = oldid) and role_id not in(3,4)) loop
DELETE from app_op_assign where role_id = c_record.role_id and op_id = newid;
INSERT INTO app_op_assign(id,app_ver_id,op_id,role_id, is_inner)
VALUES (c_record.id + 10001,c_record.app_ver_id,newid,c_record.role_id,c_record.is_inner);
END loop;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100;
select f_insert_app_op_assign(7872525861819131578,6098882599055920104);
另一个案例:
DECLARE c_record record; c_temp record; exesql VARCHAR; c_code VARCHAR;
BEGIN
UPDATE sys_department SET full_ids = '',full_codes = '',full_names = '';
exesql := 'select id,pid,n from sys_department_r where n!=0 order by n desc';
FOR c_record IN EXECUTE exesql loop SELECT
ID :: VARCHAR,
code,
NAME INTO c_temp
FROM
sys_department
WHERE
ID = c_record.pid;
IF c_temp.code = ''
OR c_temp.code IS NULL THEN
UPDATE sys_department
SET full_ids = concat (full_ids, '/', c_temp. ID),
full_names = concat (full_names, '/', c_temp. NAME)
WHERE
ID = c_record. ID;
ELSE
UPDATE sys_department
SET full_ids = concat (full_ids, '/', c_temp. ID),
full_codes = concat (full_codes, '/', c_temp.code),
full_names = concat (full_names, '/', c_temp. NAME)
WHERE
ID = c_record. ID;
END
IF;
END loop;
END;