--检查零时表是否存在的函数 CREATE OR REPLACE FUNCTION ps_temp_table("varchar") RETURNS bool AS $BODY$ DECLARE
BEGIN
perform n.nspname ,c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname like ''''pg_temp_%'''' AND pg_catalog.pg_table_is_visible(c.oid) AND Upper(relname) = Upper($1);
IF FOUND THEN RETURN TRUE; ELSE RETURN FALSE; END IF;
END; $BODY$ LANGUAGE ''''plpgsql'''' VOLATILE;
--BOM 展开函数 CREATE OR REPLACE FUNCTION bomia_part(bpchar) RETURNS SETOF bomzz AS $BODY$ DECLARE ENDTREE INT8; NLVL INT8; REC INT8; RESULT RECORD; BEGIN
ENDTREE:=0; NLVL:=1;
--Clear bill of material report data DELETE FROM bomzz;
--Check temp table "STACK" exists IF ps_temp_table(''''STACK'''') THEN RAISE NOTICE ''''TEMP TABLE "STACK" EXISTS!''''; ELSE CREATE TEMP TABLE STACK ( PARENT CHAR(12), PARTNO CHAR(12), USAGE INT8, LVLS INT8 ) WITHOUT OIDS ON COMMIT DELETE ROWS; END IF;
INSERT INTO STACK SELECT IB010,IB020,IB030,1 FROM BOMIB WHERE IB010=$1;
WHILE (ENDTREE=0) LOOP NLVL := NLVL + 1;
INSERT INTO STACK(LVLS,PARENT,PARTNO,USAGE) SELECT NLVL, A.IB010, A.IB020, A.IB030 FROM BOMIB A, STACK B WHERE A.IB010 =B.PARTNO AND B.LVLS= NLVL -1;
IF NOT EXISTS(SELECT PARTNO FROM STACK WHERE LVLS =NLVL) THEN ENDTREE := 1; END IF;
END LOOP;
INSERT INTO BOMZZ(ZZ010,ZZ020,ZZ030,zz040) SELECT PARENT,PARTNO,USAGE,LVLS FROM STACK;
FOR RESULT IN SELECT * FROM BOMZZ LOOP RETURN NEXT RESULT; END LOOP;
RETURN; END$BODY$ LANGUAGE ''''plpgsql'''' VOLATILE;
--使用这个函数 SELECT * FROM bomia_part(''''xxx''''); --xxx = 父阶料号
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
