# 建筑下的业务空间 ## 前置条件 1. 业务空间必须要有所属的楼层 ``` FROM zone_* WHERE project_id='Pj4201050001' AND floor_id is not null ``` ## 处理逻辑 1. 从业务空间绑定的楼层信息里取到楼层所属的建筑ID, 2. 取到的建筑ID即是业务空间的所属建筑 (建筑ID为空,则的建筑ID也为空). ## 实现方式 SQL ``` update zone_space_base zone set zone.building_id = floor.building_id from floor where zone.floor_id = floor.id and zone.project_id = 'Pj4201050001' and zone.floor_id is not null ``` # 函数
源码 ``` CREATE OR REPLACE FUNCTION "public"."rel_bd2sp"("tables" text, "project_id" varchar) RETURNS "pg_catalog"."bool" AS $BODY$ try: list = tables.split(',') # 将下面对数据库的操作作为一个事务, 出异常则自动rollback with plpy.subtransaction(): for table in list: str = "UPDATE {0} as zone SET building_id = floor.building_id from public.floor as floor where floor_id = floor.id and zone.project_id = $1 and floor_id is not null".format(table.strip()) plan = plpy.prepare(str, ["text"]) data = plan.execute([project_id]) except Exception as e: plpy.warning(e) return False else: return True $BODY$ LANGUAGE plpython3u VOLATILE COST 100 ```
## 输入 1. 参与计算的表的全名称, 带schema名, 以英文逗号隔开 2. 项目id ## 返回结果 true 成功 false 失败