设备所在竖井关系计算 ## 前置条件 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');