Mercurial > gemma
view schema/gemma.sql @ 5654:358130887c72
Added tag v5.7.1 for changeset 9b3779f649c2
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Tue, 08 Aug 2023 15:07:00 +0200 |
parents | 8797274e2739 |
children | 796196f83678 |
line wrap: on
line source
-- This is Free Software under GNU Affero General Public License v >= 3.0 -- without warranty, see README.md and license for details. -- SPDX-License-Identifier: AGPL-3.0-or-later -- License-Filename: LICENSES/AGPL-3.0.txt -- Copyright (C) 2018, 2019 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Tom Gottfried <tom@intevation.de> -- * Sascha Teichmann <sascha.teichmann@intevation.de> -- * Sascha Wilde <sascha.wilde@intevation.de> BEGIN; -- -- Extensions -- CREATE EXTENSION postgis; -- needed for multi-column GiST indexes with otherwise unsupported types: CREATE EXTENSION btree_gist; -- -- Functions to be used in CHECK constraints -- -- Check if a given string can be used as a FROM item in an SQL statement CREATE OR REPLACE FUNCTION is_valid_from_item(stmt text) RETURNS boolean AS $$ BEGIN EXECUTE format('SELECT * FROM (%s) AS test', stmt); RETURN true; EXCEPTION WHEN OTHERS THEN RAISE NOTICE USING MESSAGE = SQLERRM, ERRCODE = SQLSTATE; RETURN false; END $$ LANGUAGE plpgsql STRICT; -- -- Trigger functions -- -- TODO: will there ever be UPDATEs or can we drop that function due to -- historicisation? CREATE FUNCTION update_date_info() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN NEW.date_info = CURRENT_TIMESTAMP; RETURN NEW; END; $$; -- -- Trigger functions to be used in constraint triggers -- /* The constraint triggers are subject to a possible race condition due to PostgreSQL MVCC: Trigger functions do not see uncommited changes of other transactions running in parallel. Since we serialize imports at application level, the race condition should not happen, though. In case serialization at application level should be removed, database side locking/serialization would have to be applied. */ -- Trigger function to be used as a constraint trigger to enforce uniqueness -- of geometries in the column with its name given as an argument to the -- trigger function. If additional column names are given as trigger arguments, -- the group of given columns is tested for equality, if no operators are -- given. Optionally, a column can be compared with a different operator -- given after the keyword "WITH" in the trigger argument. CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS $$ DECLARE new_geom geometry; tg_arg text; col varchar; opr varchar; filters text; has_equal boolean; BEGIN EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0]) INTO new_geom USING NEW; FOREACH tg_arg IN ARRAY TG_ARGV[1:] LOOP -- Test each additional argument for equality -- or with operator given after keyword "WITH" tg_arg = lower(tg_arg); col = split_part(tg_arg, ' with ', 1); opr = COALESCE(NULLIF(split_part(tg_arg, ' with ', 2), ''), '='); filters = format('%s AND %I %s $2.%2$I', filters, col, opr); END LOOP; EXECUTE format( 'SELECT EXISTS(SELECT 1 FROM %I.%I ' 'WHERE id <> $2.id AND ST_Equals($1, CAST(%I AS geometry))' '%s)', TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_ARGV[0], filters) INTO has_equal USING new_geom, NEW; IF has_equal THEN RAISE EXCEPTION 'new row for relation "%" violates constraint trigger "%"', TG_TABLE_NAME, TG_NAME USING DETAIL = format('Failing row contains geometry in %s', Box2D(new_geom)), ERRCODE = 23505, SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, COLUMN = TG_ARGV[0], CONSTRAINT = TG_NAME; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; -- Trigger function to be used as a constraint trigger to enforce -- existance of a referenced gauge with intersecting validity. The -- columns with the referenced gauge isrs code an the validity are -- given as arguments to the trigger function. CREATE OR REPLACE FUNCTION check_valid_gauge() RETURNS trigger AS $$ DECLARE -- FIXME: I'm using text for the isrs code and cast it on demand. -- If someone is able to get it to work with isrs or isrs_base as -- type, feel free to show me how its done... ;-) [sw] referenced_gauge text; new_validity tstzrange; BEGIN EXECUTE format('SELECT $1.%I', TG_ARGV[0]) INTO referenced_gauge USING NEW; EXECUTE format('SELECT $1.%I', TG_ARGV[1]) INTO new_validity USING NEW; IF EXISTS ( SELECT * FROM waterway.gauges WHERE location = referenced_gauge::isrs AND validity && new_validity ) THEN RETURN NEW; ELSE RAISE EXCEPTION 'new row for relation "%" violates constraint trigger "%"', TG_TABLE_NAME, TG_NAME USING DETAIL = format('No matching gauge %s found.', isrs_AsText(referenced_gauge::isrs)), ERRCODE = 23503, SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, COLUMN = TG_ARGV[0], CONSTRAINT = TG_NAME; END IF; END; $$ LANGUAGE plpgsql; -- The same for objects with a timestamp instead of a validity range. CREATE OR REPLACE FUNCTION check_valid_gauge_ts() RETURNS trigger AS $$ DECLARE -- FIXME: I'm using text for the isrs code and cast it on demand. -- If someone is able to get it to work with isrs or isrs_base as -- type, feel free to show me how its done... ;-) [sw] referenced_gauge text; new_tstz timestamptz; BEGIN EXECUTE format('SELECT $1.%I', TG_ARGV[0]) INTO referenced_gauge USING NEW; EXECUTE format('SELECT $1.%I', TG_ARGV[1]) INTO new_tstz USING NEW; IF EXISTS ( SELECT * FROM waterway.gauges WHERE location = referenced_gauge::isrs AND validity @> new_tstz ) THEN RETURN NEW; ELSE RAISE EXCEPTION 'new row for relation "%" violates constraint trigger "%"', TG_TABLE_NAME, TG_NAME USING DETAIL = format('No matching gauge %s found.', isrs_AsText(referenced_gauge::isrs)), ERRCODE = 23503, SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, COLUMN = TG_ARGV[0], CONSTRAINT = TG_NAME; END IF; END; $$ LANGUAGE plpgsql; -- Trigger function to be used as a constraint trigger to enforce -- existance of a referenced bottleneck with validity at a given time. -- The columns with the referenced bottleneck id and the timestamp are -- given as arguments to the trigger function. CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS $$ DECLARE referenced_bottleneck_id text; new_tstz timestamptz; BEGIN EXECUTE format('SELECT $1.%I', TG_ARGV[0]) INTO referenced_bottleneck_id USING NEW; EXECUTE format('SELECT $1.%I', TG_ARGV[1]) INTO new_tstz USING NEW; IF EXISTS ( SELECT * FROM waterway.bottlenecks WHERE bottleneck_id = referenced_bottleneck_id AND validity @> new_tstz ) THEN RETURN NEW; ELSE RAISE EXCEPTION 'new row for relation "%" violates constraint trigger "%"', TG_TABLE_NAME, TG_NAME USING DETAIL = format('No matching bottleneck %s for %s found.', referenced_bottleneck_id, new_tstz), ERRCODE = 23503, SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, COLUMN = TG_ARGV[0], CONSTRAINT = TG_NAME; END IF; END; $$ LANGUAGE plpgsql; -- Constraint trigger: sounding Results must intersect with the area -- of the bottleneck they belong to. The "xx" at the beginning of the -- name is to ensure, it is fired last after other triggers. CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF NOT st_intersects((SELECT area FROM waterway.bottlenecks WHERE bottleneck_id = NEW.bottleneck_id AND validity @> NEW.date_info::timestamptz), NEW.area) THEN RAISE EXCEPTION 'new row for relation "%" violates constraint trigger "%"', TG_TABLE_NAME, TG_NAME USING DETAIL = 'Failing row area has no intersection with bottleneck.', ERRCODE = 23514, SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, CONSTRAINT = TG_NAME; END IF; RETURN NEW; END; $$; -- -- GEMA meta data -- CREATE TABLE gemma_schema_version ( version int PRIMARY KEY, update_date timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE OR REPLACE FUNCTION get_schema_version() RETURNS int LANGUAGE sql AS $$ SELECT max(version) FROM public.gemma_schema_version; $$; -- -- GEMMA data -- -- -- Look-up tables with data that are static in a running system -- CREATE TABLE language_codes ( language_code varchar PRIMARY KEY ); CREATE TABLE catccls ( catccl smallint PRIMARY KEY -- TODO: Do we need name and/or definition from IENC feature catalogue? -- (see page 328 of edition 2.3) ); INSERT INTO catccls VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11); CREATE TABLE dirimps ( dirimp smallint PRIMARY KEY -- TODO: Do we need name and/or definition from IENC feature catalogue? -- (see page 381 of edition 2.3) ); INSERT INTO dirimps VALUES (1), (2), (3), (4), (5); CREATE TABLE depth_references ( depth_reference varchar(4) PRIMARY KEY -- See col. AB and AI RIS-Index Encoding Guide -- XXX: We need a way to distinguish between geodetic (eg. col. AP -- RIS-Index) and other references (e.g. col. AB and AI): -- _ multi-column FK with a boolean column (geodetic/non-geodetic; -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side. -- _ Do not mixup things with different meanings in one table at all -- (which would mean a model differing a bit from RIS-Index ideas) ); CREATE TABLE catdis ( catdis smallint PRIMARY KEY -- TODO: Do we need name and/or definition from IENC feature catalogue? -- (see page 171 of edition 2.3) ); INSERT INTO catdis VALUES (1), (2), (3), (4); CREATE TABLE position_codes ( position_code char(2) PRIMARY KEY -- Use smallint because of fairway availability provided on daily basis? -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx, -- sheet "Position_code" or RIS-Index encoding guide? -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here. -- Clarify! -- TODO: Do we need an attribute "meaning" or so? ); CREATE TABLE levels_of_service ( level_of_service smallint PRIMARY KEY, name varchar(4) ); INSERT INTO levels_of_service ( level_of_service, name ) VALUES (1, 'LOS1'), (2, 'LOS2'), (3, 'LOS3'); CREATE TABLE riverbed_materials ( material varchar PRIMARY KEY -- XXX: Should this table contain choices from DRC 2.2.3 or -- from IENC Encoding Guide M.4.3, attribute NATSUR? ); CREATE TABLE survey_types ( survey_type varchar PRIMARY KEY ); INSERT INTO survey_types (survey_type) VALUES ('single'), ('multi'), ('marking'); CREATE TABLE coverage_types ( coverage_type varchar PRIMARY KEY ); CREATE TABLE limiting_factors ( limiting_factor varchar PRIMARY KEY ); CREATE TABLE measure_types ( measure_type varchar PRIMARY KEY ); CREATE TYPE template_types AS ENUM ( 'map', 'diagram', 'report' ); -- Namespace not to be accessed directly by any user CREATE SCHEMA internal -- Profile data are only accessible via the view users.list_users. CREATE TABLE user_profiles ( username varchar PRIMARY KEY CHECK(octet_length(username) <= 63) CHECK(to_regrole(quote_ident(username)) IS NOT NULL), -- keep username length compatible with role identifier country char(2) NOT NULL REFERENCES countries, map_extent box2d NOT NULL, email_address varchar NOT NULL, report_reciever boolean NOT NULL DEFAULT false, active boolean NOT NULL DEFAULT true ) ; -- Namespace to be accessed by sys_admin only CREATE SCHEMA sys_admin CREATE TABLE system_config ( config_key varchar PRIMARY KEY, config_val varchar ) CREATE TABLE password_reset_requests ( hash varchar(32) PRIMARY KEY, issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, username varchar NOT NULL UNIQUE REFERENCES internal.user_profiles(username) ON DELETE CASCADE ON UPDATE CASCADE ) -- Tables with geo data to be published with GeoServer. CREATE TABLE external_services ( local_name varchar PRIMARY KEY, remote_url varchar NOT NULL, is_wfs boolean NOT NULL DEFAULT TRUE ) CREATE TABLE published_services ( schema varchar CHECK(to_regnamespace(schema) IS NOT NULL), name varchar, PRIMARY KEY (schema, name), -- SQL statement used for an SQL view in GeoServer: view_def text CHECK (is_valid_from_item(view_def)), -- Column in output of SQL statement to be used as primary key: key_column varchar, -- SRID to be used with SQL view: srid int REFERENCES spatial_ref_sys, -- SLD style document or ZIP blob: style bytea, as_wms boolean NOT NULL DEFAULT TRUE, as_wfs boolean NOT NULL DEFAULT TRUE, wmst_attribute varchar DEFAULT NULL, wmst_end_attribute varchar DEFAULT NULL, -- Either give a valid relation or a SQL statement: CHECK (to_regclass(schema || '.' || name) IS NOT NULL OR view_def IS NOT NULL) ) CREATE TABLE layer_groups ( name varchar PRIMARY KEY ) CREATE TABLE grouped_layers ( group_name varchar REFERENCES layer_groups ON DELETE CASCADE, schema varchar, name varchar, ord int NOT NULL DEFAULT 0, PRIMARY KEY (group_name, schema, name), UNIQUE (group_name, schema, name, ord), FOREIGN KEY(schema, name) REFERENCES published_services ) -- Table to store scripts which updates aggregated data. CREATE TABLE stats_updates ( name varchar PRIMARY key, script TEXT NULL ) ; -- Namespace for user management related data CREATE SCHEMA users CREATE TABLE stretches ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, name varchar NOT NULL, stretch isrsrange NOT NULL, area geography(MULTIPOLYGON, 4326) NOT NULL CHECK(ST_IsValid(CAST(area AS geometry))), objnam varchar NOT NULL, nobjnam varchar, date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, source_organization varchar NOT NULL, staging_done boolean NOT NULL DEFAULT false, UNIQUE(name, staging_done) ) CREATE TRIGGER stretches_date_info BEFORE UPDATE ON stretches FOR EACH ROW EXECUTE PROCEDURE update_date_info() CREATE TABLE stretch_countries ( stretch_id int NOT NULL REFERENCES stretches(id) ON DELETE CASCADE, country char(2) NOT NULL REFERENCES countries, PRIMARY KEY(stretch_id, country) ) CREATE TABLE templates ( template_name varchar NOT NULL, template_type template_types NOT NULL DEFAULT 'map'::template_types, country char(2) REFERENCES countries, template_data bytea NOT NULL, date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE (template_name, template_type, country) ) CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates FOR EACH ROW EXECUTE PROCEDURE update_date_info() CREATE VIEW list_users WITH (security_barrier) AS SELECT r.rolname, p.username, CAST('' AS varchar) AS pw, p.country, p.map_extent, p.email_address, p.report_reciever, p.active FROM internal.user_profiles p JOIN pg_roles u ON p.username = u.rolname JOIN pg_auth_members a ON u.oid = a.member JOIN pg_roles r ON a.roleid = r.oid WHERE p.username = current_user OR pg_has_role('waterway_admin', 'MEMBER') AND p.country = ( SELECT country FROM internal.user_profiles WHERE username = current_user) AND r.rolname <> 'sys_admin' OR pg_has_role('sys_admin', 'MEMBER') ; -- -- Functions to be used in DEFAULT expresions -- -- Return current_user's country code CREATE FUNCTION users.user_country(user_name name DEFAULT current_user) RETURNS internal.user_profiles.country%TYPE AS $$ SELECT country FROM users.list_users WHERE username = user_name $$ LANGUAGE SQL STABLE PARALLEL SAFE; -- Namespace for waterway data that can change in a running system CREATE SCHEMA waterway -- Eventually obsolete. -- See https://roundup-intern.intevation.de/gemma/issue5 -- CREATE TABLE rwdrs ( -- tretch isrsrange PRIMARY KEY, -- -- https://www.postgresql.org/docs/10/static/sql-createindex.html: -- -- Only B-tree supports UNIQUE indexes, but we need the GIST index -- -- below anyhow. -- -- Is it a good idea to build B-tree indexes on relatively large -- -- composites of string values or should we use inter PKs? -- -- => In case the index is used and cache space becomes a limiting -- -- factor, this might be an issue. -- rwdr double precision NOT NULL, -- EXCLUDE USING GIST (stretch WITH &&) --) CREATE TABLE waterway_area ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, area geography(POLYGON, 4326) NOT NULL CHECK(ST_IsValid(CAST(area AS geometry))), catccl smallint REFERENCES catccls, dirimp smallint REFERENCES dirimps ) CREATE CONSTRAINT TRIGGER waterway_area_area_unique AFTER INSERT OR UPDATE OF area ON waterway_area FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('area') CREATE TABLE gauges ( location isrs CHECK( (location).orc SIMILAR TO 'G[[:digit:]]{4}' AND CAST(substring((location).orc from 2 for 4) AS int) < 2048), objname varchar NOT NULL, geom geography(POINT, 4326) NOT NULL, applicability_from_km int8, applicability_to_km int8, validity tstzrange NOT NULL CHECK (NOT isempty(validity)), zero_point double precision NOT NULL, geodref varchar, date_info timestamp with time zone NOT NULL, source_organization varchar, lastupdate timestamp with time zone NOT NULL, -- entry removed from external data source (RIS-Index)/historicised: erased boolean NOT NULL DEFAULT false, PRIMARY KEY (location, validity), EXCLUDE USING GiST (isrs_astext(location) WITH =, validity WITH &&) DEFERRABLE INITIALLY DEFERRED ) -- Allow only one non-erased entry per location CREATE UNIQUE INDEX gauges_erased_unique_constraint ON gauges (location) WHERE NOT erased CREATE TABLE gauges_reference_water_levels ( location isrs NOT NULL, validity tstzrange NOT NULL, FOREIGN KEY (location, validity) REFERENCES gauges ON UPDATE CASCADE, -- Omit foreign key constraint to be able to store not NtS-compliant -- names, too: depth_reference varchar NOT NULL, -- REFERENCES depth_references, PRIMARY KEY (location, validity, depth_reference), value int NOT NULL ) CREATE TABLE gauge_measurements ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, location isrs NOT NULL, measure_date timestamp with time zone NOT NULL, country_code char(2) NOT NULL REFERENCES countries, sender varchar NOT NULL, -- "from" element from NtS response language_code varchar NOT NULL REFERENCES language_codes, date_issue timestamp with time zone NOT NULL, reference_code varchar(4) NOT NULL REFERENCES depth_references, water_level double precision NOT NULL, date_info timestamp with time zone NOT NULL, source_organization varchar NOT NULL, -- "originator" from NtS response staging_done boolean NOT NULL DEFAULT false, UNIQUE (measure_date, location, staging_done) ) CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge AFTER INSERT OR UPDATE OF location ON gauge_measurements FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date') CREATE TABLE gauge_predictions ( location isrs NOT NULL, measure_date timestamp with time zone NOT NULL, country_code char(2) NOT NULL REFERENCES countries, sender varchar NOT NULL, -- "from" element from NtS response language_code varchar NOT NULL REFERENCES language_codes, date_issue timestamp with time zone NOT NULL, reference_code varchar(4) NOT NULL REFERENCES depth_references, water_level double precision NOT NULL, conf_interval numrange CHECK (conf_interval @> CAST(water_level AS numeric)), date_info timestamp with time zone NOT NULL, source_organization varchar NOT NULL, -- "originator" from NtS response PRIMARY KEY (measure_date, location, date_issue) ) CREATE CONSTRAINT TRIGGER waterway_gauge_predictions_reference_gauge AFTER INSERT OR UPDATE OF location ON gauge_predictions FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date') CREATE TABLE waterway_axis ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, wtwaxs geography(MULTILINESTRING, 4326) NOT NULL CHECK(ST_IsSimple(CAST(wtwaxs AS geometry))), -- TODO: Do we need to check data set quality (DRC 2.1.6)? objnam varchar NOT NULL, nobjnam varchar, validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL) CHECK (NOT isempty(validity)), -- Last time an import job found this entry in a data source: last_found timestamp with time zone NOT NULL DEFAULT current_timestamp ) CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique AFTER INSERT OR UPDATE OF wtwaxs, validity ON waterway_axis DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('wtwaxs', 'validity WITH &&') CREATE INDEX waterway_axis_validity ON waterway_axis USING GiST (validity) -- This table allows linkage between 1D ISRS location codes and 2D space -- e.g. for cutting bottleneck area out of waterway area based on virtual -- distance marks along waterway axis (see SUC7). CREATE TABLE distance_marks_virtual ( location_code isrs PRIMARY KEY, geom geography(POINT, 4326) NOT NULL, related_enc varchar(12) NOT NULL, wwname varchar ) CREATE TABLE distance_marks ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, country char(2) REFERENCES countries, hectom int, geom geography(POINT, 4326) NOT NULL, -- include location in primary key, because we have no fairway code: catdis smallint REFERENCES catdis, position_code char(2) REFERENCES position_codes, related_enc varchar(12) ) -- Like stretches without the countries CREATE TABLE sections ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, name varchar NOT NULL, section isrsrange NOT NULL, area geography(MULTIPOLYGON, 4326) NOT NULL CHECK(ST_IsValid(CAST(area AS geometry))), objnam varchar NOT NULL, nobjnam varchar, country char(2) NOT NULL REFERENCES countries DEFAULT users.user_country(), date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, source_organization varchar NOT NULL, staging_done boolean NOT NULL DEFAULT false, -- Disallow the same name for different countries EXCLUDE USING GiST (name WITH =, country WITH <>), -- Allow the same name one time in and outside staging area, each UNIQUE(name, staging_done) ) CREATE TRIGGER sections_date_info BEFORE UPDATE ON sections FOR EACH ROW EXECUTE PROCEDURE update_date_info() CREATE TABLE waterway_profiles ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, location isrs NOT NULL REFERENCES distance_marks_virtual, geom geography(linestring, 4326), validity tstzrange, lnwl double precision, mwl double precision, hnwl double precision, fe30 double precision, fe100 double precision, -- XXX: further normalise using reference_water_levels? CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL OR validity IS NOT NULL), date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, source_organization varchar NOT NULL, staging_done boolean NOT NULL DEFAULT false, EXCLUDE USING GIST ( isrs_asText(location) WITH =, validity WITH &&, CAST(staging_done AS int) WITH =) ) CREATE TRIGGER waterway_profiles_date_info BEFORE UPDATE ON waterway_profiles FOR EACH ROW EXECUTE PROCEDURE update_date_info() CREATE TABLE fairway_dimensions ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, area geography(MULTIPOLYGON, 4326) NOT NULL CHECK(ST_IsValid(CAST(area AS geometry)) AND NOT ST_IsEmpty(CAST(area AS geometry))), level_of_service smallint NOT NULL REFERENCES levels_of_service, min_width smallint NOT NULL, max_width smallint NOT NULL, min_depth smallint NOT NULL, date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, source_organization varchar NOT NULL, staging_done boolean NOT NULL DEFAULT false, validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL) CHECK (NOT isempty(validity)) ) CREATE TRIGGER fairway_dimensions_date_info BEFORE UPDATE ON fairway_dimensions FOR EACH ROW EXECUTE PROCEDURE update_date_info() CREATE CONSTRAINT TRIGGER fairway_dimensions_area_unique AFTER INSERT OR UPDATE OF area, validity, staging_done ON fairway_dimensions FOR EACH ROW EXECUTE FUNCTION prevent_st_equals( 'area', 'validity WITH &&', 'staging_done') CREATE INDEX fairway_dimensions_validity ON fairway_dimensions USING GiST (validity) -- -- Bottlenecks -- CREATE TABLE bottlenecks ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, bottleneck_id varchar NOT NULL, validity tstzrange NOT NULL CHECK (NOT isempty(validity)), gauge_location isrs NOT NULL, objnam varchar, nobjnm varchar, stretch isrsrange NOT NULL, area geography(MULTIPOLYGON, 4326) NOT NULL CHECK(ST_IsValid(CAST(area AS geometry))), rb char(2) REFERENCES countries, -- from rb_lb in interface lb char(2) REFERENCES countries, -- from rb_lb in interface responsible_country char(2) NOT NULL REFERENCES countries, revisiting_time smallint, limiting varchar NOT NULL REFERENCES limiting_factors, -- surtyp varchar NOT NULL REFERENCES survey_types, -- XXX: Also an attribut of sounding result? -- coverage varchar REFERENCES coverage_types, -- XXX: Also an attribut of sounding result? -- CHECK allowed combinations of surtyp and coverage or -- different model approach? -- depth_reference char(3) NOT NULL REFERENCES depth_references, -- XXX: Also an attribut of sounding result? date_info timestamp with time zone NOT NULL, source_organization varchar NOT NULL, staging_done boolean NOT NULL DEFAULT false, UNIQUE (bottleneck_id, validity, staging_done), EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&, CAST(staging_done AS int) WITH =) DEFERRABLE INITIALLY DEFERRED ) CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge AFTER INSERT OR UPDATE OF gauge_location ON bottlenecks FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity') CREATE TABLE bottlenecks_riverbed_materials ( bottleneck_id int NOT NULL REFERENCES bottlenecks(id) ON DELETE CASCADE, riverbed varchar NOT NULL REFERENCES riverbed_materials, -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3 PRIMARY KEY (bottleneck_id, riverbed) ) CREATE TABLE sounding_results ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, bottleneck_id varchar NOT NULL, date_info date NOT NULL, UNIQUE (bottleneck_id, date_info), area geography(POLYGON, 4326) NOT NULL CHECK(ST_IsValid(CAST(area AS geometry))), surtyp varchar REFERENCES survey_types, coverage varchar REFERENCES coverage_types, depth_reference varchar NOT NULL, -- REFERENCES depth_references, zpg_exception bool NOT NULL DEFAULT FALSE, -- Found no LDC in ZPG case. mesh_checksum varchar, mesh_index bytea, staging_done boolean NOT NULL DEFAULT false ) CREATE CONSTRAINT TRIGGER a_sounding_results_reference_bottleneck AFTER INSERT OR UPDATE OF bottleneck_id ON sounding_results FOR EACH ROW EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info') CREATE CONSTRAINT TRIGGER b_sounding_results_in_bn_area AFTER INSERT OR UPDATE ON sounding_results FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area() CREATE TABLE sounding_results_iso_areas ( sounding_result_id int NOT NULL REFERENCES sounding_results ON DELETE CASCADE, height numeric NOT NULL, areas geography(MULTIPOLYGON, 4326) NOT NULL, -- TODO: generate valid simple features and add constraint: -- CHECK(ST_IsSimple(CAST(areas AS geometry))), PRIMARY KEY (sounding_result_id, height) ) CREATE TABLE sounding_results_marking_points ( sounding_result_id int NOT NULL REFERENCES sounding_results ON DELETE CASCADE, height numeric NOT NULL, -- XXX: GeoServer does not like geography(MULTIPOINTZ) -- We need to track this down. Maybe with -- GeoServer upstream. points geography(MULTIPOINTZ, 4326) NOT NULL, PRIMARY KEY (sounding_result_id, height) ) -- -- Fairway availability -- CREATE TABLE fairway_availability ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, position varchar, bottleneck_id varchar NOT NULL, surdat date NOT NULL, UNIQUE (bottleneck_id, surdat), -- additional_data xml -- Currently not relevant for GEMMA critical boolean, date_info timestamp with time zone NOT NULL, source_organization varchar NOT NULL ) -- FIXME: From the DRC it is unclear what the exact semantics of -- surdat and Date_Info ar unclear. Currently we assume that -- (fk_bn_fid,surdat) has to be unique, but that might be false. -- Anyway, I will date_info here to check for an matching -- reference gauge at the bottleneck. The reason for this -- decision is purely practical (and might be semantically -- disputable: the bottleneck data in the demo system is not old -- enough to cover rthe surdat times... CREATE CONSTRAINT TRIGGER fairway_availability_referenced_bottleneck AFTER INSERT OR UPDATE OF bottleneck_id ON fairway_availability FOR EACH ROW EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info') CREATE TABLE fa_reference_values ( fairway_availability_id int NOT NULL REFERENCES fairway_availability, level_of_service smallint NOT NULL REFERENCES levels_of_service, PRIMARY KEY (fairway_availability_id, level_of_service), fairway_depth smallint, fairway_width smallint, fairway_radius int, CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL), shallowest_spot geography(POINT, 4326) ) CREATE TABLE bottleneck_pdfs ( fairway_availability_id int NOT NULL REFERENCES fairway_availability, profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow? PRIMARY KEY (fairway_availability_id, profile_pdf_url), pdf_generation_date timestamp with time zone NOT NULL, source_organization varchar NOT NULL ) CREATE TABLE effective_fairway_availability ( fairway_availability_id int NOT NULL REFERENCES fairway_availability, measure_date timestamp with time zone NOT NULL, level_of_service smallint NOT NULL REFERENCES levels_of_service, PRIMARY KEY (fairway_availability_id, measure_date, level_of_service, measure_type), available_depth_value smallint, available_width_value smallint, water_level_value smallint, CHECK(COALESCE(available_depth_value, available_width_value, water_level_value) IS NOT NULL), measure_type varchar NOT NULL REFERENCES measure_types, source_organization varchar NOT NULL, forecast_generation_time timestamp with time zone, CHECK(measure_type <> 'forecasted' OR forecast_generation_time IS NOT NULL), value_lifetime timestamp with time zone, CHECK(measure_type = 'minimum guaranteed' OR value_lifetime IS NOT NULL) ) -- Attributes common to all fairway marks CREATE TABLE fairway_marks ( validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL) CHECK (NOT isempty(validity)), -- Last time an import job found this entry in a data source: last_found timestamp with time zone NOT NULL DEFAULT current_timestamp, geom geography(POINT, 4326) NOT NULL, -- Attributes according to IENC Feature Catalogue: datsta varchar, datend varchar, persta varchar, perend varchar, objnam varchar, nobjnm varchar, inform varchar, ninfom varchar, scamin int, picrep varchar, txtdsc varchar, sordat varchar, sorind varchar ) -- Additional attributes for IENC features BCNLAT/bcnlat CREATE TABLE fairway_marks_bcnlat ( colour varchar, colpat varchar, condtn int, bcnshp int, catlam int ) INHERITS (fairway_marks) CREATE TABLE fairway_marks_bcnlat_hydro ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY ) INHERITS (fairway_marks_bcnlat) -- Prevent identical entries using composite type comparison -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_bcnlat_hydro_distinct_rows ON fairway_marks_bcnlat_hydro ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, colour, colpat, condtn, bcnshp, catlam, 0 ) AS fairway_marks_bcnlat_hydro) )) CREATE INDEX fairway_marks_bcnlat_hydro_validity ON fairway_marks_bcnlat_hydro USING GiST (validity) CREATE TABLE fairway_marks_bcnlat_ienc ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY ) INHERITS (fairway_marks_bcnlat) -- Prevent identical entries using composite type comparison -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_bcnlat_ienc_distinct_rows ON fairway_marks_bcnlat_ienc ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, colour, colpat, condtn, bcnshp, catlam, 0 ) AS fairway_marks_bcnlat_ienc) )) CREATE INDEX fairway_marks_bcnlat_ienc_validity ON fairway_marks_bcnlat_ienc USING GiST (validity) CREATE TABLE fairway_marks_bcnlat_dirimps ( fm_bcnlat_id int REFERENCES fairway_marks_bcnlat_ienc, dirimp smallint REFERENCES dirimps, PRIMARY KEY (fm_bcnlat_id, dirimp) ) -- Additional attributes for IENC feature BOYCAR CREATE TABLE fairway_marks_boycar ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, colour varchar, colpat varchar, conrad int, marsys int, boyshp int, catcam int ) INHERITS (fairway_marks) -- Prevent identical entries using composite type comparison -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_boycar_distinct_rows ON fairway_marks_boycar ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, colour, colpat, conrad, marsys, boyshp, catcam ) AS fairway_marks_boycar) )) CREATE INDEX fairway_marks_boycar_validity ON fairway_marks_boycar USING GiST (validity) -- Additional attributes for IENC feature BOYLAT CREATE TABLE fairway_marks_boylat ( colour varchar, colpat varchar, conrad int, marsys int, boyshp int, catlam int ) INHERITS (fairway_marks) CREATE TABLE fairway_marks_boylat_hydro ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY ) INHERITS (fairway_marks_boylat) -- Prevent identical entries using composite type comparison -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_boylat_hydro_distinct_rows ON fairway_marks_boylat_hydro ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, colour, colpat, conrad, marsys, boyshp, catlam, 0 ) AS fairway_marks_boylat_hydro) )) CREATE INDEX fairway_marks_boylat_hydro_validity ON fairway_marks_boylat_hydro USING GiST (validity) CREATE TABLE fairway_marks_boylat_ienc ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY ) INHERITS (fairway_marks_boylat) -- Prevent identical entries using composite type comparison -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_boylat_ienc_distinct_rows ON fairway_marks_boylat_ienc ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, colour, colpat, conrad, marsys, boyshp, catlam, 0 ) AS fairway_marks_boylat_ienc) )) CREATE INDEX fairway_marks_boylat_ienc_validity ON fairway_marks_boylat_ienc USING GiST (validity) -- Additional attributes for IENC feature BOYSAW CREATE TABLE fairway_marks_boysaw ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, colour varchar, colpat varchar, conrad int, marsys int, boyshp int ) INHERITS (fairway_marks) -- Prevent identical entries using composite type comparison -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_boysaw_distinct_rows ON fairway_marks_boysaw ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, colour, colpat, conrad, marsys, boyshp ) AS fairway_marks_boysaw) )) CREATE INDEX fairway_marks_boysaw_validity ON fairway_marks_boysaw USING GiST (validity) -- Additional attributes for IENC feature BOYSPP CREATE TABLE fairway_marks_boyspp ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, colour varchar, colpat varchar, conrad int, marsys int, boyshp int, catspm varchar ) INHERITS (fairway_marks) -- Prevent identical entries using composite type comparison -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_boyspp_distinct_rows ON fairway_marks_boyspp ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, colour, colpat, conrad, marsys, boyshp, catspm ) AS fairway_marks_boyspp) )) CREATE INDEX fairway_marks_boyspp_validity ON fairway_marks_boyspp USING GiST (validity) -- Additional attributes for IENC features DAYMAR/daymar CREATE TABLE fairway_marks_daymar ( colour varchar, colpat varchar, condtn int, topshp int ) INHERITS (fairway_marks) CREATE TABLE fairway_marks_daymar_hydro ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY ) INHERITS (fairway_marks_daymar) -- Prevent identical entries using composite type comparison -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_daymar_hydro_distinct_rows ON fairway_marks_daymar_hydro ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, colour, colpat, condtn, topshp, 0 ) AS fairway_marks_daymar_hydro) )) CREATE INDEX fairway_marks_daymar_hydro_validity ON fairway_marks_daymar_hydro USING GiST (validity) CREATE TABLE fairway_marks_daymar_ienc ( orient double precision, id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY ) INHERITS (fairway_marks_daymar) -- Prevent identical entries using composite type comparison -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_daymar_ienc_distinct_rows ON fairway_marks_daymar_ienc ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, colour, colpat, condtn, topshp, orient, 0 ) AS fairway_marks_daymar_ienc) )) CREATE INDEX fairway_marks_daymar_ienc_validity ON fairway_marks_daymar_ienc USING GiST (validity) CREATE TABLE fairway_marks_daymar_dirimps ( fm_daymar_id int REFERENCES fairway_marks_daymar_ienc, dirimp smallint REFERENCES dirimps, PRIMARY KEY (fm_daymar_id, dirimp) ) -- Additional attributes for IENC feature LIGHTS CREATE TABLE fairway_marks_lights ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, colour varchar, condtn int, orient double precision, catlit varchar, exclit int, litchr int, litvis varchar, mltylt int, sectr1 double precision, sectr2 double precision, siggrp varchar, sigper double precision, sigseq varchar, status varchar ) INHERITS (fairway_marks) -- Prevent identical entries using composite type comparison -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_lights_distinct_rows ON fairway_marks_lights ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, colour, condtn, orient, catlit, exclit, litchr, litvis, mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status ) AS fairway_marks_lights) )) CREATE INDEX fairway_marks_lights_validity ON fairway_marks_lights USING GiST (validity) -- Additional attributes for IENC feature RTPBCN CREATE TABLE fairway_marks_rtpbcn ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, condtn int, siggrp varchar, catrtb int, radwal varchar ) INHERITS (fairway_marks) -- Prevent identical entries using composite type comparison -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_rtpbcn_distinct_rows ON fairway_marks_rtpbcn ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, condtn, siggrp, catrtb, radwal ) AS fairway_marks_rtpbcn) )) CREATE INDEX fairway_marks_rtpbcn_validity ON fairway_marks_rtpbcn USING GiST (validity) -- Additional attributes for IENC feature TOPMAR CREATE TABLE fairway_marks_topmar ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, colour varchar, colpat varchar, condtn int, topshp int ) INHERITS (fairway_marks) -- Prevent identical entries using composite type comparison -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_topmar_distinct_rows ON fairway_marks_topmar ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, colour, colpat, condtn, topshp ) AS fairway_marks_topmar) )) CREATE INDEX fairway_marks_topmar_validity ON fairway_marks_topmar USING GiST (validity) -- Additional attributes for IENC feature NOTMRK CREATE TABLE fairway_marks_notmrk ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, condtn int, marsys int, orient double precision, status varchar, addmrk varchar, catnmk int, disipd double precision, disipu double precision, disbk1 double precision, disbk2 double precision, fnctnm int, bnkwtw int ) INHERITS (fairway_marks) -- Prevent identical entries using composite type comparison -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_notmrk_distinct_rows ON fairway_marks_notmrk ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, condtn, marsys, orient, status, addmrk, catnmk, disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw ) AS fairway_marks_notmrk) )) CREATE INDEX fairway_marks_notmrk_validity ON fairway_marks_notmrk USING GiST (validity) CREATE TABLE fairway_marks_notmrk_dirimps ( fm_notmrk_id int REFERENCES fairway_marks_notmrk, dirimp smallint REFERENCES dirimps, PRIMARY KEY (fm_notmrk_id, dirimp) ) ; -- -- Import queue and respective logging -- CREATE TYPE import_state AS ENUM ( 'queued', 'running', 'failed', 'unchanged', 'pending', 'accepted', 'declined', 'reviewed' ); CREATE TYPE log_type AS ENUM ('info', 'warn', 'error'); -- Namespace for import queue and respective logging CREATE SCHEMA import CREATE TABLE import_configuration ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, username varchar NOT NULL REFERENCES internal.user_profiles(username) ON DELETE CASCADE ON UPDATE CASCADE, kind varchar NOT NULL ) CREATE TABLE import_configuration_attributes ( import_configuration_id int NOT NULL REFERENCES import_configuration(id) ON DELETE CASCADE ON UPDATE CASCADE, k VARCHAR NOT NULL, v TEXT NOT NULL, UNIQUE (import_configuration_id, k) ) CREATE TABLE imports ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, state import_state NOT NULL DEFAULT 'queued', kind varchar NOT NULL, enqueued timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, changed timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, due timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, retry_wait interval CHECK(retry_wait IS NULL OR retry_wait >= interval '0 microseconds'), trys_left int, -- if NULL and retry_wait NOT NULL, endless username varchar NOT NULL REFERENCES internal.user_profiles(username) ON DELETE CASCADE ON UPDATE CASCADE, signer varchar REFERENCES internal.user_profiles(username) ON DELETE SET NULL ON UPDATE CASCADE, send_email boolean NOT NULL DEFAULT false, data TEXT, summary TEXT ) -- Mainly for listing imports in clients: CREATE INDEX enqueued_idx ON imports(enqueued) -- For fast retrieval of queued imports by the import queue in backend: CREATE INDEX state_idx ON imports(state) CREATE TABLE import_logs ( import_id int NOT NULL REFERENCES imports(id) ON DELETE CASCADE, time timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, kind log_type NOT NULL DEFAULT 'info', msg TEXT NOT NULL ) CREATE INDEX import_logs_import_id ON import.import_logs (import_id) CREATE TABLE track_imports ( import_id int NOT NULL REFERENCES imports(id) ON DELETE CASCADE, deletion bool NOT NULL DEFAULT false, relation regclass NOT NULL, key int NOT NULL, UNIQUE (relation, key) ) ; CREATE FUNCTION import.del_import(imp_id int) RETURNS void AS $$ DECLARE tmp RECORD; BEGIN FOR tmp IN SELECT relation, array_agg(key) AS keys FROM import.track_imports WHERE import_id = imp_id AND NOT deletion GROUP BY relation LOOP EXECUTE format('DELETE FROM %s WHERE id = ANY($1)', tmp.relation) USING tmp.keys; END LOOP; END; $$ LANGUAGE plpgsql; CREATE SCHEMA caching CREATE TABLE sounding_differences ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, minuend int NOT NULL REFERENCES waterway.sounding_results(id) ON DELETE CASCADE, subtrahend int NOT NULL REFERENCES waterway.sounding_results(id) ON DELETE CASCADE, UNIQUE (minuend, subtrahend) ) CREATE TABLE sounding_differences_iso_areas ( sounding_differences_id int NOT NULL REFERENCES sounding_differences(id) ON DELETE CASCADE, height numeric NOT NULL, areas geography(MULTIPOLYGON, 4326) NOT NULL, PRIMARY KEY (sounding_differences_id, height) ) ; COMMIT;