tww.tww

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(wn._function_hierarchic) OVER w
              , first_value(wn_from._function_hierarchic) OVER w) AS function_hierarchic,
      COALESCE(first_value(wn._usage_current) OVER w
              , first_value(wn_from._usage_current) OVER w) AS usage_current,
      COALESCE(first_value(wn._status) OVER w
             , first_value(wn_from._status) OVER w) AS status
    FROM
	  tww_od.overflow                    ov
	  LEFT JOIN tww_od.wastewater_node 			   wn	  	  ON ov.fk_overflow_to=wn.obj_id
      LEFT JOIN tww_od.wastewater_networkelement   ne_ov      ON ne_ov.obj_id = ov.fk_wastewater_node
      LEFT JOIN tww_vl.channel_function_hierarchic vl_fct_hier	ON wn._function_hierarchic = vl_fct_hier.code
      LEFT JOIN tww_vl.channel_usage_current       vl_usg_curr	ON wn._usage_current = vl_usg_curr.code

LEFT JOIN tww_od.wastewater_node			   wn_from	  ON ne_ov.obj_id = wn_from.obj_id
	  LEFT JOIN tww_vl.channel_function_hierarchic vl_fct_hier_from	ON wn_from._function_hierarchic = vl_fct_hier_from.code
      LEFT JOIN tww_vl.channel_usage_current       vl_usg_curr_from	ON wn_from._usage_current = vl_usg_curr_from.code
	  WHERE (_all OR wn.obj_id = _obj_id)
      WINDOW w AS ( PARTITION BY wn.obj_id
                    ORDER BY coalesce(vl_fct_hier.tww_symbology_inflow_prio,false) DESC
						   , vl_fct_hier.tww_symbology_order ASC NULLS LAST
                           , vl_fct_hier_from.tww_symbology_order ASC NULLS LAST

, vl_usg_curr.tww_symbology_order ASC NULLS LAST
                           , vl_usg_curr_from.tww_symbology_order ASC NULLS LAST
                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
) symbology_ne
WHERE symbology_ne.wn_obj_id = n.obj_id
 	  AND TRUE = ANY(array[n._function_hierarchic IS NULL
					  ,n._usage_current IS NULL
					  ,n._status IS NULL]);

-- See above
IF _all THEN
  RAISE INFO 'Reenabling symbology triggers';
  PERFORM tww_app.alter_symbology_triggers('enable');
  PERFORM tww_app.alter_modification_triggers('enable');
END IF;

END

update_wn_symbology_by_overflow(_obj_id text, _all boolean DEFAULT false)

Parameters

Definition