tww
.tww_app
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
update_wastewater_node_symbology(_obj_id text, _all boolean DEFAULT false)
Parameters
Name
Type
Mode
_obj_id
text
IN
_all
boolean
IN
Definition
BEGIN -- Otherwise this will result in very slow query due to on_structure_part_change_networkelement -- being triggered for all rows. See https://github.com/QGEP/datamodel/pull/166#issuecomment-760245405 //skip-keyword-check IF _all THEN RAISE INFO 'Temporarily disabling symbology and modification triggers'; PERFORM tww_app.alter_symbology_triggers('disable'); PERFORM tww_app.alter_modification_triggers('disable'); END IF; UPDATE tww_od.wastewater_node n SET _function_hierarchic = function_hierarchic, _usage_current = usage_current, _status = status FROM( SELECT DISTINCT ON (wn.obj_id) wn.obj_id AS wn_obj_id, COALESCE(first_value(CH_from.function_hierarchic) OVER w , first_value(CH_to.function_hierarchic) OVER w) AS function_hierarchic, COALESCE(first_value(CH_from.usage_current) OVER w , first_value(CH_to.usage_current) OVER w) AS usage_current, COALESCE(first_value(ws_node.status) OVER w , first_value(ws_from.status) OVER w , first_value(ws_to.status) OVER w) AS status, rank() OVER w AS hierarchy_rank FROM tww_od.wastewater_node wn LEFT JOIN tww_od.wastewater_networkelement ne ON ne.obj_id = wn.obj_id LEFT JOIN tww_od.wastewater_structure ws_node ON ws_node.obj_id = ne.fk_wastewater_structure LEFT JOIN tww_od.reach_point rp ON wn.obj_id = rp.fk_wastewater_networkelement LEFT JOIN tww_od.reach re_from ON re_from.fk_reach_point_from = rp.obj_id LEFT JOIN tww_od.wastewater_networkelement ne_from ON ne_from.obj_id = re_from.obj_id LEFT JOIN tww_od.channel CH_from ON CH_from.obj_id = ne_from.fk_wastewater_structure LEFT JOIN tww_od.wastewater_structure ws_from ON ws_from.obj_id = ne_from.fk_wastewater_structure LEFT JOIN tww_vl.channel_function_hierarchic vl_fct_hier_from ON CH_from.function_hierarchic = vl_fct_hier_from.code LEFT JOIN tww_vl.channel_usage_current vl_usg_curr_from ON CH_from.usage_current = vl_usg_curr_from.code LEFT JOIN tww_od.reach re_to ON re_to.fk_reach_point_to = rp.obj_id LEFT JOIN tww_od.wastewater_networkelement ne_to ON ne_to.obj_id = re_to.obj_id LEFT JOIN tww_od.channel CH_to ON CH_to.obj_id = ne_to.fk_wastewater_structure LEFT JOIN tww_od.wastewater_structure ws_to ON ws_to.obj_id = ne_to.fk_wastewater_structure LEFT JOIN tww_vl.channel_function_hierarchic vl_fct_hier_to ON CH_to.function_hierarchic = vl_fct_hier_to.code LEFT JOIN tww_vl.channel_usage_current vl_usg_curr_to ON CH_to.usage_current = vl_usg_curr_to.code WHERE _all OR wn.obj_id = _obj_id WINDOW w AS ( PARTITION BY wn.obj_id ORDER BY coalesce(vl_fct_hier_to.tww_symbology_inflow_prio,false) DESC , vl_fct_hier_from.tww_symbology_order ASC NULLS LAST , vl_fct_hier_to.tww_symbology_order ASC NULLS LAST , vl_usg_curr_from.tww_symbology_order ASC NULLS LAST , vl_usg_curr_to.tww_symbology_order ASC NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ) symbology_ne WHERE symbology_ne.wn_obj_id = n.obj_id; EXECUTE tww_app.update_wn_symbology_by_overflow(_obj_id, _all); -- See above IF _all THEN RAISE INFO 'Reenabling symbology and modification triggers'; PERFORM tww_app.alter_symbology_triggers('enable'); PERFORM tww_app.alter_modification_triggers('enable'); END IF; END