设备所在建筑
设备已经绑定楼层
FROM equipment WHERE project_id='Pj4201050001' AND floor_id is not null
Eq2Fl
从绑定的楼层里取建筑ID, 将建筑ID填入设备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
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 失败