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 $$;