\set ECHO none
--------------------------------------------------------------------------------
-- Fonction trigger sur les insertions
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION set_path () RETURNS trigger AS $$
DECLARE
parent_path varchar;
_table RECORD;
_element_id RECORD;
BEGIN
-- We are dealing with a row update.
IF (TG_OP = 'UPDATE') THEN
IF (((OLD.parent_id != NEW.parent_id) AND
(NEW.parent_id IS NOT NULL))) THEN
-- Trying to get the parent path
SELECT INTO parent_path path
FROM _element
WHERE id = NEW.parent_id;
NEW.path := parent_path || '.' || NEW.id;
END IF;
-- Mise a jour recursive des path
IF (OLD.path != NEW.path) THEN
PERFORM disable_elements_triggers ();
UPDATE _element
SET path =
(
NEW.path
||
subpath (_element.path, nlevel(OLD.path))
)
WHERE _element.path <@ OLD.path
AND nlevel(_element.path) > nlevel(OLD.path);
PERFORM enable_elements_triggers ();
END IF;
-- We are dealing with different cases about inserts
ELSEIF (TG_OP = 'INSERT') THEN
IF (NEW.parent_id IS NOT NULL) THEN
-- Trying to get the parent path
SELECT INTO parent_path path
FROM _element
WHERE id = NEW.parent_id;
NEW.path := parent_path || '.' || NEW.id;
-- Updating counters
PERFORM disable_elements_triggers ();
FOR _element_id IN
SELECT id
FROM _element WHERE _element.path @> NEW.path LOOP
UPDATE _element
SET counters =
COALESCE
(
counters ||
(
TG_RELNAME::text =>
(
SELECT (COALESCE ((counters->(TG_RELNAME::text))::int, 0) + 1)::text
FROM _element
WHERE _element.id = _element_id.id
)
),
(
TG_RELNAME::text =>
(
SELECT (COALESCE ((counters->(TG_RELNAME::text))::int, 0) + 1)::text
FROM _element
WHERE _element.id = _element_id.id
)
)
)
WHERE _element.id = _element_id.id;
END LOOP;
PERFORM enable_elements_triggers ();
ELSEIF (NEW.parent_id IS NULL) THEN
NEW.path := NEW.id;
UPDATE _counter
SET table_count = table_count + 1
WHERE table_name = TG_RELNAME;
END IF;
ELSEIF (TG_OP = 'DELETE') THEN
-- Updating counters
PERFORM disable_elements_triggers ();
FOR _element_id IN
SELECT id
FROM _element
WHERE _element.path @> OLD.path
AND nlevel(_element.path) < nlevel(OLD.path) LOOP
UPDATE _element
SET counters =
COALESCE
(
counters ||
(
TG_RELNAME::text =>
(
SELECT (COALESCE ((counters->(TG_RELNAME::text))::int, 0) - 1)::text
FROM _element
WHERE _element.id = _element_id.id
)
),
(
TG_RELNAME::text =>
(
SELECT (COALESCE ((counters->(TG_RELNAME::text))::int, 0) - 1)::text
FROM _element
WHERE _element.id = _element_id.id
)
)
)
WHERE _element.id = _element_id.id;
END LOOP;
PERFORM enable_elements_triggers ();
RETURN OLD;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
--------------------------------------------------------------------------------
-- Fonction trigger sur les delete
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION recursive_delete () RETURNS trigger AS $$
DECLARE
BEGIN
DELETE
FROM _element
WHERE _element.path <@ OLD.path;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
--------------------------------------------------------------------------------
-- Functions to disable or enable the elements triggers
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION disable_elements_triggers () RETURNS INTEGER AS $$
DECLARE
BEGIN
ALTER TABLE _site DISABLE TRIGGER ALL;
ALTER TABLE _site_tmp DISABLE TRIGGER ALL;
ALTER TABLE _community DISABLE TRIGGER ALL;
ALTER TABLE _community_tmp DISABLE TRIGGER ALL;
ALTER TABLE _group DISABLE TRIGGER ALL;
ALTER TABLE _blog DISABLE TRIGGER ALL;
ALTER TABLE _blog_tmp DISABLE TRIGGER ALL;
ALTER TABLE _topic DISABLE TRIGGER ALL;
ALTER TABLE _topic_tmp DISABLE TRIGGER ALL;
ALTER TABLE _article DISABLE TRIGGER ALL;
ALTER TABLE _article_tmp DISABLE TRIGGER ALL;
ALTER TABLE _comment DISABLE TRIGGER ALL;
ALTER TABLE _comment_tmp DISABLE TRIGGER ALL;
ALTER TABLE _trackback DISABLE TRIGGER ALL;
ALTER TABLE _trackback_tmp DISABLE TRIGGER ALL;
ALTER TABLE _link DISABLE TRIGGER ALL;
ALTER TABLE _link_tmp DISABLE TRIGGER ALL;
ALTER TABLE _album DISABLE TRIGGER ALL;
ALTER TABLE _album_tmp DISABLE TRIGGER ALL;
ALTER TABLE _text DISABLE TRIGGER ALL;
ALTER TABLE _text_tmp DISABLE TRIGGER ALL;
ALTER TABLE _relation DISABLE TRIGGER ALL;
ALTER TABLE _relation_tmp DISABLE TRIGGER ALL;
ALTER TABLE _log DISABLE TRIGGER ALL;
ALTER TABLE _log_tmp DISABLE TRIGGER ALL;
ALTER TABLE _product DISABLE TRIGGER ALL;
ALTER TABLE _product_association DISABLE TRIGGER ALL;
ALTER TABLE _product_association_tmp DISABLE TRIGGER ALL;
ALTER TABLE _product_billing DISABLE TRIGGER ALL;
ALTER TABLE _product_billing_tmp DISABLE TRIGGER ALL;
ALTER TABLE _product_billing_operation DISABLE TRIGGER ALL ;
RETURN 1;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION enable_elements_triggers () RETURNS INTEGER AS $$
DECLARE
BEGIN
ALTER TABLE _site ENABLE TRIGGER ALL;
ALTER TABLE _site_tmp ENABLE TRIGGER ALL;
ALTER TABLE _community ENABLE TRIGGER ALL;
ALTER TABLE _community_tmp ENABLE TRIGGER ALL;
ALTER TABLE _group ENABLE TRIGGER ALL;
ALTER TABLE _blog ENABLE TRIGGER ALL;
ALTER TABLE _blog_tmp ENABLE TRIGGER ALL;
ALTER TABLE _topic ENABLE TRIGGER ALL;
ALTER TABLE _topic_tmp ENABLE TRIGGER ALL;
ALTER TABLE _article ENABLE TRIGGER ALL;
ALTER TABLE _article_tmp ENABLE TRIGGER ALL;
ALTER TABLE _comment ENABLE TRIGGER ALL;
ALTER TABLE _comment_tmp ENABLE TRIGGER ALL;
ALTER TABLE _trackback ENABLE TRIGGER ALL;
ALTER TABLE _trackback_tmp ENABLE TRIGGER ALL;
ALTER TABLE _link ENABLE TRIGGER ALL;
ALTER TABLE _link_tmp ENABLE TRIGGER ALL;
ALTER TABLE _album ENABLE TRIGGER ALL;
ALTER TABLE _album_tmp ENABLE TRIGGER ALL;
ALTER TABLE _text ENABLE TRIGGER ALL;
ALTER TABLE _text_tmp ENABLE TRIGGER ALL;
ALTER TABLE _relation ENABLE TRIGGER ALL;
ALTER TABLE _relation_tmp ENABLE TRIGGER ALL;
ALTER TABLE _log ENABLE TRIGGER ALL;
ALTER TABLE _log_tmp ENABLE TRIGGER ALL;
ALTER TABLE _product ENABLE TRIGGER ALL;
ALTER TABLE _product_association ENABLE TRIGGER ALL;
ALTER TABLE _product_association_tmp ENABLE TRIGGER ALL;
ALTER TABLE _product_billing ENABLE TRIGGER ALL;
ALTER TABLE _product_billing_tmp ENABLE TRIGGER ALL;
ALTER TABLE _product_billing_operation ENABLE TRIGGER ALL;
RETURN 1;
END;
$$ LANGUAGE plpgsql;