|
-------------------------------------------------------------table
CREATE TABLE ferp.mol_bom
(
mol_pcd character varying(20) NOT NULL, -- 모금형코드
mol_ccd character varying(20) NOT NULL, -- 자금형코드
reqvl integer, --정수
CONSTRAINT mol_bom_pkey PRIMARY KEY (mol_pcd, mol_ccd, sdate)
)
------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION ferp.fun_mol_partlist(as_mol_no character varying)
RETURNS TABLE( mol_pcd character varying, mol_ccd character varying, reqvl numeric, qty numeric) AS
$BODY$
begin
WITH RECURSIVE MOL_PARTLIST AS (
SELECT a.mol_pcd
, a.mol_ccd
, a.reqvl
, 1 * a.reqvl AS qty
FROM mol_bom a
WHERE a.mol_pcd = as_mol_no
UNION ALL
SELECT b.mol_pcd
, b.mol_ccd
, b.reqvl
, (c.qty * b.reqvl) AS qty
FROM mol_bom b
, MOL_PARTLIST c
where b.mol_pcd = c.mol_ccd
)
SELECT d.mol_pcd
, d.mol_ccd
, d.reqvl
, d.qty
FROM MOL_PARTLIST d;
end ;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION ferp.fun_mol_partlist(character varying)
---------------------------------------------------------------------------- 실행
SELECT mol_pcd FROM fun_mol_partlist('MO-0001')
ERROR: 쿼리에 결과 데이터의 대상이 없음
HINT: SELECT의 결과를 취소하려면 대신 PERFORM을 사용하십시오.
CONTEXT: PL/pgSQL 함수 "fun_mol_partlist(character varying)" 의 4번째 SQL 문
********** Error **********
ERROR: 쿼리에 결과 데이터의 대상이 없음
SQL state: 42601
Hint: SELECT의 결과를 취소하려면 대신 PERFORM을 사용하십시오.
Context: PL/pgSQL 함수 "fun_mol_partlist(character varying)" 의 4번째 SQL 문
|
