PostgreSQL Recursive 運用遞迴，取得多階層BOM原料ID

-- FUNCTION: adempiere.get_subbom_id(numeric)

-- DROP FUNCTION adempiere.get_subbom_id(numeric);

CREATE OR REPLACE FUNCTION adempiere.get_subbom_id(
	id numeric)
    RETURNS TABLE(m_product_id numeric) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN
    RETURN QUERY 
/*  
    Author: Ray 
    Email: ray.asia@gmail.com; ray@ninniku.tw
    Date: 2021-04-25
    Description: Get all m_productbom_id  self and children
*/
    with recursive  recur as (

	 select c.m_productbom_id from M_Product_BOM c where c.m_product_id = id
	 union
	 (
	  select ch.m_productbom_id from M_Product_BOM ch
          inner join recur on ch.m_product_id = recur.m_productbom_id
	 ) 
    )
    select recur.m_productbom_id from recur;

END;
$BODY$;

ALTER FUNCTION adempiere.get_subbom_id(numeric)
    OWNER TO adempiere;