-- Copyright (C) 2009 Andrejs Sisojevs <andrejs.sisojevs@nextmail.ru>
-- 
-- All rights reserved.
-- 
-- For license and copyright information, see the file COPYRIGHT

--------------------------------------------------------------------------
--------------------------------------------------------------------------

\c pcltcatalogs user_pcltcatalogs_owner

SET search_path TO sch_pcltcatalogs, public; -- sets only for current session

--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS by_catalog_used_config (integer);
DROP FUNCTION IF EXISTS in_catalog_localized_tpls_with_their_sdls(integer); 
DROP TYPE     IF EXISTS type__catalog_ltpl_row;

\echo
\echo **** DROPPING TRIGGERS. 
\echo **** psql may throw a dosen of complains here, like "ERROR:  relation "tpls_catalogs" does not exist", - just ignore that!..
\echo
DROP TRIGGER IF EXISTS tri_tpls_catalogs_onupdate ON tpls_catalogs;
DROP TRIGGER IF EXISTS tri_tplscat_inner_configs_onupdate ON tplscat_inner_configs;
DROP TRIGGER IF EXISTS tri_tpls_collections_onupdate ON tpls_collections;
DROP TRIGGER IF EXISTS tri_tpls_onupdate ON tpls;
DROP TRIGGER IF EXISTS tri_tpls_oninsert ON tpls;
DROP TRIGGER IF EXISTS tri_tpls_localized_onupdate ON tpls_localized;
DROP TRIGGER IF EXISTS tri_tpls_inclusions_in_collections_onupdate ON tpls_inclusions_in_collections;
DROP TRIGGER IF EXISTS tri_tpls_representations_sdls_onupdate ON tpls_representations_sdls;
DROP TRIGGER IF EXISTS tri_collections_inclusions_in_catalogs_onupdate ON collections_inclusions_in_catalogs;
DROP TRIGGER IF EXISTS tri_collections_dependencies_onupdate ON collections_dependencies;

DROP SEQUENCE IF EXISTS tpls_catalogs_ids_seq;
DROP SEQUENCE IF EXISTS tpls_detalization_policies_ids_seq;
DROP SEQUENCE IF EXISTS tpls_collections_ids_seq;
DROP SEQUENCE IF EXISTS tplscat_inner_configs_ids_seq;

--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------

CREATE TABLE nameables ( -- an ancestor table
         name        varchar NOT NULL DEFAULT '<name not given>'
       , description varchar     NULL 
       , comments    varchar     NULL
) TABLESPACE tabsp_pcltcatalogs;

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE nameables TO user_pcltCatalogs_data_admin;
GRANT SELECT                         ON TABLE nameables TO user_pcltCatalogs_data_reader;

------------------------------------------------------------------------------

CREATE TABLE language_iso639_3 ( -- an ancestor table
	lng varchar(3) PRIMARY KEY
) INHERITS (nameables) TABLESPACE tabsp_pcltcatalogs;

INSERT INTO language_iso639_3 (lng, name) VALUES ('rus', 'Russian'), ('eng', 'English');

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE language_iso639_3 TO user_pcltCatalogs_data_admin;
GRANT SELECT                         ON TABLE language_iso639_3 TO user_pcltCatalogs_data_reader;

------------------------------------------------------------------------------

CREATE TABLE lang_localizables ( -- an ancestor table
	lng varchar(3) NOT NULL REFERENCES language_iso639_3 (lng) ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE tabsp_pcltcatalogs;

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE lang_localizables TO user_pcltCatalogs_data_admin;
GRANT SELECT                         ON TABLE lang_localizables TO user_pcltCatalogs_data_reader;

------------------------------------------------------------------------------

CREATE SEQUENCE tplscat_inner_configs_ids_seq INCREMENT BY 1
       MINVALUE 100
       START WITH 100
       NO CYCLE;

CREATE TABLE tplscat_inner_configs (
          tplscat_inner_config_id integer DEFAULT nextval('tplscat_inner_configs_ids_seq') PRIMARY KEY

        , CompositePlaceholderWrapper    varchar(10) NOT NULL DEFAULT '##|'
        , ParameterPlaceholderWrapper    varchar(10) NOT NULL DEFAULT '@@|'
        , InsuficientDetLevelPlaceholder varchar(10) NOT NULL DEFAULT '#x#'
        , MarkingErrorPlaceholderWrapper varchar(10) NOT NULL DEFAULT '/!E!\\'
        , DefaultLanguage                varchar(3)  NOT NULL DEFAULT 'eng' REFERENCES language_iso639_3 (lng) ON DELETE RESTRICT ON UPDATE CASCADE
        , StrictOrient_ofParamsAndCmpsts_onDfltLngTplsSets
                                         varchar     NOT NULL DEFAULT
' StrictOrient_ofParamsAndCmpsts_onDfltLngTplsSets {
  soStrict_IsIt = True
, soExcludingInComposites = []
, soExcludingComposites   = []
, soExcludingParameters   = []
, soExcludingCompComposites = []
, soExcludingCompParameters = []
}'
        , AllowUntemplatedMessages       boolean NOT NULL DEFAULT FALSE
        , AllowUntemplatedLocalizationsOfMessages
                                         boolean NOT NULL DEFAULT FALSE
        , ShowAdhocParamsInResultOfUntemplated
                                         boolean NOT NULL DEFAULT FALSE
        , InstaniationResultMaxSize      integer NOT NULL DEFAULT 10000000
        , AllowEmptySDL_parseItByModusMargin
                                         boolean NOT NULL DEFAULT TRUE
        , AllowUnreadableSDL_parseIdByModusMargin
                                         boolean NOT NULL DEFAULT TRUE
        , ReparsingDepthMax              integer NOT NULL DEFAULT 100
        , ReparseParameterContentMaxSize integer NOT NULL DEFAULT 50000
        , Newline                        varchar NOT NULL DEFAULT E'\n'

) INHERITS (nameables) TABLESPACE tabsp_pcltcatalogs;
INSERT INTO tplscat_inner_configs(tplscat_inner_config_id, name) VALUES (1, 'Default config');

GRANT USAGE                          ON SEQUENCE tplscat_inner_configs_ids_seq TO user_pcltCatalogs_data_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE    tplscat_inner_configs         TO user_pcltCatalogs_data_admin;
GRANT SELECT                         ON TABLE    tplscat_inner_configs         TO user_pcltCatalogs_data_reader;

------------------------------------------------------------------------------

CREATE SEQUENCE tpls_collections_ids_seq INCREMENT BY 1
       MINVALUE 100
       START WITH 100
       NO CYCLE;

CREATE TABLE tpls_collections (
         tpls_collection_id integer DEFAULT nextval('tpls_collections_ids_seq') PRIMARY KEY
) INHERITS (nameables) TABLESPACE tabsp_pcltcatalogs;

INSERT INTO tpls_collections(tpls_collection_id, name) VALUES (1, 'General commons');
INSERT INTO tpls_collections(tpls_collection_id, name) VALUES (2, 'General DB commons');
INSERT INTO tpls_collections(tpls_collection_id, name) VALUES (3, 'General networking commons');
INSERT INTO tpls_collections(tpls_collection_id, name) VALUES (4, 'PCLT messages');
INSERT INTO tpls_collections(tpls_collection_id, name) VALUES (5, 'PCLT-DB messages');
INSERT INTO tpls_collections(tpls_collection_id, name) VALUES (10, 'Default commons');

GRANT USAGE                          ON SEQUENCE tpls_collections_ids_seq TO user_pcltCatalogs_data_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE    tpls_collections         TO user_pcltCatalogs_data_admin;
GRANT SELECT                         ON TABLE    tpls_collections         TO user_pcltCatalogs_data_reader;

------------------------------------------------------------------------------

CREATE SEQUENCE tpls_detalization_policies_ids_seq INCREMENT BY 1
       MINVALUE 100
       START WITH 100
       NO CYCLE;

CREATE TABLE tpls_detalization_policies (
         tpls_detalization_policy_id integer DEFAULT nextval('tpls_detalization_policies_ids_seq') PRIMARY KEY
) INHERITS (nameables) TABLESPACE tabsp_pcltcatalogs;
INSERT INTO tpls_detalization_policies (tpls_detalization_policy_id, name, description) VALUES 
          (1, 'Minimal detailization requirements for all', 'The policy gets automatically filled by the AFTER INSERT trigger on tpls.')
        , (5,  'Custom detailization requirements for all', 'The policy gets automatically filled by the AFTER INSERT trigger on tpls.')
        , (9, 'Maximal detailization requirements for all', 'The policy gets automatically filled by the AFTER INSERT trigger on tpls.');

GRANT USAGE                          ON SEQUENCE tpls_detalization_policies_ids_seq TO user_pcltCatalogs_data_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE    tpls_detalization_policies         TO user_pcltCatalogs_data_admin;
GRANT SELECT                         ON TABLE    tpls_detalization_policies         TO user_pcltCatalogs_data_reader;

------------------------------------------------------------------------------

CREATE SEQUENCE tpls_catalogs_ids_seq INCREMENT BY 1
       MINVALUE 100 
       START WITH 100 
       NO CYCLE;

CREATE TABLE tpls_catalogs (
         tpls_catalog_id             integer NOT NULL DEFAULT nextval('tpls_catalogs_ids_seq') PRIMARY KEY
       , tplscat_inner_config_id     integer NOT NULL DEFAULT 1 REFERENCES tplscat_inner_configs(tplscat_inner_config_id) ON DELETE RESTRICT ON UPDATE CASCADE
       , cat_new_version_available   boolean NOT NULL DEFAULT TRUE
       , tpls_detalization_policy_id integer     NULL           REFERENCES tpls_detalization_policies(tpls_detalization_policy_id) ON DELETE RESTRICT ON UPDATE CASCADE
) INHERITS (nameables) TABLESPACE tabsp_pcltcatalogs;

INSERT INTO tpls_catalogs(tpls_catalog_id, name) VALUES (1, 'PCLT-DB initial catalog');

GRANT USAGE                              ON SEQUENCE tpls_catalogs_ids_seq TO user_pcltCatalogs_data_admin;
GRANT SELECT, INSERT, UPDATE, DELETE     ON TABLE    tpls_catalogs         TO user_pcltCatalogs_data_admin;
GRANT SELECT                             ON TABLE    tpls_catalogs         TO user_pcltCatalogs_data_reader;
GRANT UPDATE (cat_new_version_available) ON TABLE    tpls_catalogs         TO user_pcltCatalogs_data_reader; -- in next version this workaround will be removed

------------------------------------------------------------------------------

CREATE TABLE tpls (
         pclt_id varchar PRIMARY KEY
) INHERITS (nameables) TABLESPACE tabsp_pcltcatalogs;

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE tpls TO user_pcltCatalogs_data_admin;
GRANT SELECT                         ON TABLE tpls TO user_pcltCatalogs_data_reader;

------------------------------------------------------------------------------

CREATE TABLE tpls_localized (
         pclt_id         varchar NOT NULL REFERENCES tpls (pclt_id) ON DELETE CASCADE ON UPDATE CASCADE
       , structured_text varchar NOT NULL
       , FOREIGN KEY (lng) REFERENCES language_iso639_3 (lng) ON DELETE RESTRICT ON UPDATE CASCADE
       , PRIMARY KEY (lng, pclt_id)
) INHERITS (lang_localizables) TABLESPACE tabsp_pcltcatalogs;

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE tpls_localized TO user_pcltCatalogs_data_admin;
GRANT SELECT                         ON TABLE tpls_localized TO user_pcltCatalogs_data_reader;

------------------------------------------------------------------------------

CREATE TABLE tpls_inclusions_in_collections (
         tpls_collection_id integer NOT NULL REFERENCES tpls_collections(tpls_collection_id) ON DELETE CASCADE ON UPDATE CASCADE
       , pclt_id            varchar NOT NULL REFERENCES tpls (pclt_id) ON DELETE CASCADE ON UPDATE CASCADE
       , tpl_dflt_req_sdl   varchar NOT NULL -- '+inf' < {number} < 'one' < 'zero' 
       , PRIMARY KEY (tpls_collection_id, pclt_id)
) TABLESPACE tabsp_pcltcatalogs;

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE tpls_inclusions_in_collections TO user_pcltCatalogs_data_admin;
GRANT SELECT                         ON TABLE tpls_inclusions_in_collections TO user_pcltCatalogs_data_reader;

------------------------------------------------------------------------------

CREATE TABLE tpls_representations_sdls (
         tpls_detalization_policy_id integer NOT NULL REFERENCES tpls_detalization_policies(tpls_detalization_policy_id) ON DELETE RESTRICT ON UPDATE CASCADE
       , pclt_id                     varchar NOT NULL REFERENCES tpls (pclt_id) ON DELETE CASCADE ON UPDATE CASCADE
       , tpl_req_sdl                 varchar     NULL -- '+inf' < {number} < 'one' < 'zero' 
       , PRIMARY KEY (pclt_id, tpls_detalization_policy_id)
) TABLESPACE tabsp_pcltcatalogs;

-- A fast way to regulate current requirement of representation reciever's required detailization level minimum.
-- On every insertion of every template (in tpls table) a trigger gets executed, 
-- which lookups from tpls_representations_sdls table first row that uses 5th policy, remembers used tpl_req_sdl, 
-- inserts a new row into tpls_representations_sdls with same policy ID and tpl_req_sdl and with a reference 
-- on a newly created template. 
-- This way, there is an account for each template in 5th policy. In a similar way 1st and 9th policy gets filled,
-- but for these tpl_req_sdl is constant - 'zero' for 1st and '+inf' for 9th. Using simple funcion below one may 
-- perform fast regulation or tpl_req_sdl used by 5th policy. 
-- Then by fast switching of policy used by catalog from some userdefined to 1st, 5th or 9th user may regulate 
-- representation requirement for all template at once.
CREATE OR REPLACE FUNCTION setSDL_forAll5 (varchar) RETURNS VOID AS $$ -- $1 is a new tpl_req_sdl for all templates in a 5th policy
        UPDATE tpls_representations_sdls SET tpl_req_sdl = $1 WHERE tpls_detalization_policy_id = 5;
$$ LANGUAGE SQL;

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE tpls_representations_sdls   TO user_pcltCatalogs_data_admin;
GRANT SELECT                         ON TABLE tpls_representations_sdls   TO user_pcltCatalogs_data_reader;
GRANT EXECUTE                        ON FUNCTION setSDL_forAll5 (varchar) TO user_pcltCatalogs_data_admin;

------------------------------------------------------------------------------

CREATE TABLE collections_inclusions_in_catalogs (
         tpls_collection_id integer NOT NULL REFERENCES tpls_collections(tpls_collection_id) ON DELETE CASCADE ON UPDATE CASCADE
       , tpls_catalog_id    integer NOT NULL REFERENCES tpls_catalogs(tpls_catalog_id)       ON DELETE CASCADE ON UPDATE CASCADE
       , PRIMARY KEY (tpls_collection_id, tpls_catalog_id)
) TABLESPACE tabsp_pcltcatalogs;

INSERT INTO collections_inclusions_in_catalogs(tpls_catalog_id, tpls_collection_id) VALUES (1 , 10);

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE collections_inclusions_in_catalogs TO user_pcltCatalogs_data_admin;
GRANT SELECT                         ON TABLE collections_inclusions_in_catalogs TO user_pcltCatalogs_data_reader;

------------------------------------------------------------------------------

CREATE TABLE collections_dependencies (
         tpls_collection_rer_id integer NOT NULL REFERENCES tpls_collections(tpls_collection_id) ON DELETE CASCADE  ON UPDATE CASCADE
       , tpls_collection_red_id integer NOT NULL REFERENCES tpls_collections(tpls_collection_id) ON DELETE RESTRICT ON UPDATE CASCADE
         -- rer = requirer; red = required
       , PRIMARY KEY (tpls_collection_rer_id, tpls_collection_red_id)
) TABLESPACE tabsp_pcltcatalogs;

INSERT INTO collections_dependencies(tpls_collection_rer_id, tpls_collection_red_id) VALUES (10,  1);
INSERT INTO collections_dependencies(tpls_collection_rer_id, tpls_collection_red_id) VALUES (10,  2);
INSERT INTO collections_dependencies(tpls_collection_rer_id, tpls_collection_red_id) VALUES (10,  3);
INSERT INTO collections_dependencies(tpls_collection_rer_id, tpls_collection_red_id) VALUES (10,  4);
INSERT INTO collections_dependencies(tpls_collection_rer_id, tpls_collection_red_id) VALUES (10,  5);

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE collections_dependencies TO user_pcltCatalogs_data_admin;
GRANT SELECT                         ON TABLE collections_dependencies TO user_pcltCatalogs_data_reader;

------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION catalogs_thatuse_tpls_collections (integer,integer) RETURNS SETOF integer AS $$ 
        SELECT tpls_catalog_id
        FROM collections_inclusions_in_catalogs
        WHERE tpls_collection_id = $1 OR tpls_collection_id = $2
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION catalogs_thatuse_tpls (varchar, varchar) RETURNS SETOF integer AS $$ 
        SELECT tpls_catalog_id
        FROM collections_inclusions_in_catalogs ciic
           , tpls_inclusions_in_collections tiic
        WHERE ciic.tpls_collection_id = tiic.tpls_collection_id
          AND (tiic.pclt_id = $1 OR tiic.pclt_id = $2)
$$ LANGUAGE SQL;

GRANT EXECUTE ON FUNCTION catalogs_thatuse_tpls_collections  (integer,integer)                 TO user_pcltCatalogs_data_admin;
GRANT EXECUTE ON FUNCTION catalogs_thatuse_tpls_collections  (integer,integer)                 TO user_pcltCatalogs_data_reader;
GRANT EXECUTE ON FUNCTION catalogs_thatuse_tpls              (varchar,varchar)                 TO user_pcltCatalogs_data_admin;
GRANT EXECUTE ON FUNCTION catalogs_thatuse_tpls              (varchar,varchar)                 TO user_pcltCatalogs_data_reader;

--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION tpls_catalogs_onupdate() RETURNS trigger AS $tri_tpls_catalogs_onupdate$ -- upd
BEGIN
        NEW.cat_new_version_available := TRUE;
        RETURN NEW;
END;
$tri_tpls_catalogs_onupdate$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION tplscat_inner_configs_onupdate() RETURNS trigger AS $tri_tplscat_inner_configs_onupdate$ -- upd
BEGIN
        UPDATE tpls_catalogs AS tc
        SET cat_new_version_available = TRUE 
        WHERE tc.tplscat_inner_config_id = OLD.tplscat_inner_config_id
           OR tc.tplscat_inner_config_id = NEW.tplscat_inner_config_id;

        RETURN NEW;
END;
$tri_tplscat_inner_configs_onupdate$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION tpls_collections_onupdate() RETURNS trigger AS $tri_tpls_collections_onupdate$ -- upd, del
DECLARE
        old_col_id integer := NULL;
BEGIN
        IF TG_OP = 'UPDATE' THEN
                old_col_id := OLD.tpls_collection_id;
        END IF;
        UPDATE tpls_catalogs AS tc 
        SET cat_new_version_available = TRUE 
        WHERE tc.tpls_catalog_id IN (SELECT * FROM catalogs_thatuse_tpls_collections (old_col_id, NEW.tpls_collection_id));

        RETURN NEW;
END;
$tri_tpls_collections_onupdate$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION tpls_onupdate() RETURNS trigger AS $tri_tpls_onupdate$ -- upd, del
DECLARE
        old_tpl_id varchar := NULL;
BEGIN
        IF TG_OP = 'UPDATE' THEN
                old_tpl_id := OLD.pclt_id;
        END IF;
        UPDATE tpls_catalogs AS tc 
        SET cat_new_version_available = TRUE 
        WHERE tc.tpls_catalog_id IN (SELECT * FROM catalogs_thatuse_tpls (old_tpl_id, NEW.pclt_id));

        RETURN NEW;
END;
$tri_tpls_onupdate$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION tpls_oninsert() RETURNS trigger AS $tri_tpls_oninsert$ -- ins
DECLARE
        custom_sdl varchar;
BEGIN 
        SELECT tpl_req_sdl INTO custom_sdl FROM tpls_representations_sdls WHERE tpls_detalization_policy_id = 5 LIMIT 1;
        INSERT INTO tpls_representations_sdls (tpls_detalization_policy_id, pclt_id, tpl_req_sdl) 
                SELECT tpls_detalization_policy_id
                     , NEW.pclt_id
                     , CASE tpls_detalization_policy_id 
                           WHEN 1 THEN 'zero'
                           WHEN 5 THEN custom_sdl
                           WHEN 9 THEN '+inf'
		       END
                FROM tpls_detalization_policies
                WHERE tpls_detalization_policy_id IN (1, 5, 9);

        RETURN NEW;
END;
$tri_tpls_oninsert$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION tpls_localized_onupdate() RETURNS trigger AS $tri_tpls_localized_onupdate$ -- upd, del, ins
DECLARE
        old_tpl_id varchar := NULL;
        new_tpl_id varchar := NULL;
BEGIN
        IF    TG_OP = 'INSERT' THEN
                new_tpl_id := NEW.pclt_id;
        ELSIF TG_OP = 'DELETE' THEN
                old_tpl_id := OLD.pclt_id;
        ELSIF TG_OP = 'UPDATE' THEN
                new_tpl_id := NEW.pclt_id;
                old_tpl_id := OLD.pclt_id;
        END IF;
        UPDATE tpls_catalogs AS tc 
        SET cat_new_version_available = TRUE 
        WHERE tc.tpls_catalog_id IN (SELECT * FROM catalogs_thatuse_tpls (new_tpl_id, old_tpl_id));

        RETURN NEW;
END;
$tri_tpls_localized_onupdate$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION tpls_inclusions_in_collections_onupdate() RETURNS trigger AS $tri_tpls_inclusions_in_collections_onupdate$ -- upd, del, ins
DECLARE
        old_col_id integer := NULL;
        new_col_id integer := NULL;
BEGIN
        IF    TG_OP = 'INSERT' THEN
                new_col_id := NEW.tpls_collection_id;
        ELSIF TG_OP = 'DELETE' THEN
                old_col_id := OLD.tpls_collection_id;
        ELSIF TG_OP = 'UPDATE' THEN
                new_col_id := NEW.tpls_collection_id;
                old_col_id := OLD.tpls_collection_id;
        END IF;
        UPDATE tpls_catalogs AS tc 
        SET cat_new_version_available = TRUE 
        WHERE tc.tpls_catalog_id IN (SELECT * FROM catalogs_thatuse_tpls_collections (old_col_id, new_col_id));

        RETURN NEW;
END;
$tri_tpls_inclusions_in_collections_onupdate$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION tpls_representations_sdls_onupdate() RETURNS trigger AS $tri_tpls_representations_sdls_onupdate$ -- upd, ins, del
DECLARE
        old_dp_id integer := NULL;
        new_dp_id integer := NULL;
BEGIN
        IF    TG_OP = 'INSERT' THEN
                new_dp_id := NEW.tpls_detalization_policy_id;
        ELSIF TG_OP = 'DELETE' THEN
                old_dp_id := OLD.tpls_detalization_policy_id;
        ELSIF TG_OP = 'UPDATE' THEN
                new_dp_id := NEW.tpls_detalization_policy_id;
                old_dp_id := OLD.tpls_detalization_policy_id;
        END IF;
        UPDATE tpls_catalogs AS tc
        SET cat_new_version_available = TRUE 
        WHERE tc.tpls_detalization_policy_id = old_dp_id
           OR tc.tpls_detalization_policy_id = new_dp_id;

        RETURN NEW;
END;
$tri_tpls_representations_sdls_onupdate$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION collections_inclusions_in_catalogs_onupdate() RETURNS trigger AS $tri_collections_inclusions_in_catalogs_onupdate$ -- upd, ins, del
DECLARE
        old_cat_id integer := NULL;
        new_cat_id integer := NULL;
BEGIN
        IF    TG_OP = 'INSERT' THEN
                new_cat_id := NEW.tpls_catalog_id;
        ELSIF TG_OP = 'DELETE' THEN
                old_cat_id := OLD.tpls_catalog_id;
        ELSIF TG_OP = 'UPDATE' THEN
                new_cat_id := NEW.tpls_catalog_id;
                old_cat_id := OLD.tpls_catalog_id;
        END IF;
        UPDATE tpls_catalogs AS tc
        SET cat_new_version_available = TRUE 
        WHERE tc.tpls_catalog_id = old_cat_id
           OR tc.tpls_catalog_id = new_cat_id;

        RETURN NEW;
END;
$tri_collections_inclusions_in_catalogs_onupdate$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION collections_dependencies_onupdate() RETURNS trigger AS $tri_collections_dependencies_onupdate$ -- upd, ins, del
DECLARE
        old_col_id integer := NULL;
        new_col_id integer := NULL;
BEGIN
        IF    TG_OP = 'INSERT' THEN
                new_col_id := NEW.tpls_collection_rer_id;
        ELSIF TG_OP = 'DELETE' THEN
                old_col_id := OLD.tpls_collection_rer_id;
        ELSIF TG_OP = 'UPDATE' THEN
                new_col_id := NEW.tpls_collection_rer_id;
                old_col_id := OLD.tpls_collection_rer_id;
        END IF;
        UPDATE tpls_catalogs AS tc
        SET cat_new_version_available = TRUE 
        WHERE tc.tpls_catalog_id IN (SELECT * FROM catalogs_thatuse_tpls_collections (old_col_id, new_col_id));

        RETURN NEW;
END;
$tri_collections_dependencies_onupdate$ LANGUAGE plpgsql;

\i data.sql

CREATE TRIGGER tri_tpls_catalogs_onupdate                      AFTER UPDATE ON tpls_catalogs
    FOR EACH ROW EXECUTE PROCEDURE tpls_catalogs_onupdate();
CREATE TRIGGER tri_tplscat_inner_configs_onupdate              AFTER UPDATE ON tplscat_inner_configs
    FOR EACH ROW EXECUTE PROCEDURE tplscat_inner_configs_onupdate();
CREATE TRIGGER tri_tpls_collections_onupdate                   AFTER UPDATE OR DELETE ON tpls_collections
    FOR EACH ROW EXECUTE PROCEDURE tpls_collections_onupdate();
CREATE TRIGGER tri_tpls_onupdate                               AFTER UPDATE OR DELETE ON tpls
    FOR EACH ROW EXECUTE PROCEDURE tpls_onupdate();
CREATE TRIGGER tri_tpls_oninsert                               AFTER UPDATE OR DELETE ON tpls
    FOR EACH ROW EXECUTE PROCEDURE tpls_oninsert();
CREATE TRIGGER tri_tpls_localized_onupdate                     AFTER UPDATE OR DELETE OR INSERT ON tpls_localized
    FOR EACH ROW EXECUTE PROCEDURE tpls_localized_onupdate();
CREATE TRIGGER tri_tpls_inclusions_in_collections_onupdate     AFTER UPDATE OR DELETE OR INSERT ON tpls_inclusions_in_collections
    FOR EACH ROW EXECUTE PROCEDURE tpls_inclusions_in_collections_onupdate();
CREATE TRIGGER tri_tpls_representations_sdls_onupdate          AFTER UPDATE OR DELETE OR INSERT ON tpls_representations_sdls
    FOR EACH ROW EXECUTE PROCEDURE tpls_representations_sdls_onupdate();
CREATE TRIGGER tri_collections_inclusions_in_catalogs_onupdate AFTER UPDATE OR DELETE OR INSERT ON collections_inclusions_in_catalogs
    FOR EACH ROW EXECUTE PROCEDURE collections_inclusions_in_catalogs_onupdate();
CREATE TRIGGER tri_collections_dependencies_onupdate           AFTER UPDATE OR DELETE OR INSERT ON collections_dependencies
    FOR EACH ROW EXECUTE PROCEDURE collections_dependencies_onupdate();