pgsql通过递归获取某组织的全称的函数

– 组织表
CREATE TABLE mdc.base_org
(
uuid character varying(32) NOT NULL, – 组织编码
name character varying(64) NOT NULL, – 组织名称
org_parent_uuid character varying(32) NOT NULL, – 上级组织编码
memo character varying(256), – 描述
delete_flag smallint NOT NULL, – 删除标志位:1-启用,0-删除
court_uuid character varying(32), – 小区uuid,用于云端同步
create_time timestamp(0) without time zone NOT NULL, – 创建日期
update_time timestamp(0) without time zone NOT NULL, – 修改日期
create_user character varying(32) NOT NULL, – 创建用户id
update_user character varying(32) NOT NULL, – 修改用户id
is_parent boolean NOT NULL, – 判断是否为父节点,若该节点下有组织或房屋则为父节点,true-父节点,false-非父节点
sort_by character varying(255) NOT NULL, – 排序字段,每3位代表一个级别,同级别该字段自动递增,根节点为001
org_type character varying(2) NOT NULL, – 组织类型:1-小区,2-期,3-楼栋,4-单元,5-楼层,6-门类型,7-其他
CONSTRAINT pk_base_org PRIMARY KEY (uuid)
)
WITH (
OIDS=FALSE
);

COMMENT ON TABLE mdc.base_org
IS ‘组织表’;
COMMENT ON COLUMN mdc.base_org.uuid IS ‘组织编码’;
COMMENT ON COLUMN mdc.base_org.name IS ‘组织名称’;
COMMENT ON COLUMN mdc.base_org.org_parent_uuid IS ‘上级组织编码’;
COMMENT ON COLUMN mdc.base_org.memo IS ‘描述’;
COMMENT ON COLUMN mdc.base_org.delete_flag IS ‘删除标志位:1-启用,0-删除’;
COMMENT ON COLUMN mdc.base_org.court_uuid IS ‘小区uuid,用于云端同步’;
COMMENT ON COLUMN mdc.base_org.create_time IS ‘创建日期’;
COMMENT ON COLUMN mdc.base_org.update_time IS ‘修改日期’;
COMMENT ON COLUMN mdc.base_org.create_user IS ‘创建用户id’;
COMMENT ON COLUMN mdc.base_org.update_user IS ‘修改用户id’;
COMMENT ON COLUMN mdc.base_org.is_parent IS ‘判断是否为父节点,若该节点下有组织或房屋则为父节点,true-父节点,false-非父节点’;
COMMENT ON COLUMN mdc.base_org.sort_by IS ‘排序字段,每3位代表一个级别,同级别该字段自动递增,根节点为001’;
COMMENT ON COLUMN mdc.base_org.org_type IS ‘组织类型:1-小区,2-期,3-楼栋,4-单元,5-楼层,6-门类型,7-其他’;

– 获取某组织全称的函数
CREATE OR REPLACE FUNCTION mdc.get_org_fullname(
org_uuid character varying)
RETURNS character varying
LANGUAGE ‘plpgsql’
AS $BODY$
DECLARE v_rec_record RECORD;
DECLARE o_area varchar :=’’;
BEGIN

FOR v_rec_record IN (
		WITH RECURSIVE r AS (
			SELECT uuid, name, org_parent_uuid, 1 as depth
			FROM mdc.base_org 
			WHERE uuid = $1
			UNION ALL
			SELECT t.uuid, t.name, t.org_parent_uuid, r.depth+1 as depth
			FROM mdc.base_org t ,r WHERE t.uuid = r.org_parent_uuid
			)
			SELECT uuid, name, org_parent_uuid,depth FROM r order by depth desc) LOOP
	o_area :=  o_area || v_rec_record.name ;
END LOOP;
return o_area;

END；
$BODY$;