Untitled

CREATE OR REPLACE FUNCTION check_presentation_conflict()
    RETURNS TRIGGER AS $check_presentation_conflict$
    DECLARE
        overlap_count RECORD;
        old_id BIGINT;
    BEGIN
        IF NEW.presentation_start<NEW.presentation_end THEN
            -- on update, don't throw overlap on the old record being changed
            IF (TG_OP='UPDATE') THEN
                old_id := OLD.presentation_timeslots_id;
            ELSE
                old_id := NULL;
            END IF;

            -- OVERLAP CHECK
            SELECT COUNT(*) INTO overlap_count FROM presentation_timeslots
                WHERE
                     (NEW.fairs_id = presentation_timeslots.fairs_id)
                     AND presentation_timeslots.presentation_timeslots_id != old_id
                     AND ((NEW.presentation_start, NEW.presentation_end)
                           OVERLAPS
                           (presentation_timeslots.presentation_start,
                            presentation_timeslots.presentation_end));
                IF overlap_count.COUNT = 0 THEN
                    RETURN NEW;
                ELSE
                    RAISE EXCEPTION 'The presentation overlaps with another one.';
                END IF;
        ELSE
            RAISE EXCEPTION 'The start of presentation must precede the end.';
        END IF;
    END;
$check_presentation_conflict$ LANGUAGE plpgsql;