CREATE OR REPLACE FUNCTION fun2(bvalue float,con char(1), store_no int4, timeflag varchar )
RETURNS SETOF RECORD AS
$BODY$
DECLARE
temp RECORD;
temp2 RECORD;
BEGIN
IF (timeflag IS NOT NULL) THEN
FOR temp IN SELECT COALESCE(field23039,0)*COALESCE(field23234,0) AS v ,field23042||'' AS f ,field23035 AS n FROM tbl_3504 WHERE field23041 <= timeflag AND field23039 > 0 AND field23818 = 'Y'
LOOP
FOR temp2 IN SELECT r.x,r.b,r.c FROM fun2(temp.v,temp.f,temp.n,null) r(x float,b text,c int4)
LOOP
return next temp2;
END LOOP;
RETURN next temp;
END LOOP;
ELSE
if (con = '1') THEN
FOR temp IN SELECT ((COALESCE(bvalue,0)+COALESCE(tbl_1525.field11329,0) ) * COALESCE(tbl_1526.field11210,0) + COALESCE(tbl_1526.field11211,0) ) AS v ,tbl_1526.field11212||'' AS f , field11207 AS n FROM tbl_1525,tbl_1526 where tbl_1525.field11201 = 'Y' AND tbl_1526.field11264='Y' AND tbl_1525.tbl_fldid=tbl_1526.tbl_fldid AND tbl_1525.field11202 = store_no
LOOP
FOR temp2 IN SELECT r.x,r.b,r.c FROM fun2(temp.v,temp.f,temp.n,null) r(x float,b text,c int4)
LOOP
RETURN NEXT temp2;
END LOOP;
RETURN next temp;
end LOOP;
ELSEIF (con = '2') THEN
FOR temp IN SELECT ((bvalue+COALESCE(tbl_1513.field11118,0))*( (COALESCE(tbl_1514.field11127,0) / COALESCE(tbl_1513.field11119,1) ) +(COALESCE(tbl_1514.field11128,0)/100) )) AS v,tbl_1513.field23097||'' AS f,field23101 AS n FROM tbl_1513,tbl_1514 WHERE tbl_1513.field11122='Y' AND tbl_1514.field11263 = 'Y' AND tbl_1513.tbl_fldid = tbl_1514.tbl_fldid AND tbl_1513.field11113 = store_no
LOOP
FOR temp2 IN SELECT r.x,r.b,r.c FROM fun2(temp.v,temp.f,temp.n,null) r(x float,b text,c int4)
LOOP
RETURN NEXT temp2;
END LOOP;
RETURN NEXT temp;
END LOOP;
END IF;
END IF;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION fun1(timeflag varchar) RETURNS SETOF RECORD AS
$$
DECLARE
temp2 RECORD;
BEGIN
FOR temp2 IN SELECT tablename FROM pg_tables WHERE tablename='x1'
LOOP
EXECUTE 'DROP table x1';
END LOOP;
FOR temp2 IN SELECT tablename FROM pg_tables WHERE tablename='x2'
LOOP
EXECUTE 'DROP table x2';
END LOOP;
FOR temp2 IN SELECT tablename FROM pg_tables WHERE tablename='x3'
LOOP
EXECUTE 'DROP table x3';
END LOOP;
FOR temp2 IN SELECT tablename FROM pg_tables WHERE tablename='x4'
LOOP
EXECUTE 'DROP table x4';
END LOOP;
/** condition x1 **/
EXECUTE 'CREATE TEMP TABLE x1 AS SELECT material_no AS no,unproduct_number AS count FROM tbl_headstore WHERE unproduct_number > 0 AND work_code<>''Q''' ;
/** condition x2 **/
EXECUTE 'CREATE TEMP TABLE x2 AS SELECT field20004 AS no, field20009 * COALESCE(field20007,0) AS count FROM tbl_3001 WHERE field20325 <= ' ||timeflag|| ' AND field20009 > 0 AND field20021 <> ''U'' AND field20021 <> ''K'' AND field20332 = ''Y''';
/** condition x3 **/
EXECUTE 'CREATE TEMP TABLE x3 AS SELECT field20031 AS no , field20036 * COALESCE(field20034,0) AS count FROM tbl_3002 WHERE field20331 <= '||timeflag||' AND field20036 > 0 AND field20030 <> ''M'' AND field20030 <> ''U'' AND field20352 = ''Y''';
/** condition x4 **/
EXECUTE 'CREATE TEMP table x4 as SELECT sum(store.count) AS count,store.no AS no from fun2(0,'''',0,'''||timeflag||''') store(count float,flag text,no int4) GROUP BY store.no';
FOR temp2 IN EXECUTE'SELECT CAST(x5.no AS varchar), tbl_headmaterial.material_name,tbl_headmaterial.material_unit,
CAST(round(COALESCE(CAST(x1.count AS numeric),0),3) AS varchar) ,
CAST(round(COALESCE(CAST(x2.count AS numeric),0),3) AS varchar) ,
CAST(round(COALESCE(CAST(x3.count AS numeric),0),3) AS varchar) ,
CAST(round(COALESCE(CAST(x4.count AS numeric),0),3) AS varchar) ,
CAST(round(COALESCE(CAST(x1.count AS numeric),0)+COALESCE(CAST(x2.count AS numeric),0)-COALESCE(CAST(x3.count AS numeric),0)-COALESCE(CAST(x4.count AS numeric),0),3) AS varchar) FROM
(SELECT DISTINCT no AS no FROM (SELECT CAST(no as int4) as no FROM x1 UNION SELECT CAST(no as int4) AS no FROM x2 UNION SELECT CAST(no as int4) as no FROM x3 UNION SELECT CAST(no as int4) as no FROM x4)AS x6) AS x5
LEFT JOIN x1 ON x5.no = x1.no LEFT JOIN x2 ON x5.no = x2.no LEFT JOIN x3 ON x5.no = x3.no LEFT JOIN x4 on x5.no = x4.no LEFT JOIN tbl_headmaterial on x5.no = tbl_headmaterial.material_no'
LOOP
RETURN NEXT temp2;
END LOOP;
EXECUTE 'DROP table x1';
EXECUTE 'DROP table x2';
EXECUTE 'DROP table x3';
EXECUTE 'DROP table x4';
RETURN;
END;
$$
language plpgsql;
select * from fun1('2007-12-30') store (no varchar,name varchar,unit varchar ,x1 varchar,x2 varchar,x3 varchar,x4 varchar,x5 varchar);
</script>