Postgrepsql Creating a function

--Here the Create OR REPLACE FUNCTION is fixed: "Public". " Function_info_a1 "This is the name of the function

CREATE OR REPLACE FUNCTION "public". " function_info_a1"(d1 varchar, d2 varchar, procuct varchar)

RETURNS setof "Pg_catalog". " Record "as $BODY $
Declare
REC record;
BEGIN
--Create a temporary table
CREATE TEMP Table flagtable (product_id int,rq timestamp,doctype varchar,docname varchar,qc float,rk float,ck float, JC float) on commit drop;

--on Commit drop indicates that the commit is deleted after the

--Inserting data
INSERT INTO flagtable (PRODUCT_ID,RQ,DOCTYPE,DOCNAME,QC,RK,CK,JC)

--Find data
Select T1.id,d1::timestamp, ' Beginning ', ' ', 0,0,0,0 from Product_product T1 left joins Product_template t0 on T1.produ Ct_tmpl_id=t0.id
where t0.active= ' t ' and t1.active= ' t ' and t0.categ_id=6;
--all finished items before the start date balance (beginning of period)

--Update data
update flagtable t3 set qc= (SELECT
COALESCE (SUM (case location_id =get_warehouse_id (' finished warehouse ') THEN-1 * COALESCE (product_qty,0) ELSE coalesce (p roduct_qty,0) END), 0) QC
From stock_move TS
WHERE state = ' done ' and date< d1::timestamp
and (location_id =get_warehouse_id (' finished warehouse ') OR location_dest_id =get_warehouse_id (' finished warehouse ')) and ts.product_id= T3.PRODUCT_ID);
-The initial period is the same as the balance
update flagtable set JC=QC;
--inserting data in the time period
INSERT INTO flagtable (PRODUCT_ID,RQ,DOCTYPE,DOCNAME,QC,RK,CK,JC)
 
Select Product_id,ts1.date,case when ts3.name= ' Internal transfers ' then ' transfer single ' when Ts3.name was null then ' counting order ' else Ts3.name End,
Case when ts2.name was null then Ts1.name else ts2.name end,0,coalesce (case when location_dest_id =get_wareho USE_ID (' finished warehouse ') then Product_qty ELSE 0 end,0),
COALESCE (case when location_id =get_warehouse_id (' finished warehouse ') then Product_qty ELSE 0 end,0), 0
From Stock_move Ts1 left joins Stock_picking Ts2 on ts1.picking_id=ts2.id left joins Stock_picking_type Ts3 on TS2.P Icking_type_id=ts3.id
where Ts1. State = ' Done ' and ts1. DATE >=d1::timestamp and Ts1. DATE <=d2::timestamp and (ts1.location_id =get_warehouse_id (' finished warehouse ') OR ts1.location_dest_id =get_warehouse_id (' finished warehouse ') ))
ORDER by ts1.product_id;
--Calculate the beginning of each record

update flagtable T4 set QC=COALESCE (case if qc=0 then (select SUM (jc+rk-ck) from Flagtable T5 where t5.product_id= T4.PRODUCT_ID and T5.RQ<T4.RQ) Else QC end,0);
-Calculate each balance
Update flagtable set jc= (QC+RK-CK)
where jc=0;
for rec in select Rq,name_template,th,spec,doctype,docname,qc, RK,CK,JC from (select Product_id,a0.rq rq,a1.name_t Emplate, (select Khwl_code from Product_custo_info where a2.id=product_tmpl_id order by ID limit 1) th,a2.spec,a0.doctype, A0.docname, A0.QC, A0.RK,A0.CK,A0.JC
From Flagtable A0 left joins Product_product A1 on A0.product_id=a1.id left joins Product_template A2 on A1.product_ Tmpl_id=a2.id
where (a0.qc<>0 or a0.rk<>0 or a0.ck<>0 or a0.jc<>0)) T where (t.th like '% ' | | procuct| | ' % ' or name_template like '% ' | | procuct| | ' % ' or spec like '% ' | | procuct| | ' % ')
ORDER by Product_id,rq
Loop
RETURN next rec;
end Loop;
return;
END;
$BODY $
LANGUAGE ' plpgsql ' VOLATILE cost
ROWS
;

Postgrepsql Creating a function