PGSQL function,procedure

1、procedure

1.1 create:

CREATE OR REPLACE PROCEDURE myProc001(site character varying, fweek integer)

LANGUAGE plpgsql

AS $procedure$

BEGIN

           sqls;--only execute , no return

    END;

$procedure$;

1.2 call: call myProc001('ooodt1 08:00:00','ooodt2 08:00:00');

2、function

2.1 create:

CREATE OR REPLACE FUNCTION  myFunc001(bills character varying, weekstart integer, fuserid bigint)

RETURNS TABLE(column001 DataType,column002 DataType)

LANGUAGE plpgsql

AS $function$

declare myVar001 varchar(2000);

begin
        ...
        excsql=concat('insert into tb_rp_cacu_id select id,',cast(fuserid as varchar)) ;
        excsql=concat(excsql,'from tb_hr_qtyrequest where id in(');
        excsql=concat(excsql,bills);
        excsql=concat(excsql,')');
        execute(excsql);

        ........

RETURN QUERY select * from tb_tmp_rp01;--this is how to return,with the start
        drop table tb_tmp_rp01;

END;

$function$;

2.2 call : select * from myFunc001('OOOids',OOOweek,OOOuserId)

 

3.how to loop

do $$
declare v_idx integer := 1;
declare v_wks integer := 1;
declare v_wk integer := 1;
declare dtstart date;
begin
    dtstart  = (select start_date from tb_schedule where cast("month" as int)=1  and customer ='HW');
    while v_idx < 13 loop
      v_wks =(select weeks from tb_schedule where cast("month" as int)=v_idx  and customer ='HW');
      while v_wks>0 loop 
          insert into tb_schedule_weeks select cast("month" as int2) ,v_wk,weeks,cast(head_id  as int2),1,
              dtstart+ ((v_wk-1)*7 || ' day')::interval,dtstart+ ((v_wk)*7-1 || ' day')::interval
              from tb_schedule where cast("month" as int)=v_idx and customer ='HW';
          v_wk = v_wk +1;
          v_wks = v_wks-1;
      end loop;
    v_idx = v_idx+1;
  end loop;
end $$;