Compare text

Find the difference between two text files
Real-time diff
Unified diff
Collapse lines
Highlight change
Syntax highlighting
Tools

Untitled diff

Created Diff never expires
9 removals
486 lines
7 additions
484 lines
-- Table: qwat_od.valve
-- Table: qwat_od.valve


-- DROP TABLE qwat_od.valve;
-- DROP TABLE qwat_od.valve;


CREATE TABLE qwat_od.valve
CREATE TABLE qwat_od.valve
(
(
id integer NOT NULL DEFAULT nextval('qwat_od.valve_id_seq'::regclass),
id serial NOT NULL,
fk_valve_type integer NOT NULL,
fk_valve_type integer NOT NULL,
fk_valve_function integer NOT NULL,
fk_valve_function integer NOT NULL,
fk_valve_actuation integer NOT NULL,
fk_valve_actuation integer NOT NULL,
fk_pipe integer,
fk_pipe integer,
fk_handle_precision integer,
fk_handle_precision integer,
fk_handle_precisionalti integer,
fk_handle_precisionalti integer,
fk_maintenance integer[],
fk_maintenance integer[],
diameter_nominal character varying(10),
diameter_nominal character varying(10),
closed boolean DEFAULT false,
closed boolean DEFAULT false,
networkseparation boolean DEFAULT false,
networkseparation boolean DEFAULT false,
handle_altitude numeric(10,3),
handle_altitude numeric(10,3),
handle_geometry geometry(PointZ,21781),
handle_geometry geometry(PointZ,21781),
fk_district integer,
fk_district integer,
fk_pressurezone integer,
fk_pressurezone integer,
fk_distributor integer NOT NULL,
fk_distributor integer NOT NULL,
fk_precision integer NOT NULL,
fk_precision integer NOT NULL,
fk_precisionalti integer,
fk_precisionalti integer,
fk_status integer NOT NULL,
fk_status integer NOT NULL,
fk_object_reference integer,
fk_object_reference integer,
fk_folder integer,
fk_folder integer,
year smallint,
year smallint,
year_end smallint,
year_end smallint,
altitude numeric(10,3) DEFAULT NULL::numeric,
altitude numeric(10,3) DEFAULT NULL::numeric,
orientation double precision,
orientation double precision,
fk_locationtype integer[],
fk_locationtype integer[],
identification character varying(50),
identification character varying(50),
remark text,
remark text,
fk_printmap integer[],
fk_printmap integer[],
_geometry_alt1_used boolean,
_geometry_alt1_used boolean,
_geometry_alt2_used boolean,
_geometry_alt2_used boolean,
_pipe_node_type qwat_od.pipe_connection,
_pipe_node_type qwat_od.pipe_connection,
_pipe_orientation double precision DEFAULT 0,
_pipe_orientation double precision DEFAULT 0,
_pipe_schema_visible boolean DEFAULT false,
_pipe_schema_visible boolean DEFAULT false,
_printmaps text,
_printmaps text,
geometry geometry(PointZ,21781) NOT NULL,
geometry geometry(PointZ,21781) NOT NULL,
geometry_alt1 geometry(PointZ,21781),
geometry_alt1 geometry(PointZ,21781),
geometry_alt2 geometry(PointZ,21781),
geometry_alt2 geometry(PointZ,21781),
update_geometry_alt1 boolean,
update_geometry_alt1 boolean,
update_geometry_alt2 boolean,
update_geometry_alt2 boolean,
schema_force_visible boolean,
_schema_visible boolean, -- define if the element is visible on schematic view. Update by trigger as COALESCE(schema_force_visible, valve_function.schema_visible)
label_1_visible smallint DEFAULT 1,
label_1_visible smallint DEFAULT 1,
label_1_x double precision,
label_1_x double precision,
label_1_y double precision,
label_1_y double precision,
label_1_rotation double precision,
label_1_rotation double precision,
label_1_text character varying(120),
label_1_text character varying(120),
label_2_visible smallint DEFAULT 1,
label_2_visible smallint DEFAULT 1,
label_2_x double precision,
label_2_x double precision,
label_2_y double precision,
label_2_y double precision,
label_2_rotation double precision,
label_2_rotation double precision,
label_2_text character varying(120),
label_2_text character varying(120),
schema_force_visible boolean,
_schema_visible boolean, -- define if the element is visible on schematic view. Update by trigger as COALESCE(schema_force_visible, valve_function.schema_visible)
CONSTRAINT valve_pkey PRIMARY KEY (id),
CONSTRAINT valve_pkey PRIMARY KEY (id),
CONSTRAINT valve_fk_distributor FOREIGN KEY (fk_distributor)
CONSTRAINT valve_fk_distributor FOREIGN KEY (fk_distributor)
REFERENCES qwat_od.distributor (id) MATCH FULL
REFERENCES qwat_od.distributor (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_district FOREIGN KEY (fk_district)
CONSTRAINT valve_fk_district FOREIGN KEY (fk_district)
REFERENCES qwat_od.district (id) MATCH FULL
REFERENCES qwat_od.district (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_folder FOREIGN KEY (fk_folder)
CONSTRAINT valve_fk_folder FOREIGN KEY (fk_folder)
REFERENCES qwat_od.folder (id) MATCH FULL
REFERENCES qwat_od.folder (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_function FOREIGN KEY (fk_valve_function)
CONSTRAINT valve_fk_function FOREIGN KEY (fk_valve_function)
REFERENCES qwat_vl.valve_function (id) MATCH FULL
REFERENCES qwat_vl.valve_function (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_handle_precision FOREIGN KEY (fk_handle_precision)
CONSTRAINT valve_fk_handle_precision FOREIGN KEY (fk_handle_precision)
REFERENCES qwat_vl."precision" (id) MATCH FULL
REFERENCES qwat_vl."precision" (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_handle_precisionalti FOREIGN KEY (fk_handle_precisionalti)
CONSTRAINT valve_fk_handle_precisionalti FOREIGN KEY (fk_handle_precisionalti)
REFERENCES qwat_vl.precisionalti (id) MATCH FULL
REFERENCES qwat_vl.precisionalti (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_object_reference FOREIGN KEY (fk_object_reference)
CONSTRAINT valve_fk_object_reference FOREIGN KEY (fk_object_reference)
REFERENCES qwat_vl.object_reference (id) MATCH FULL
REFERENCES qwat_vl.object_reference (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_pipe FOREIGN KEY (fk_pipe)
CONSTRAINT valve_fk_pipe FOREIGN KEY (fk_pipe)
REFERENCES qwat_od.pipe (id) MATCH FULL
REFERENCES qwat_od.pipe (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_precision FOREIGN KEY (fk_precision)
CONSTRAINT valve_fk_precision FOREIGN KEY (fk_precision)
REFERENCES qwat_vl."precision" (id) MATCH FULL
REFERENCES qwat_vl."precision" (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_precisionalti FOREIGN KEY (fk_precisionalti)
CONSTRAINT valve_fk_precisionalti FOREIGN KEY (fk_precisionalti)
REFERENCES qwat_vl.precisionalti (id) MATCH FULL
REFERENCES qwat_vl.precisionalti (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_pressurezone FOREIGN KEY (fk_pressurezone)
CONSTRAINT valve_fk_pressurezone FOREIGN KEY (fk_pressurezone)
REFERENCES qwat_od.pressurezone (id) MATCH FULL
REFERENCES qwat_od.pressurezone (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_status FOREIGN KEY (fk_status)
CONSTRAINT valve_fk_status FOREIGN KEY (fk_status)
REFERENCES qwat_vl.status (id) MATCH FULL
REFERENCES qwat_vl.status (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_type FOREIGN KEY (fk_valve_type)
CONSTRAINT valve_fk_type FOREIGN KEY (fk_valve_type)
REFERENCES qwat_vl.valve_type (id) MATCH FULL
REFERENCES qwat_vl.valve_type (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_valve_actuation FOREIGN KEY (fk_valve_actuation)
CONSTRAINT valve_fk_valve_actuation FOREIGN KEY (fk_valve_actuation)
REFERENCES qwat_vl.valve_actuation (id) MATCH FULL
REFERENCES qwat_vl.valve_actuation (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_label_1_visible FOREIGN KEY (label_1_visible)
CONSTRAINT valve_label_1_visible FOREIGN KEY (label_1_visible)
REFERENCES qwat_vl.visible (vl_code_int) MATCH SIMPLE
REFERENCES qwat_vl.visible (vl_code_int) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_label_2_visible FOREIGN KEY (label_2_visible)
CONSTRAINT valve_label_2_visible FOREIGN KEY (label_2_visible)
REFERENCES qwat_vl.visible (vl_code_int) MATCH SIMPLE
REFERENCES qwat_vl.visible (vl_code_int) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_schema_force_visible FOREIGN KEY (schema_force_visible)
CONSTRAINT valve_schema_force_visible FOREIGN KEY (schema_force_visible)
REFERENCES qwat_vl.visible (vl_code) MATCH FULL
REFERENCES qwat_vl.visible (vl_code) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT chk_valve_altitude_obj_ref CHECK (fk_object_reference IS NOT NULL OR altitude IS NULL),
CONSTRAINT chk_valve_altitude_precisionalti CHECK (fk_precisionalti IS NOT NULL OR altitude IS NULL),
CONSTRAINT valve_year_check CHECK (year IS NULL OR year > 1800 AND year < 2100),
CONSTRAINT valve_year_check CHECK (year IS NULL OR year > 1800 AND year < 2100),
CONSTRAINT valve_year_end_check CHECK (year_end IS NULL OR year_end > 1800 AND year_end < 2100)
CONSTRAINT valve_year_end_check CHECK (year_end IS NULL OR year_end > 1800 AND year_end < 2100)
)
)
WITH (
WITH (
OIDS=FALSE
OIDS=FALSE
);
);
ALTER TABLE qwat_od.valve
ALTER TABLE qwat_od.valve
OWNER TO postgres;
OWNER TO postgres;
GRANT ALL ON TABLE qwat_od.valve TO postgres;
GRANT SELECT, REFERENCES, TRIGGER ON TABLE qwat_od.valve TO qwat_viewer;
GRANT ALL ON TABLE qwat_od.valve TO qwat_user;
GRANT ALL ON TABLE qwat_od.valve TO qwat_manager;
COMMENT ON TABLE qwat_od.valve
COMMENT ON TABLE qwat_od.valve
IS 'Table for valve. Inherits from node.';
IS 'Table for valve.';
COMMENT ON COLUMN qwat_od.valve._schema_visible IS 'define if the element is visible on schematic view. Update by trigger as COALESCE(schema_force_visible, valve_function.schema_visible)';
COMMENT ON COLUMN qwat_od.valve._schema_visible IS 'define if the element is visible on schematic view. Update by trigger as COALESCE(schema_force_visible, valve_function.schema_visible)';




-- Index: qwat_od.fki_valve_fk_distributor
-- Index: qwat_od.fki_valve_fk_distributor


-- DROP INDEX qwat_od.fki_valve_fk_distributor;
-- DROP INDEX qwat_od.fki_valve_fk_distributor;


CREATE INDEX fki_valve_fk_distributor
CREATE INDEX fki_valve_fk_distributor
ON qwat_od.valve
ON qwat_od.valve
USING btree
USING btree
(fk_distributor);
(fk_distributor);


-- Index: qwat_od.fki_valve_fk_district
-- Index: qwat_od.fki_valve_fk_district


-- DROP INDEX qwat_od.fki_valve_fk_district;
-- DROP INDEX qwat_od.fki_valve_fk_district;


CREATE INDEX fki_valve_fk_district
CREATE INDEX fki_valve_fk_district
ON qwat_od.valve
ON qwat_od.valve
USING btree
USING btree
(fk_district);
(fk_district);


-- Index: qwat_od.fki_valve_fk_folder
-- Index: qwat_od.fki_valve_fk_folder


-- DROP INDEX qwat_od.fki_valve_fk_folder;
-- DROP INDEX qwat_od.fki_valve_fk_folder;


CREATE INDEX fki_valve_fk_folder
CREATE INDEX fki_valve_fk_folder
ON qwat_od.valve
ON qwat_od.valve
USING btree
USING btree
(fk_folder);
(fk_folder);


-- Index: qwat_od.fki_valve_fk_function
-- Index: qwat_od.fki_valve_fk_function


-- DROP INDEX qwat_od.fki_valve_fk_function;
-- DROP INDEX qwat_od.fki_valve_fk_function;


CREATE INDEX fki_valve_fk_function
CREATE INDEX fki_valve_fk_function
ON qwat_od.valve
ON qwat_od.valve
USING btree
USING btree
(fk_valve_function);
(fk_valve_function);


-- Index: qwat_od.fki_valve_fk_handle_precision
-- Index: qwat_od.fki_valve_fk_handle_precision


-- DROP INDEX qwat_od.fki_valve_fk_handle_precision;
-- DROP INDEX qwat_od.fki_valve_fk_handle_precision;


CREATE INDEX fki_valve_fk_handle_precision
CREATE INDEX fki_valve_fk_handle_precision
ON qwat_od.valve
ON qwat_od.valve
USING btree
USING btree
(fk_handle_precision);
(fk_handle_precision);


-- Index: qwat_od.fki_valve_fk_handle_precisionalti
-- Index: qwat_od.fki_valve_fk_handle_precisionalti


-- DROP INDEX qwat_od.fki_valve_fk_handle_precisionalti;
-- DROP INDEX qwat_od.fki_valve_fk_handle_precisionalti;


CREATE INDEX fki_valve_fk_handle_precisionalti
CREATE INDEX fki_valve_fk_handle_precisionalti
ON qwat_od.valve
ON qwat_od.valve
USING btree
USING btree
(fk_handle_precisionalti);
(fk_handle_precisionalti);


-- Index: qwat_od.fki_valve_fk_object_reference
-- Index: qwat_od.fki_valve_fk_object_reference


-- DROP INDEX qwat_od.fki_valve_fk_object_reference;
-- DROP INDEX qwat_od.fki_valve_fk_object_reference;


CREATE INDEX fki_valve_fk_object_reference
CREATE INDEX fki_valve_fk_object_reference
ON qwat_od.valve
ON qwat_od.valve
USING btree
USING btree
(fk_object_reference);
(fk_object_reference);


-- Index: qwat_od.fki_valve_fk_pipe
-- Index: qwat_od.fki_valve_fk_pipe


-- DROP INDEX qwat_od.fki_valve_fk_pipe;
-- DROP INDEX qwat_od.fki_valve_fk_pipe;


CREATE INDEX fki_valve_fk_pipe
CREATE INDEX fki_valve_fk_pipe
ON qwat_od.valve
ON qwat_od.valve
USING btree
USING btree
(fk_pipe);
(fk_pipe);


-- Index: qwat_od.fki_valve_fk_precision
-- Index: qwat_od.fki_valve_fk_precision


-- DROP INDEX qwat_od.fki_valve_fk_precision;
-- DROP INDEX qwat_od.fki_valve_fk_precision;


CREATE INDEX fki_valve_fk_precision
CREATE INDEX fki_valve_fk_precision
ON qwat_od.valve
ON qwat_od.valve
USING btree
USING btree
(fk_precision);
(fk_precision);


-- Index: qwat_od.fki_valve_fk_precisionalti
-- Index: qwat_od.fki_valve_fk_precisionalti


-- DROP INDEX qwat_od.fki_valve_fk_precisionalti;
-- DROP INDEX qwat_od.fki_valve_fk_precisionalti;


CREATE INDEX fki_valve_fk_precisionalti
CREATE INDEX fki_valve_fk_precisionalti
ON qwat_od.valve
ON qwat_od.valve
USING btree
USING btree
(fk_precisionalti);
(fk_precisionalti);


-- Index: qwat_od.fki_valve_fk_pressurezone
-- Index: qwat_od.fki_valve_fk_pressurezone


-- DROP INDEX qwat_od.fki_valve_fk_pressurezone;
-- DROP INDEX qwat_od.fki_valve_fk_pressurezone;


CREATE INDEX fki_valve_fk_pressurezone
CREATE INDEX fki_valve_fk_pressurezone
ON qwat_od.valve
ON qwat_od.valve
USING btree
USING btree
(fk_pressurezone);
(fk_pressurezone);


-- Index: qwat_od.fki_valve_fk_status
-- Index: qwat_od.fki_valve_fk_status


-- DROP INDEX qwat_od.fki_valve_fk_status;
-- DROP INDEX qwat_od.fki_valve_fk_status;


CREATE INDEX fki_valve_fk_status
CREATE INDEX fki_valve_fk_status
ON qwat_od.valve
ON qwat_od.valve
USING btree
USING btree
(fk_status);
(fk_status);


-- Index: qwat_od.fki_valve_fk_type
-- Index: qwat_od.fki_valve_fk_type


-- DROP INDEX qwat_od.fki_valve_fk_type;
-- DROP INDEX qwat_od.fki_valve_fk_type;


CREATE INDEX fki_valve_fk_type
CREATE INDEX fki_valve_fk_type
ON qwat_od.valve
ON qwat_od.valve
USING btree
USING btree
(fk_valve_type);
(fk_valve_type);


-- Index: qwat_od.fki_valve_fk_valve_actuation
-- Index: qwat_od.fki_valve_fk_valve_actuation


-- DROP INDEX qwat_od.fki_valve_fk_valve_actuation;
-- DROP INDEX qwat_od.fki_valve_fk_valve_actuation;


CREATE INDEX fki_valve_fk_valve_actuation
CREATE INDEX fki_valve_fk_valve_actuation
ON qwat_od.valve
ON qwat_od.valve
USING btree
USING btree
(fk_valve_actuation);
(fk_valve_actuation);


-- Index: qwat_od.fki_valve_label_1_visible
-- Index: qwat_od.fki_valve_label_1_visible


-- DROP INDEX qwat_od.fki_valve_label_1_visible;
-- DROP INDEX qwat_od.fki_valve_label_1_visible;


CREATE INDEX fki_valve_label_1_visible
CREATE INDEX fki_valve_label_1_visible
ON qwat_od.valve
ON qwat_od.valve
USING btree
USING btree
(label_1_visible);
(label_1_visible);


-- Index: qwat_od.fki_valve_label_2_visible
-- Index: qwat_od.fki_valve_label_2_visible


-- DROP INDEX qwat_od.fki_valve_label_2_visible;
-- DROP INDEX qwat_od.fki_valve_label_2_visible;


CREATE INDEX fki_valve_label_2_visible
CREATE INDEX fki_valve_label_2_visible
ON qwat_od.valve
ON qwat_od.valve
USING btree
USING btree
(label_2_visible);
(label_2_visible);


-- Index: qwat_od.fki_valve_schema_force_visible
-- Index: qwat_od.fki_valve_schema_force_visible


-- DROP INDEX qwat_od.fki_valve_schema_force_visible;
-- DROP INDEX qwat_od.fki_valve_schema_force_visible;


CREATE INDEX fki_valve_schema_force_visible
CREATE INDEX fki_valve_schema_force_visible
ON qwat_od.valve
ON qwat_od.valve
USING btree
USING btree
(schema_force_visible);
(schema_force_visible);


-- Index: qwat_od.valve_geoidx
-- Index: qwat_od.valve_geoidx


-- DROP INDEX qwat_od.valve_geoidx;
-- DROP INDEX qwat_od.valve_geoidx;


CREATE INDEX valve_geoidx
CREATE INDEX valve_geoidx
ON qwat_od.valve
ON qwat_od.valve
USING gist
USING gist
(geometry);
(geometry);


-- Index: qwat_od.valve_geoidx_alt1
-- Index: qwat_od.valve_geoidx_alt1


-- DROP INDEX qwat_od.valve_geoidx_alt1;
-- DROP INDEX qwat_od.valve_geoidx_alt1;


CREATE INDEX valve_geoidx_alt1
CREATE INDEX valve_geoidx_alt1
ON qwat_od.valve
ON qwat_od.valve
USING gist
USING gist
(geometry_alt1);
(geometry_alt1);


-- Index: qwat_od.valve_geoidx_alt2
-- Index: qwat_od.valve_geoidx_alt2


-- DROP INDEX qwat_od.valve_geoidx_alt2;
-- DROP INDEX qwat_od.valve_geoidx_alt2;


CREATE INDEX valve_geoidx_alt2
CREATE INDEX valve_geoidx_alt2
ON qwat_od.valve
ON qwat_od.valve
USING gist
USING gist
(geometry_alt2);
(geometry_alt2);




-- Trigger: audit_trigger_row on qwat_od.valve
-- Trigger: audit_trigger_row on qwat_od.valve


-- DROP TRIGGER audit_trigger_row ON qwat_od.valve;
-- DROP TRIGGER audit_trigger_row ON qwat_od.valve;


CREATE TRIGGER audit_trigger_row
CREATE TRIGGER audit_trigger_row
AFTER INSERT OR UPDATE OR DELETE
AFTER INSERT OR UPDATE OR DELETE
ON qwat_od.valve
ON qwat_od.valve
FOR EACH ROW
FOR EACH ROW
EXECUTE PROCEDURE qwat_sys.if_modified_func('true');
EXECUTE PROCEDURE qwat_sys.if_modified_func('true');


-- Trigger: audit_trigger_stm on qwat_od.valve
-- Trigger: audit_trigger_stm on qwat_od.valve


-- DROP TRIGGER audit_trigger_stm ON qwat_od.valve;
-- DROP TRIGGER audit_trigger_stm ON qwat_od.valve;


CREATE TRIGGER audit_trigger_stm
CREATE TRIGGER audit_trigger_stm
AFTER TRUNCATE
AFTER TRUNCATE
ON qwat_od.valve
ON qwat_od.valve
FOR EACH STATEMENT
FOR EACH STATEMENT
EXECUTE PROCEDURE qwat_sys.if_modified_func('true');
EXECUTE PROCEDURE qwat_sys.if_modified_func('true');


-- Trigger: tr_valve_add_pipe_vertex_insert on qwat_od.valve
-- Trigger: tr_valve_add_pipe_vertex_insert on qwat_od.valve


-- DROP TRIGGER tr_valve_add_pipe_vertex_insert ON qwat_od.valve;
-- DROP TRIGGER tr_valve_add_pipe_vertex_insert ON qwat_od.valve;


CREATE TRIGGER tr_valve_add_pipe_vertex_insert
CREATE TRIGGER tr_valve_add_pipe_vertex_insert
AFTER INSERT
AFTER INSERT
ON qwat_od.valve
ON qwat_od.valve
FOR EACH ROW
FOR EACH ROW
EXECUTE PROCEDURE qwat_od.ft_valve_add_pipe_vertex();
EXECUTE PROCEDURE qwat_od.ft_valve_add_pipe_vertex();
COMMENT ON TRIGGER tr_valve_add_pipe_vertex_insert ON qwat_od.valve IS 'Trigger: updates auto fields after insert.';
COMMENT ON TRIGGER tr_valve_add_pipe_vertex_insert ON qwat_od.valve IS 'Trigger: updates auto fields after insert.';


-- Trigger: tr_valve_add_pipe_vertex_update on qwat_od.valve
-- Trigger: tr_valve_add_pipe_vertex_update on qwat_od.valve


-- DROP TRIGGER tr_valve_add_pipe_vertex_update ON qwat_od.valve;
-- DROP TRIGGER tr_valve_add_pipe_vertex_update ON qwat_od.valve;


CREATE TRIGGER tr_valve_add_pipe_vertex_update
CREATE TRIGGER tr_valve_add_pipe_vertex_update
AFTER UPDATE OF geometry
AFTER UPDATE OF geometry
ON qwat_od.valve
ON qwat_od.valve
FOR EACH ROW
FOR EACH ROW
WHEN ((st_equals(st_force2d(new.geometry), st_force2d(old.geometry)) IS FALSE))
WHEN ((st_equals(st_force2d(new.geometry), st_force2d(old.geometry)) IS FALSE))
EXECUTE PROCEDURE qwat_od.ft_valve_add_pipe_vertex();
EXECUTE PROCEDURE qwat_od.ft_valve_add_pipe_vertex();
COMMENT ON TRIGGER tr_valve_add_pipe_vertex_update ON qwat_od.valve IS 'Trigger: updates auto fields after geom update.';
COMMENT ON TRIGGER tr_valve_add_pipe_vertex_update ON qwat_od.valve IS 'Trigger: updates auto fields after geom update.';


-- Trigger: tr_valve_altgeom_alt on qwat_od.valve
-- Trigger: tr_valve_altgeom_alt on qwat_od.valve


-- DROP TRIGGER tr_valve_altgeom_alt ON qwat_od.valve;
-- DROP TRIGGER tr_valve_altgeom_alt ON qwat_od.valve;


CREATE TRIGGER tr_valve_altgeom_alt
CREATE TRIGGER tr_valve_altgeom_alt
BEFORE UPDATE OF geometry_alt1, geometry_alt2
BEFORE UPDATE OF geometry_alt1, geometry_alt2
ON qwat_od.valve
ON qwat_od.valve
FOR EACH ROW
FOR EACH ROW
EXECUTE PROCEDURE qwat_od.ft_geometry_alternative_aux();
EXECUTE PROCEDURE qwat_od.ft_geometry_alternative_aux();
COMMENT ON TRIGGER tr_valve_altgeom_alt ON qwat_od.valve IS 'Trigger: when updating, check if alternative geometries are different to fill the boolean fields.';
COMMENT ON TRIGGER tr_valve_altgeom_alt ON qwat_od.valve IS 'Trigger: when updating, check if alternative geometries are different to fill the boolean fields.';


-- Trigger: tr_valve_altgeom_insert on qwat_od.valve
-- Trigger: tr_valve_altgeom_insert on qwat_od.valve


-- DROP TRIGGER tr_valve_altgeom_insert ON qwat_od.valve;
-- DROP TRIGGER tr_valve_altgeom_insert ON qwat_od.valve;


CREATE TRIGGER tr_valve_altgeom_insert
CREATE TRIGGER tr_valve_altgeom_insert
BEFORE INSERT
BEFORE INSERT
ON qwat_od.valve
ON qwat_od.valve
FOR EACH ROW
FOR EACH ROW
EXECUTE PROCEDURE qwat_od.ft_geometry_alternative_main();
EXECUTE PROCEDURE qwat_od.ft_geometry_alternative_main();
COMMENT ON TRIGGER tr_valve_altgeom_insert ON qwat_od.valve IS 'Trigger: handle alternative geometries on insert';
COMMENT ON TRIGGER tr_valve_altgeom_insert ON qwat_od.valve IS 'Trigger: handle alternative geometries on insert';


-- Trigger: tr_valve_altgeom_update on qwat_od.valve
-- Trigger: tr_valve_altgeom_update on qwat_od.valve


-- DROP TRIGGER tr_valve_altgeom_update ON qwat_od.valve;
-- DROP TRIGGER tr_valve_altgeom_update ON qwat_od.valve;


CREATE TRIGGER tr_valve_altgeom_update
CREATE TRIGGER tr_valve_altgeom_update
BEFORE UPDATE OF geometry
BEFORE UPDATE OF geometry
ON qwat_od.valve
ON qwat_od.valve
FOR EACH ROW
FOR EACH ROW
WHEN ((st_equals(st_force2d(new.geometry), st_force2d(old.geometry)) IS FALSE))
WHEN ((st_equals(st_force2d(new.geometry), st_force2d(old.geometry)) IS FALSE))
EXECUTE PROCEDURE qwat_od.ft_geometry_alternative_main();
EXECUTE PROCEDURE qwat_od.ft_geometry_alternative_main();
COMMENT ON TRIGGER tr_valve_altgeom_update ON qwat_od.valve IS 'Trigger: handle alternative geometries on update';
COMMENT ON TRIGGER tr_valve_altgeom_update ON qwat_od.valve IS 'Trigger: handle alternative geometries on update';


-- Trigger: tr_valve_infos_insert_trigger on qwat_od.valve
-- Trigger: tr_valve_infos_insert_trigger on qwat_od.valve


-- DROP TRIGGER tr_valve_infos_insert_trigger ON qwat_od.valve;
-- DROP TRIGGER tr_valve_infos_insert_trigger ON qwat_od.valve;


CREATE TRIGGER tr_valve_infos_insert_trigger
CREATE TRIGGER tr_valve_infos_insert_trigger
BEFORE INSERT
BEFORE INSERT
ON qwat_od.valve
ON qwat_od.valve
FOR EACH ROW
FOR EACH ROW
EXECUTE PROCEDURE qwat_od.ft_valve_geom();
EXECUTE PROCEDURE qwat_od.ft_valve_geom();
COMMENT ON TRIGGER tr_valve_infos_insert_trigger ON qwat_od.valve IS 'Trigger: when inserting a valve, assign pipe.';
COMMENT ON TRIGGER tr_valve_infos_insert_trigger ON qwat_od.valve IS 'Trigger: when inserting a valve, assign pipe.';


-- Trigger: tr_valve_infos_update_trigger on qwat_od.valve
-- Trigger: tr_valve_infos_update_trigger on qwat_od.valve


-- DROP TRIGGER tr_valve_infos_update_trigger ON qwat_od.valve;
-- DROP TRIGGER tr_valve_infos_update_trigger ON qwat_od.valve;


CREATE TRIGGER tr_valve_infos_update_trigger
CREATE TRIGGER tr_valve_infos_update_trigger
BEFORE UPDATE
BEFORE UPDATE
ON qwat_od.valve
ON qwat_od.valve
FOR EACH ROW
FOR EACH ROW
WHEN ((NOT st_equals(old.geometry, new.geometry)))
WHEN ((NOT st_equals(old.geometry, new.geometry)))
EXECUTE PROCEDURE qwat_od.ft_valve_geom();
EXECUTE PROCEDURE qwat_od.ft_valve_geom();
COMMENT ON TRIGGER tr_valve_infos_update_trigger ON qwat_od.valve IS 'Trigger: when updating a valve, assign pipe.';
COMMENT ON TRIGGER tr_valve_infos_update_trigger ON qwat_od.valve IS 'Trigger: when updating a valve, assign pipe.';


-- Trigger: tr_valve_schema_visible_insert on qwat_od.valve
-- Trigger: tr_valve_schema_visible_insert on qwat_od.valve


-- DROP TRIGGER tr_valve_schema_visible_insert ON qwat_od.valve;
-- DROP TRIGGER tr_valve_schema_visible_insert ON qwat_od.valve;


CREATE TRIGGER tr_valve_schema_visible_insert
CREATE TRIGGER tr_valve_schema_visible_insert
BEFORE INSERT
BEFORE INSERT
ON qwat_od.valve
ON qwat_od.valve
FOR EACH ROW
FOR EACH ROW
EXECUTE PROCEDURE qwat_od.ft_valve_schema_visible();
EXECUTE PROCEDURE qwat_od.ft_valve_schema_visible();


-- Trigger: tr_valve_schema_visible_update on qwat_od.valve
-- Trigger: tr_valve_schema_visible_update on qwat_od.valve


-- DROP TRIGGER tr_valve_schema_visible_update ON qwat_od.valve;
-- DROP TRIGGER tr_valve_schema_visible_update ON qwat_od.valve;


CREATE TRIGGER tr_valve_schema_visible_update
CREATE TRIGGER tr_valve_schema_visible_update
BEFORE UPDATE OF fk_valve_function
BEFORE UPDATE OF fk_valve_function
ON qwat_od.valve
ON qwat_od.valve
FOR EACH ROW
FOR EACH ROW
EXECUTE PROCEDURE qwat_od.ft_valve_schema_visible();
EXECUTE PROCEDURE qwat_od.ft_valve_schema_visible();


-- Trigger: tr_valve_update_trigger on qwat_od.valve
-- Trigger: tr_valve_update_trigger on qwat_od.valve


-- DROP TRIGGER tr_valve_update_trigger ON qwat_od.valve;
-- DROP TRIGGER tr_valve_update_trigger ON qwat_od.valve;


CREATE TRIGGER tr_valve_update_trigger
CREATE TRIGGER tr_valve_update_trigger
AFTER UPDATE
AFTER UPDATE
ON qwat_od.valve
ON qwat_od.valve
FOR EACH ROW
FOR EACH ROW
EXECUTE PROCEDURE qwat_od.ft_valve_update();
EXECUTE PROCEDURE qwat_od.ft_valve_update();
COMMENT ON TRIGGER tr_valve_update_trigger ON qwat_od.valve IS 'Trigger: when updating a valve, reevaluate old and new pipes.';
COMMENT ON TRIGGER tr_valve_update_trigger ON qwat_od.valve IS 'Trigger: when updating a valve, reevaluate old and new pipes.';


-- Trigger: valve_handle_altitude_insert_trigger on qwat_od.valve
-- Trigger: valve_handle_altitude_insert_trigger on qwat_od.valve


-- DROP TRIGGER valve_handle_altitude_insert_trigger ON qwat_od.valve;
-- DROP TRIGGER valve_handle_altitude_insert_trigger ON qwat_od.valve;


CREATE TRIGGER valve_handle_altitude_insert_trigger
CREATE TRIGGER valve_handle_altitude_insert_trigger
BEFORE INSERT
BEFORE INSERT
ON qwat_od.valve
ON qwat_od.valve
FOR EACH ROW
FOR EACH ROW
EXECUTE PROCEDURE qwat_od.ft_valve_handle_altitude();
EXECUTE PROCEDURE qwat_od.ft_valve_handle_altitude();
COMMENT ON TRIGGER valve_handle_altitude_insert_trigger ON qwat_od.valve IS 'Trigger: when updating, check if altitude or Z value of geometry changed and synchronize them.';
COMMENT ON TRIGGER valve_handle_altitude_insert_trigger ON qwat_od.valve IS 'Trigger: when updating, check if altitude or Z value of geometry changed and synchronize them.';


-- Trigger: valve_handle_altitude_update_trigger on qwat_od.valve
-- Trigger: valve_handle_altitude_update_trigger on qwat_od.valve


-- DROP TRIGGER valve_handle_altitude_update_trigger ON qwat_od.valve;
-- DROP TRIGGER valve_handle_altitude_update_trigger ON qwat_od.valve;


CREATE TRIGGER valve_handle_altitude_update_trigger
CREATE TRIGGER valve_handle_altitude_update_trigger
BEFORE UPDATE OF handle_altitude, handle_geometry
BEFORE UPDATE OF handle_altitude, handle_geometry
ON qwat_od.valve
ON qwat_od.valve
FOR EACH ROW
FOR EACH ROW
WHEN (((new.handle_altitude <> old.handle_altitude) OR (st_z(new.handle_geometry) <> st_z(old.handle_geometry))))
WHEN (((new.handle_altitude <> old.handle_altitude) OR (st_z(new.handle_geometry) <> st_z(old.handle_geometry))))
EXECUTE PROCEDURE qwat_od.ft_valve_handle_altitude();
EXECUTE PROCEDURE qwat_od.ft_valve_handle_altitude();
COMMENT ON TRIGGER valve_handle_altitude_update_trigger ON qwat_od.valve IS 'Trigger: when updating, check if altitude or Z value of geometry changed and synchronize them.';
COMMENT ON TRIGGER valve_handle_altitude_update_trigger ON qwat_od.valve IS 'Trigger: when updating, check if altitude or Z value of geometry changed and synchronize them.';


-- Trigger: valve_main_altitude_insert_trigger on qwat_od.valve
-- Trigger: valve_main_altitude_insert_trigger on qwat_od.valve


-- DROP TRIGGER valve_main_altitude_insert_trigger ON qwat_od.valve;
-- DROP TRIGGER valve_main_altitude_insert_trigger ON qwat_od.valve;


CREATE TRIGGER valve_main_altitude_insert_trigger
CREATE TRIGGER valve_main_altitude_insert_trigger
BEFORE INSERT
BEFORE INSERT
ON qwat_od.valve
ON qwat_od.valve
FOR EACH ROW
FOR EACH ROW
EXECUTE PROCEDURE qwat_od.ft_valve_main_altitude();
EXECUTE PROCEDURE qwat_od.ft_valve_main_altitude();
COMMENT ON TRIGGER valve_main_altitude_insert_trigger ON qwat_od.valve IS 'Trigger: when updating, check if altitude or Z value of geometry changed and synchronize them.';
COMMENT ON TRIGGER valve_main_altitude_insert_trigger ON qwat_od.valve IS 'Trigger: when updating, check if altitude or Z value of geometry changed and synchronize them.';


-- Trigger: valve_main_altitude_update_trigger on qwat_od.valve
-- Trigger: valve_main_altitude_update_trigger on qwat_od.valve


-- DROP TRIGGER valve_main_altitude_update_trigger ON qwat_od.valve;
-- DROP TRIGGER valve_main_altitude_update_trigger ON qwat_od.valve;


CREATE TRIGGER valve_main_altitude_update_trigger
CREATE TRIGGER valve_main_altitude_update_trigger
BEFORE UPDATE OF altitude, geometry
BEFORE UPDATE OF altitude, geometry
ON qwat_od.valve
ON qwat_od.valve
FOR EACH ROW
FOR EACH ROW
WHEN (((new.altitude <> old.altitude) OR (st_z(new.geometry) <> st_z(old.geometry))))
WHEN (((new.altitude <> old.altitude) OR (st_z(new.geometry) <> st_z(old.geometry))))
EXECUTE PROCEDURE qwat_od.ft_valve_main_altitude();
EXECUTE PROCEDURE qwat_od.ft_valve_main_altitude();
COMMENT ON TRIGGER valve_main_altitude_update_trigger ON qwat_od.valve IS 'Trigger: when updating, check if altitude or Z value of geometry changed and synchronize them.';
COMMENT ON TRIGGER valve_main_altitude_update_trigger ON qwat_od.valve IS 'Trigger: when updating, check if altitude or Z value of geometry changed and synchronize them.';




Saved diffs