postgres创建递归视图

• 递归功能：把所有继承关系的id都放到一个新的数级字段中（当前的元素id+父id+父的父id+…)

CREATE OR REPLACE RECURSIVE VIEW VW_BUSINESS_CAPABILITY_MODEL_HIERARCHY (relation_id, sub_id, bcm_id, bcm_name, bcm_type, path) AS
	SELECT sbcm.id,
		sbcm2.id AS sub_id,
		sbcm2.bcm_id,
		sbcm2.bcm_name,
		sbcm2.bcm_type,
		ARRAY [cast(sbcm2.id as character varying)]
		FROM solaris_business_capability_model sbcm
		LEFT JOIN solaris_business_capability_model sbcm2 ON sbcm.id = sbcm2.parent_id
		WHERE sbcm.parent_id is null AND sbcm.bcm_industry_id = 'I_CROSS'
	UNION
		SELECT vw.sub_id as id,
		sbcm3.id as sub_id,
		sbcm3.bcm_id,
		sbcm3.bcm_name,
		sbcm3.bcm_type,
		vw.path || cast(sbcm3.id as character varying)
		FROM vw_business_capability_model_hierarchy vw
		JOIN solaris_business_capability_model sub ON sbcm3.parent_id = vw.sub_id

• 把字符串 字段 放在一个数组类型的新字段 path中
  • ARRAY [cast(sbcm2.id as character varying)]
• 新数组字段添加 字符串值 元素进来
  • vw.path || cast(sbcm3.id as character varying)