CREATE OR REPLACE FUNCTION PUBLIC.validate_service_realization()
  RETURNS TRIGGER AS
$BODY$
DECLARE
    can_takes_urgent_orders BOOLEAN;
    can_executes_service BOOLEAN := FALSE;
BEGIN
    SELECT dt.takes_urgent_orders
    INTO can_takes_urgent_orders
    FROM department_type dt
    JOIN department d ON NEW.department_id = d.id AND d.department_type_id = dt.id;

    IF (NEW.urgent=TRUE AND can_takes_urgent_orders=FALSE) THEN
        RAISE EXCEPTION 'Department with id % can not takes urgent orders', NEW.department_id;
    END IF;

    SELECT COUNT(*)
    INTO can_executes_service
    FROM department_type_services dts
    WHERE dts.department_types_id = NEW.department_id AND dts.services_id = NEW.service_id;

    IF (can_executes_service = FALSE) THEN
        RAISE EXCEPTION 'Department with id % can not executes service with id %', NEW.department_id, NEW.service_id;
    END IF;

    RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER before_insert_service_realization_validation
  BEFORE INSERT
  ON PUBLIC.service_realization
  FOR EACH ROW
  EXECUTE PROCEDURE validate_service_realization();

CREATE TRIGGER before_update_service_realization_validation
  BEFORE UPDATE
  ON PUBLIC.service_realization
  FOR EACH ROW
  EXECUTE PROCEDURE validate_service_realization();


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


CREATE OR REPLACE FUNCTION PUBLIC.validate_department()
  RETURNS TRIGGER AS
$BODY$
DECLARE
    executor_executor_id INTEGER;
BEGIN
    SELECT d.executor_id
    INTO executor_executor_id
    FROM department d
    WHERE d.id = NEW.executor_id;

    IF (executor_executor_id != NEW.executor_id) THEN
        RAISE EXCEPTION 'Department with id % can not be executor', NEW.executor_id;
    END IF;

    RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER before_insert_department_validation
  BEFORE INSERT
  ON PUBLIC.department
  FOR EACH ROW
  EXECUTE PROCEDURE validate_department();

CREATE TRIGGER before_update_department_validation
  BEFORE UPDATE
  ON PUBLIC.department
  FOR EACH ROW
  EXECUTE PROCEDURE validate_department();