# 设备所在建筑 ## 前置条件 1. 设备已经绑定楼层 ``` FROM equipment WHERE project_id='Pj4201050001' AND floor_id is not null ``` ## 依赖函数 Eq2Fl ## 处理逻辑 1. 从设备绑定的楼层里取楼层所属的建筑ID, 2. 将取到的建筑ID填入设备的所属建筑中(建筑ID为空,则设备的所属建筑ID也为空)。 ## 实现方式 SQL ``` UPDATE equipment SET building_id = (SELECT building_id FROM "floor" WHERE id = equipment.floor_id) WHERE project_id='Pj4201050001' AND floor_id is not null ``` # 函数
源码 ```plpython create or replace function public.rel_eq2bd(project_id character varying) returns boolean as $$ try: # 将下面对数据库的操作作为一个事务, 出异常则自动rollback with plpy.subtransaction(): str = "UPDATE public.equipment SET building_id = (SELECT building_id FROM public.floor WHERE id = equipment.floor_id) WHERE project_id=$1 AND floor_id is not null" plan = plpy.prepare(str, ["text"]) data = plan.execute([project_id]) except Exception as e: return False else: return True $$ LANGUAGE 'plpython3u' VOLATILE; ```
## 入参 1. 项目id ## 返回结果 true 成功 false 失败