设备所在竖井关系计算
1. 首先需要有设备所在业务空间关系
2. 需要有竖井包含业务空间关系
Eq2Sp
1. 通过 竖井--> 业务空间 --> 设备的间接关系, 堆出竖井和设备的关系
2. 结果保存到r_eq_in_sh表中
-- 设备所在竖井
create or replace function public.rel_eq2sh(project_id character varying) returns boolean
as
$$
try:
# 将下面对数据库的操作作为一个事务, 出异常则自动rollback
with plpy.subtransaction():
delete_plan = plpy.prepare("delete from r_eq_in_sh where project_id = $1 and sign = 2", ["text"])
delete_plan.execute([project_id])
join_plan = plpy.prepare("select sh.shaft_id as shaft_id, eq.equip_id as equip_id from r_sh_contain_sp_base as sh inner join r_eq_in_sp_base as eq on sh.space_id = eq.space_id where eq.project_id = $1 and sh.project_id = $1", ["text"])
rel = join_plan.execute([project_id])
eq2sh = dict()
for row in rel:
shaft = row['shaft_id']
equip = row['equip_id']
if shaft not in eq2sh:
eq2sh[shaft] = set()
eq_set = eq2sh[shaft]
eq_set.add(equip)
for sid, eq_set in eq2sh.items():
for eq_id in eq_set:
plan = plpy.prepare("insert into r_eq_in_sh(shaft_id, equip_id, project_id, sign) values($1, $2, $3, 2)", ["text", "text", "text"])
plan.execute([sid, eq_id, project_id])
except Exception as e:
plpy.warning(e)
return False
else:
return True
$$
LANGUAGE 'plpython3u' VOLATILE;
1. 项目id
1. select public.rel_eq2sh('Pj1101010015');