建筑下的业务空间
有所属楼层的业务空间(所有业务空间表都需要处理)
FROM zone_* WHERE project_id='Pj4201050001' AND floor_id is not null
从绑定的楼层里取建筑ID, 将建筑ID填入设备ID(建筑ID为空,则设备的建筑ID也为空)。
SQL
update zone_air_conditioning 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 character varying) returns boolean
as
$$
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
$$
LANGUAGE 'plpython3u' VOLATILE;
1. 参与计算的表的全名称, 带schema名, 以英文逗号隔开
2. 项目id
true 成功
false 失败