如何在for循环中运行函数？ [英] How to run function in for loop?

问题描述

朋友目前我正在从我的旧数据库进行数据迁移到新DB.in我的新Db我为表格列写了用户定义函数。所以问题发生在这里

谢谢.. ..

friends at present i am doing data migration from my old database to new DB.in my new Db I wrote User defined function for a table column.So the problem occur here
thank you....

CREATE OR REPLACE FUNCTION empdatamigration1()
RETURNS void AS
$BODY$
DECLARE
EMPYID CHARACTER VARYING(20);
EMPD CHARACTER VARYING(20);

begin
FOR EMPYID IN select udf_generateemployeeid(2, ('now'::text)::date) LOOP

select coalesce(max(cast(cast(empid as integer)+1 as text)),null,EMPYID) into EMPD from tblemployeemst where createddate = 'now'::date;

INSERT INTO tblemployeemst(givenname, surname, basicsal, hiredate, rgid, qualification,
otherqualif, experience, active, age, gender, dob, resphone, offphone, mobileno,emailid, mstatus, designationid, brid,empid)
SELECT distinct e.name, e.surname, e.basicsal,e.hiredate,2, e.qualification, e.otherqualif, e.experience, cast(e.actflag as boolean),ei.age,ei.gender,ei.dob, ei.resphone,
ei.offphone, ei.mobileno,ei.emailid, ei.mstatus,d.designationid,2,EMPD
FROM tabemployeemst e left outer Join tabemployeedtl ei on ei.eid=e.eid left outer join tbldesignationmst d on upper(d.designationname)=upper(e.designame);

END LOOP;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION empdatamigration1() OWNER TO postgres;

推荐答案

BODY

DECLARE
EMPYID CHARACTER VARYING （ 20 ）;
EMPD CHARACTER VARYING （ 20 ）;

开始
FOR EMPYID IN 选择 udf_generateemployeeid（ 2 ，（' now' :: text）:: date）LOOP

选择 coalesce （max（cast（强制转换（empid 作为整数）+1 as text ））， null ，EMPYID） into EMPD 来自 tblemployeemst 其中 createddate = ' now' :: date;

INSERT INTO tblemployeemst（givenname，surname，basicsal，hiredate，rgid，资格，
otherqualif，经验，活跃，年龄，性别，dob，resphone，offphone，mobileno，emailid，mstatus，specifiedid，brid，empid）
SELECT distinct e.name，e.surname，e.basicsal，e.hiredate， 2 ， e.qualification，e.otherqualif，e.experience，cast（e.actflag as boolean），ei.age，ei.gender，ei.dob，ei.resphone ，
ei.offphone，ei.mobileno，ei.emailid，ei.mstatus，d.designationid， 2 ，EMPD
FROM tabemployeemst e left outer tabemployeedtl ei ei.eid = e.eid left 外部 加入 tbldesignationmst d 上部（d .designationname）=上（e.designame）;

END LOOP;
end ;

DECLARE EMPYID CHARACTER VARYING(20); EMPD CHARACTER VARYING(20); begin FOR EMPYID IN select udf_generateemployeeid(2, ('now'::text)::date) LOOP select coalesce(max(cast(cast(empid as integer)+1 as text)),null,EMPYID) into EMPD from tblemployeemst where createddate = 'now'::date; INSERT INTO tblemployeemst(givenname, surname, basicsal, hiredate, rgid, qualification, otherqualif, experience, active, age, gender, dob, resphone, offphone, mobileno,emailid, mstatus, designationid, brid,empid) SELECT distinct e.name, e.surname, e.basicsal,e.hiredate,2, e.qualification, e.otherqualif, e.experience, cast(e.actflag as boolean),ei.age,ei.gender,ei.dob, ei.resphone, ei.offphone, ei.mobileno,ei.emailid, ei.mstatus,d.designationid,2,EMPD FROM tabemployeemst e left outer Join tabemployeedtl ei on ei.eid=e.eid left outer join tbldesignationmst d on upper(d.designationname)=upper(e.designame); END LOOP; end;

BODY

这篇关于如何在for循环中运行函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！