classify 该变量是根据函数的输入将函数分类
dependency 该变量管理函数的依赖
源码
CREATE OR REPLACE FUNCTION "public"."f_rel_calc"("func" text, "project_id" varchar, "tables" text, "out_tables" text, "sign1" int4, "sign2" int4)
RETURNS "pg_catalog"."int4" AS $BODY$
# 根据函数输入类型分类
classify = {"eq2bd":1, "eq2fl":1, "sp2fl":1, "eq2sh":1, "pe2bd":1, "pe2fl":1, "pe2sh":1, "pe2sp":1, "sh2sh":1, "sy2bd":1,
"sy2fl":1, "sy2sh":1, "sy2sp":1, "sh2bd":1, "sp2sp_transport":1, "sp2sp_light":1, "sp2sp_convection":1, "bd2sp":2, "eq2sp":3, "fl2fl":4}
dependency = {"eq2bd":["eq2fl"], "eq2sh":["eq2sp"], "eq2sp":["eq2fl", "sp2fl"], "pe2bd":["eq2bd"], "pe2fl":["eq2fl"], "pe2sh":["eq2sh"],
"pe2sp":["eq2sp"], "sy2bd":["eq2bd"], "sy2fl":["eq2fl"], "sy2sh":["eq2sh"], "sy2sp":["eq2sp"]}
def get_type_by_func_name(func_name):
if "_" in func_name:
return func_name[int(func_name.index("_")) + 1:]
else:
return func_name
try:
with plpy.subtransaction():
type = get_type_by_func_name(func)
if type not in classify:
return 100
class_id = classify[type]
plpy.info("type : {0}, class_id : {1}, projectId : {2}".format(type, class_id, project_id))
status_plan = plpy.prepare("select graphtype.f_get_calc_state($1, $2)", ["text", "text"])
status = status_plan.execute([project_id, type])
plpy.info("status : {0}".format(status))
status = status[0]['f_get_calc_state']
plpy.info("status : {0}".format(status))
#if status != 2:
#return status
# 更新计算状态, 开始计算
set_status_plan = plpy.prepare("call graphtype.f_upd_calc_state($1, $2, 3)", ["text", "text"])
set_status_plan.execute([project_id, type])
# 开始计算依赖项
if type in dependency:
dep = dependency[type]
for dep_func in dep:
dep_plan = plpy.prepare("select public.f_rel_calc($1, $2, $3, $4, $5, $6)", ["text", "text", "text", "text", "int4", "int4"])
plpy.info("dep_func : {0}".format(dep_func))
result = dep_plan.execute(['rel_'+dep_func, project_id, tables, out_tables, sign1, sign2])
result_int = result[0]['f_rel_calc']
if result_int != 0:
return -200 + result_int
if class_id == 1:
result_plan = plpy.prepare("select public.{0}($1)".format(func), ["text"])
result = result_plan.execute([project_id])
elif class_id == 2:
result_plan = plpy.prepare("select public.{0}($1, $2)".format(func), ["text", "text"])
result = result_plan.execute([tables, project_id])
elif class_id == 3:
result_plan = plpy.prepare("select public.{0}($1, $2, $3, $4, $5)".format(func), ["text", "text", "text", "int4", "int4"])
result = result_plan.execute([tables, out_tables, project_id, sign1, sign2])
elif class_id == 4:
result_plan = plpy.prepare("select public.{0}($1, $2, $3)".format(func), ["text", "int4", "int4"])
result = result_plan.execute([project_id, sign1, sign2])
result_bool = result[0][func]
plpy.info("func : {0}, result : {1}".format(func, result_bool))
if result_bool:
set_status_plan = plpy.prepare("call graphtype.f_upd_calc_state($1, $2, 1)", ["text", "text"])
set_status_plan.execute([project_id, type])
else:
set_status_plan = plpy.prepare("call graphtype.f_upd_calc_state($1, $2, 5)", ["text", "text"])
set_status_plan.execute([project_id, type])
return 0 if result_bool else -1
except Exception as e:
set_status_plan = plpy.prepare("call graphtype.f_upd_calc_state($1, $2, 5)", ["text", "text"])
set_status_plan.execute([project_id, type])
plpy.info(e)
return -1
else:
return 0
$BODY$
LANGUAGE plpython3u VOLATILE
COST 100
例子:
select public.f_rel_calc('rel_eq2sh', 'Pj*******', 'zone_space_base','relationship.r_eq2sp', 2, 2)
1. 想要计算的函数名
2. 项目id
3. 依赖的函数或此函数需要的业务空间的表名
4. 表示依赖的函数或此函数需要导出关系的表
5, 6. 删除旧关系时需要指明删除sign值为多少的关系
true 成功
false 失败