tww
.tww_app
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ft_vw_tww_wastewater_structure_insert()
Parameters
Name
Type
Mode
IN
Definition
BEGIN NEW.identifier = COALESCE(NEW.identifier, NEW.obj_id); INSERT INTO tww_od.wastewater_structure ( obj_id -- , _bottom_label -- , _cover_label , _depth -- , _input_label -- , _label -- , _output_label , accessibility , condition_score , contract_section -- , detail_geometry3d_geometry , elevation_determination , financing , fk_dataowner -- , fk_main_cover -- , fk_main_wastewater_node , fk_operator , fk_owner , fk_provider , gross_costs , identifier , inspection_interval , last_modification , location_name , records , remark , renovation_necessity , replacement_value , rv_base_year , rv_construction_type , status , status_survey_year , structure_condition , subsidies , urgency_figure , year_of_construction , year_of_replacement ) VALUES ( NEW.obj_id -- , NEW._bottom_label -- , NEW._cover_label , NEW._depth -- , NEW._input_label -- , NEW._label -- , NEW._output_label , NEW.accessibility , NEW.condition_score , NEW.contract_section -- , NEW.detail_geometry3d_geometry , NEW.elevation_determination , NEW.financing , NEW.fk_dataowner -- , NEW.fk_main_cover -- , NEW.fk_main_wastewater_node , NEW.fk_operator , NEW.fk_owner , NEW.fk_provider , NEW.gross_costs , NEW.identifier , NEW.inspection_interval , NEW.last_modification , NEW.location_name , NEW.records , NEW.remark , NEW.renovation_necessity , NEW.replacement_value , NEW.rv_base_year , NEW.rv_construction_type , NEW.status , NEW.status_survey_year , NEW.structure_condition , NEW.subsidies , NEW.urgency_figure , NEW.year_of_construction , NEW.year_of_replacement ); CASE WHEN NEW.ws_type = 'manhole' THEN -- Manhole INSERT INTO tww_od.manhole ( obj_id -- , _orientation , amphibian_exit , dimension1 , dimension2 , function , material , possibility_intervention , surface_inflow ) VALUES ( NEW.obj_id -- , NEW.ma__orientation , NEW.ma_amphibian_exit , NEW.ma_dimension1 , NEW.ma_dimension2 , NEW.ma_function , NEW.ma_material , NEW.ma_possibility_intervention , NEW.ma_surface_inflow ); -- Special Structure WHEN NEW.ws_type = 'special_structure' THEN INSERT INTO tww_od.special_structure ( obj_id , amphibian_exit , bypass , emergency_overflow , function , possibility_intervention , stormwater_tank_arrangement , upper_elevation ) VALUES ( NEW.obj_id , NEW.ss_amphibian_exit , NEW.ss_bypass , NEW.ss_emergency_overflow , NEW.ss_function , NEW.ss_possibility_intervention , NEW.ss_stormwater_tank_arrangement , NEW.ss_upper_elevation ); -- Discharge Point WHEN NEW.ws_type = 'discharge_point' THEN INSERT INTO tww_od.discharge_point ( obj_id , highwater_level , relevance , terrain_level , upper_elevation , water_course_number , water_course_segment_canton , waterlevel_hydraulic ) VALUES ( NEW.obj_id , NEW.dp_highwater_level , NEW.dp_relevance , NEW.dp_terrain_level , NEW.dp_upper_elevation , NEW.dp_water_course_number , NEW.dp_water_course_segment_canton , NEW.dp_waterlevel_hydraulic ); -- Infiltration Installation WHEN NEW.ws_type = 'infiltration_installation' THEN INSERT INTO tww_od.infiltration_installation ( obj_id , absorption_capacity , defects , dimension1 , dimension2 , distance_to_aquifer , effective_area , emergency_overflow , filling_material , fk_dss15_aquifer , kind , labeling , seepage_utilization , upper_elevation , vehicle_access , watertightness ) VALUES ( NEW.obj_id , NEW.ii_absorption_capacity , NEW.ii_defects , NEW.ii_dimension1 , NEW.ii_dimension2 , NEW.ii_distance_to_aquifer , NEW.ii_effective_area , NEW.ii_emergency_overflow , NEW.ii_filling_material , NEW.ii_fk_dss15_aquifer , NEW.ii_kind , NEW.ii_labeling , NEW.ii_seepage_utilization , NEW.ii_upper_elevation , NEW.ii_vehicle_access , NEW.ii_watertightness ); ELSE RAISE NOTICE 'Wastewater structure type not handled by this view (%)', NEW.ws_type; -- ERROR END CASE; INSERT INTO tww_app.vw_wastewater_node ( -- _function_hierarchic -- _status -- _usage_current backflow_level_current , bottom_level , elevation_accuracy , fk_dataowner , fk_hydr_geometry , fk_provider , fk_wastewater_structure , function_node_amelioration , identifier , last_modification , obj_id , remark , situation3d_geometry , wwtp_number ) VALUES ( -- NEW.wn__function_hierarchic -- NEW.wn__status -- NEW.wn__usage_current NEW.wn_backflow_level_current , NEW.wn_bottom_level , NEW.wn_elevation_accuracy , COALESCE(NULLIF(NEW.wn_fk_dataowner,''), NEW.fk_dataowner) -- fk_dataowner , NEW.wn_fk_hydr_geometry , COALESCE(NULLIF(NEW.wn_fk_provider,''), NEW.fk_provider) -- fk_provider , NEW.obj_id -- fk_wastewater_structure , NEW.wn_function_node_amelioration , COALESCE(NULLIF(NEW.wn_identifier,''), NEW.identifier) -- identifier , NOW() -- last_modification , NEW.wn_obj_id , NEW.wn_remark , ST_SetSRID(ST_MakePoint(ST_X(NEW.situation3d_geometry), ST_Y(NEW.situation3d_geometry), 'nan'), 2056 ) -- situation3d_geometry , NEW.wn_wwtp_number ); UPDATE tww_od.wastewater_structure SET fk_main_wastewater_node = NEW.wn_obj_id WHERE obj_id = NEW.obj_id; INSERT INTO tww_app.vw_cover ( brand , cover_shape , diameter , fastening , fk_dataowner , fk_provider , fk_wastewater_structure , identifier , last_modification , level , material , obj_id , positional_accuracy , remark , renovation_demand , situation3d_geometry , sludge_bucket , venting ) VALUES ( NEW.co_brand , NEW.co_shape , NEW.co_diameter , NEW.co_fastening , NEW.fk_dataowner -- fk_dataowner , NEW.fk_provider -- fk_provider , NEW.obj_id -- fk_wastewater_structure , COALESCE(NULLIF(NEW.co_identifier,''), NEW.identifier) -- identifier , NOW() -- last_modification , NEW.co_level , NEW.co_material , NEW.co_obj_id , NEW.co_positional_accuracy , NEW.co_remark , NEW.co_renovation_demand , ST_SetSRID(ST_MakePoint(ST_X(NEW.situation3d_geometry), ST_Y(NEW.situation3d_geometry), 'nan'), 2056 ) -- situation3d_geometry , NEW.co_sludge_bucket , NEW.co_venting ); UPDATE tww_od.wastewater_structure SET fk_main_cover = NEW.co_obj_id WHERE obj_id = NEW.obj_id; RETURN NEW; END;