Mercurial > gemma
changeset 1172:c3955e3db074
Propagate deletion of imports through the track_imports table.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 14 Nov 2018 13:12:41 +0100 |
parents | d61be0d972d8 |
children | 3035ddd3d1a8 |
files | schema/gemma.sql |
diffstat | 1 files changed, 13 insertions(+), 2 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Wed Nov 14 12:54:13 2018 +0100 +++ b/schema/gemma.sql Wed Nov 14 13:12:41 2018 +0100 @@ -533,17 +533,28 @@ CREATE TYPE waterway.log_type AS ENUM ('info', 'warn', 'error'); CREATE TABLE waterway.import_logs ( - import_id int NOT NULL REFERENCES waterway.imports(id), + import_id int NOT NULL REFERENCES waterway.imports(id) ON DELETE CASCADE, time timestamp NOT NULL DEFAULT now(), kind waterway.log_type NOT NULL DEFAULT 'info', msg TEXT NOT NULL ); CREATE TABLE waterway.track_imports ( - import_id int NOT NULL REFERENCES waterway.imports(id), + import_id int NOT NULL REFERENCES waterway.imports(id) ON DELETE CASCADE, relation regclass NOT NULL, key int NOT NULL, UNIQUE (relation, key) ); +CREATE FUNCTION waterway.del_import() RETURNS trigger AS +$$ +BEGIN + EXECUTE format('DELETE FROM %I WHERE id = $1', OLD.relation) USING OLD.key; +END; +$$ +LANGUAGE plpgsql; + +CREATE TRIGGER delete_import AFTER DELETE ON waterway.track_imports + FOR EACH ROW EXECUTE PROCEDURE waterway.del_import(); + COMMIT;