Alternative view for wastewaterstructure

vw_qgep_wastewater_structure2.sql

-- View: qgep_od.vw_qgep_wastewater_structure2

-- DROP VIEW qgep_od.vw_qgep_wastewater_structure2;

CREATE OR REPLACE VIEW qgep_od.vw_qgep_wastewater_structure2 AS 
 SELECT ws.identifier,
        CASE
            WHEN ma.obj_id IS NOT NULL THEN 'manhole'::text
            WHEN ss.obj_id IS NOT NULL THEN 'special_structure'::text
            WHEN dp.obj_id IS NOT NULL THEN 'discharge_point'::text
            WHEN ii.obj_id IS NOT NULL THEN 'infiltration_installation'::text
            ELSE 'unknown'::text
        END AS ws_type,
    ma.function AS ma_function,
    ss.function AS ss_function,
    ws.fk_owner,
    ws.status,
    ws.accessibility,
    ws.contract_section,
    ws.financing,
    ws.gross_costs,
    ws.inspection_interval,
    ws.location_name,
    ws.records,
    ws.remark,
    ws.renovation_necessity,
    ws.replacement_value,
    ws.rv_base_year,
    ws.rv_construction_type,
    ws.structure_condition,
    ws.subsidies,
    ws.year_of_construction,
    ws.year_of_replacement,
    ws.last_modification,
    ws.fk_operator,
    ws.fk_dataowner,
    ws.fk_provider,
    ws._depth,
    ws.obj_id,
    main_co_sp.identifier AS co_identifier,
    main_co.brand AS co_brand,
    main_co.cover_shape AS co_shape,
    main_co.diameter AS co_diameter,
    main_co.fastening AS co_fastening,
    main_co.level AS co_level,
    main_co.material AS co_material,
    main_co.positional_accuracy AS co_positional_accuracy,
    aggregated_wastewater_structure.situation_geometry,
    main_co.sludge_bucket AS co_sludge_bucket,
    main_co.venting AS co_venting,
    main_co_sp.remark AS co_remark,
    main_co_sp.renovation_demand AS co_renovation_demand,
    main_co.obj_id AS co_obj_id,
    ma.material AS ma_material,
    ma.surface_inflow AS ma_surface_inflow,
    ma.dimension1 AS ma_dimension1,
    ma.dimension2 AS ma_dimension2,
    ma._orientation AS ma_orientation,
    ss.bypass AS ss_bypass,
    ss.emergency_spillway AS ss_emergency_spillway,
    ss.stormwater_tank_arrangement AS ss_stormwater_tank_arrangement,
    ss.upper_elevation AS ss_upper_elevation,
    ii.absorption_capacity AS ii_absorption_capacity,
    ii.defects AS ii_defects,
    ii.dimension1 AS ii_dimension1,
    ii.dimension2 AS ii_dimension2,
    ii.distance_to_aquifer AS ii_distance_to_aquifer,
    ii.effective_area AS ii_effective_area,
    ii.emergency_spillway AS ii_emergency_spillway,
    ii.kind AS ii_kind,
    ii.labeling AS ii_labeling,
    ii.seepage_utilization AS ii_seepage_utilization,
    ii.upper_elevation AS ii_upper_elevation,
    ii.vehicle_access AS ii_vehicle_access,
    ii.watertightness AS ii_watertightness,
    dp.highwater_level AS dp_highwater_level,
    dp.relevance AS dp_relevance,
    dp.terrain_level AS dp_terrain_level,
    dp.upper_elevation AS dp_upper_elevation,
    dp.waterlevel_hydraulic AS dp_waterlevel_hydraulic,
    wn.identifier AS wn_identifier,
    wn.obj_id AS wn_obj_id,
    wn.backflow_level AS wn_backflow_level,
    wn.bottom_level AS wn_bottom_level,
    wn.remark AS wn_remark,
    wn.last_modification AS wn_last_modification,
    wn.fk_dataowner AS wn_fk_dataowner,
    wn.fk_provider AS wn_fk_provider,
    wn.situation_geometry AS wn_situation_geometry,
    ws._label,
    ws._usage_current AS _channel_usage_current,
    ws._function_hierarchic AS _channel_function_hierarchic
   FROM ( SELECT ws_1.obj_id,
            st_collect(st_accum(wn_1.situation_geometry) || st_accum(co.situation_geometry))::geometry(MultiPoint,21781) AS situation_geometry,
                CASE
                    WHEN count(wn_1.obj_id) = 1 THEN min(wn_1.obj_id::text)
                    ELSE NULL::text
                END AS wn_obj_id
           FROM qgep_od.wastewater_structure ws_1
             FULL JOIN qgep_od.structure_part sp ON sp.fk_wastewater_structure::text = ws_1.obj_id::text
             LEFT JOIN qgep_od.cover co ON co.obj_id::text = sp.obj_id::text
             RIGHT JOIN qgep_od.wastewater_networkelement ne ON ne.fk_wastewater_structure::text = ws_1.obj_id::text
             RIGHT JOIN qgep_od.wastewater_node wn_1 ON wn_1.obj_id::text = ne.obj_id::text
          GROUP BY ws_1.obj_id) aggregated_wastewater_structure
     LEFT JOIN qgep_od.wastewater_structure ws ON ws.obj_id::text = aggregated_wastewater_structure.obj_id::text
     LEFT JOIN qgep_od.cover main_co ON main_co.obj_id::text = ws.fk_main_cover::text
     LEFT JOIN qgep_od.structure_part main_co_sp ON main_co_sp.obj_id::text = ws.fk_main_cover::text
     LEFT JOIN qgep_od.manhole ma ON ma.obj_id::text = ws.obj_id::text
     LEFT JOIN qgep_od.special_structure ss ON ss.obj_id::text = ws.obj_id::text
     LEFT JOIN qgep_od.discharge_point dp ON dp.obj_id::text = ws.obj_id::text
     LEFT JOIN qgep_od.infiltration_installation ii ON ii.obj_id::text = ws.obj_id::text
     LEFT JOIN qgep_od.vw_wastewater_node wn ON wn.obj_id::text = aggregated_wastewater_structure.wn_obj_id;

ALTER TABLE qgep_od.vw_qgep_wastewater_structure2
  OWNER TO postgres;
GRANT ALL ON TABLE qgep_od.vw_qgep_wastewater_structure2 TO postgres;
GRANT SELECT, REFERENCES, TRIGGER ON TABLE qgep_od.vw_qgep_wastewater_structure2 TO qgep_viewer;
GRANT ALL ON TABLE qgep_od.vw_qgep_wastewater_structure2 TO qgep_user;
GRANT ALL ON TABLE qgep_od.vw_qgep_wastewater_structure2 TO qgep_manager;
ALTER TABLE qgep_od.vw_qgep_wastewater_structure2 ALTER COLUMN obj_id SET DEFAULT qgep_sys.generate_oid('qgep_od'::text, 'wastewater_structure'::text);
ALTER TABLE qgep_od.vw_qgep_wastewater_structure2 ALTER COLUMN co_obj_id SET DEFAULT qgep_sys.generate_oid('qgep_od'::text, 'structure_part'::text);


-- Trigger: vw_qgep_wastewater_structure_on_delete2 on qgep_od.vw_qgep_wastewater_structure2

-- DROP TRIGGER vw_qgep_wastewater_structure_on_delete2 ON qgep_od.vw_qgep_wastewater_structure2;

CREATE TRIGGER vw_qgep_wastewater_structure_on_delete2
  INSTEAD OF DELETE
  ON qgep_od.vw_qgep_wastewater_structure2
  FOR EACH ROW
  EXECUTE PROCEDURE qgep_od.vw_qgep_wastewater_structure_delete();

-- Trigger: vw_qgep_wastewater_structure_on_insert2 on qgep_od.vw_qgep_wastewater_structure2

-- DROP TRIGGER vw_qgep_wastewater_structure_on_insert2 ON qgep_od.vw_qgep_wastewater_structure2;

CREATE TRIGGER vw_qgep_wastewater_structure_on_insert2
  INSTEAD OF INSERT
  ON qgep_od.vw_qgep_wastewater_structure2
  FOR EACH ROW
  EXECUTE PROCEDURE qgep_od.vw_qgep_wastewater_structure_insert();

-- Trigger: vw_qgep_wastewater_structure_on_update2 on qgep_od.vw_qgep_wastewater_structure2

-- DROP TRIGGER vw_qgep_wastewater_structure_on_update2 ON qgep_od.vw_qgep_wastewater_structure2;

CREATE TRIGGER vw_qgep_wastewater_structure_on_update2
  INSTEAD OF UPDATE
  ON qgep_od.vw_qgep_wastewater_structure2
  FOR EACH ROW
  EXECUTE PROCEDURE qgep_od.vw_qgep_wastewater_structure_update();