postgresql - PL/pgSQL 函数中的语法错误

Question

这是我的 pl/sql PL/pgSQL 函数。我在这个函数中遇到语法错误。你能帮我解决这个问题吗？

ERROR:  syntax error at or near "$2"
LINE 1: SELECT   $1  FOR  $2  IN(select abl.ka003_position_lk_id as ...
                          ^
QUERY:  SELECT   $1  FOR  $2  IN(select abl.ka003_position_lk_id as posit, sum(abl.nooflabors*abl.hours) as totalhours from ahcc_boq_labor abl where ahcc_boq_item_id= $3  group by abl.ka003_position_lk_id) LOOP  $4  =  $5 
CONTEXT:  SQL statement in PL/PgSQL function "ahcc_proj_budget_cpy_plan1" near line 83

********** Error **********

ERROR: syntax error at or near "$2"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "ahcc_proj_budget_cpy_plan1" near line 83

这是我的代码:

CREATE OR REPLACE FUNCTION ahcc_proj_budget_cpy_plan1(p_pinstance_id character varying)
  RETURNS void AS
$BODY$ DECLARE 
v_Process CHAR(1);
v_project_id VARCHAR(32);
v_projbudget VARCHAR(32);
v_client VARCHAR(32);
v_petty NUMERIC;
v_org VARCHAR(32);
v_mrl NUMERIC;
v_hr NUMERIC;
v_eqp NUMERIC;
v_createdby VARCHAR(32);
v_updatedby VARCHAR(32);
v_record_id VARCHAR(32); 
v_ResultStr VARCHAR(120);
v_status VARCHAR(32);
v_message VARCHAR(255);
v_userid VARCHAR(32);
v_wbs_id VARCHAR(32);
v_boq_id VARCHAR(32);
v_boqitem_id VARCHAR(32);
v_positionId VARCHAR(255);
v_totalhours VARCHAR(255);
v_mproduct_id VARCHAR(255);
v_quantity VARCHAR(255);
v_hours VARCHAR(255);
v_material VARCHAR(255);
v_mquantity VARCHAR(255);
Cur_boq RECORD;
Cur_wbs RECORD;
Cur_hr RECORD;
Cur_eqp RECORD;
Cur_mrl RECORD;
  BEGIN

   --  Update AD_PInstance by setting IsProcessing='Y'
    RAISE NOTICE '%','Updating PInstance - Processing ' || p_PInstance_ID ;
    PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'Y', NULL, NULL) ;
    BEGIN 
    select record_id,ad_user_id into v_record_id,v_userid from ad_pinstance where ad_pinstance_id=p_PInstance_ID;
    select ahcc_project_id,ahcc_proj_budget_id, AD_CLIENT_ID, AD_ORG_ID,
             CREATEDBY, UPDATEDBY,process 
             into v_project_id,v_projbudget,v_client,v_org,v_createdby,v_updatedby,v_process
              from ahcc_proj_budget where ahcc_proj_budget_id=v_Record_ID;
                        update ahcc_proj_budget set process='N' where Process='Y';

            select count(*) into v_mrl from ahcc_proj_budget_mrl where ahcc_proj_budget_id=v_record_id;
            select count(*) into v_hr from ahcc_proj_budget_hr where ahcc_proj_budget_id=v_record_id;
            select count(*) into v_eqp from ahcc_proj_budget_eqp where ahcc_proj_budget_id=v_record_id;

              IF (v_mrl<>0 OR v_hr<>0 OR v_eqp<>0) THEN
                  delete from  ahcc_proj_budget_mrl where ahcc_proj_budget_id=v_record_id;
                  delete from ahcc_proj_budget_hr where ahcc_proj_budget_id=v_record_id;
                  delete from ahcc_proj_budget_eqp where ahcc_proj_budget_id=v_record_id;
              END IF;

            select count(*) into v_mrl from ahcc_proj_budget_mrl where ahcc_proj_budget_id=v_record_id;
            select count(*) into v_hr from ahcc_proj_budget_hr where ahcc_proj_budget_id=v_record_id;
            select count(*) into v_eqp from ahcc_proj_budget_eqp where ahcc_proj_budget_id=v_record_id;


               IF (v_mrl=0 AND v_hr=0 AND v_eqp=0) THEN
        FOR Cur_wbs IN (select apwbs.ahcc_project_wbs_id  , apbi.ahcc_boq_item_id
                from ahcc_project_boqitem apbi
                left join (select ahcc_project_wbs_id from ahcc_project_wbs
                where ahcc_project_wbs_id not in(select wbs_parent_id from ahcc_project_wbs) 
                and ahcc_project_id=v_project_id) apwbs 
                on apbi.ahcc_project_wbs_id=apwbs.ahcc_project_wbs_id)
        LOOP
                v_wbs_id = Cur_wbs.apwbs.ahcc_project_wbs_id;
                v_boq_id = Cur_wbs.apbi.ahcc_boq_item_id;

            FOR Cur_boq IN(SELECT ahcc_boq_item_id  FROM connectby('ahcc_boq_item','ahcc_boq_item_id',
                'boqitem_parentid','boqitem_name',v_boq_id,0 ,'/') AS 
                t(ahcc_boq_item_id text, boqc_parentid text, level int, branch text ,pos int))
            LOOP    
                v_boqitem_id = Cur_boq.ahcc_boq_item_id

                FOR Cur_hr IN(select abl.ka003_position_lk_id as posit, sum(abl.nooflabors*abl.hours) as totalhours 
                    from ahcc_boq_labor abl
                    where ahcc_boq_item_id=v_boqitem_id
                    group by abl.ka003_position_lk_id)
                LOOP
                    v_positionId = Cur_hr.posit;
                    v_totalhours = Cur_hr.totalhours;

                    INSERT INTO AHCC_Proj_budget_hr
                     (
                        AHCC_Proj_budget_hr_ID,ahcc_proj_budget_id, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE,
                        CREATED, CREATEDBY, UPDATED, UPDATEDBY,ka003_position_lk_id,hourprice,totalprice,total_hour)
                     VALUES
                     (
                        GET_UUID(),v_projbudget,v_client,v_org,'Y',TO_DATE(NOW()),v_createdby,TO_DATE(NOW()),v_updatedby,
                        v_positionId,0,0,v_totalhours
                     );
                END LOOP;
                FOR Cur_eqp IN(select abe.m_product_id as product,sum(abe.equipment_quantity) as qty,sum(abe.noofhours) as hour
                        from ahcc_boq_equipment abe where ahcc_boq_item_id=v_boqitem_id
                        group by abe.m_product_id)

                LOOP
                    v_mproduct_id = Cur_eqp.m_product_id;
                    v_quantity = Cur_eqp.qty;
                    v_hours = Cur_eqp.hour;


                    INSERT INTO AHCC_Proj_budget_eqp
                    (
                       AHCC_Proj_budget_eqp_ID,ahcc_proj_budget_id, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE,
                       CREATED, CREATEDBY, UPDATED, UPDATEDBY,m_product_id,no_of_equipment,hour,hourprice,totalprice)
                    VALUES
                    (
                       GET_UUID(),v_projbudget,v_client,v_org,'Y',TO_DATE(NOW()),v_createdby,TO_DATE(NOW()),v_updatedby,
                       v_mproduct_id,v_quantity,v_hours,0,0
                    );
                END LOOP;

                FOR Cur_mrl IN(select abm.m_product_id, sum(abm.material_quantity) as qty from ahcc_boq_material abm
                        where ahcc_boq_item_id=v_boqitem_id
                        group by abm.m_product_id)
                LOOP
                    v_material = Cur_mrl.m_product_id;
                    v_mquantity = Cur_mrl.qty;

                    INSERT INTO AHCC_Proj_budget_mrl
                    (
                      AHCC_Proj_budget_mrl_ID,ahcc_proj_budget_id, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE,
                      CREATED, CREATEDBY, UPDATED, UPDATEDBY,m_product_id,quantity,productprice,totalprice)
                    VALUES              
                    (
                      GET_UUID(),v_projbudget,v_client,v_org,'Y',TO_DATE(NOW()),v_createdby,TO_DATE(NOW()),v_updatedby,
                      v_material,v_mquantity,0,0
                    );

                END LOOP;
            END LOOP;
        END LOOP;   

          END IF;

    RAISE NOTICE '%','Updating PInstance - Finished ' || v_Message ;
 PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 1, v_Message) ;    
    RETURN;
 END; -- BODY

 EXCEPTION
 WHEN OTHERS THEN
   v_ResultStr:= '@ERROR=' || SQLERRM;
   RAISE NOTICE '%',v_ResultStr ;
   PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
   RETURN;
END ; $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION ahcc_proj_budget_cpy_plan1(character varying) OWNER TO tad;

Answer 1

试试这个:

在第 83 行之前添加一个分号

像这样:

v_boqitem_id = Cur_boq.ahcc_boq_item_id;