# 竖井关联的建筑 # 计算流程 根据竖井关联的空间关系来推导 ## 处理逻辑 1. 根据 竖井 --> 业务空间 --> 建筑 的间接关系, 来获取资产所在业务空间的关系 # 函数
源码 ``` CREATE OR REPLACE FUNCTION "public"."rel_sh2bd"("project_id" varchar) RETURNS "pg_catalog"."bool" AS $BODY$ try: # 将下面对数据库的操作作为一个事务, 出异常则自动rollback with plpy.subtransaction(): delete_plan = plpy.prepare("delete from r_sh_in_bd rel where rel.project_id = $1 and sign = 2", ["text"]) delete_plan.execute([project_id]) join_plan = plpy.prepare("select distinct shsp.sh_id shaft_id, spbd.building_id building_id from relationship.r_sh2sp shsp inner join r_sp_in_bd spbd on shsp.sp_id = spbd.space_id where shsp.project_id = $1 and spbd.project_id = $1", ["text"]) rel = join_plan.execute([project_id]) for row in rel: shaft_id = row['shaft_id'] building_id = row['building_id'] try: plan = plpy.prepare("insert into r_sh_in_bd(shaft_id, building_id, project_id, sign) values($1, $2, $3, 2)", ["text", "text", "text"]) plan.execute([shaft_id, building_id, project_id]) except Exception as ex: pass except Exception as e: plpy.warning(e) return False else: return True $BODY$ LANGUAGE plpython3u VOLATILE COST 100 select public.rel_sh2bd('Pj1102290002') ```
## 入参 1. 项目id ## 例子 select public.rel_sh2bd('Pj1102290002');