Eq2Sh.md 1.7 KB

设备所在竖井关系计算

前置条件

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');