[Cialug] Postgres trigger help.

[Josh More]

Here is code that worked in PostgreSQL 7.3.x.  It's old and ugly,
factors which arise due to my being awfully young when I wrote it, and
the fact that it is SQL.  I am just using it to raise exceptions for
error conditions, but you can put anything you like in place of the
RAISE EXCEPTION clause.  I recommend starting with an R.E. though,
because you can debug those awfully easily.

code follows:

CREATE TRIGGER check_event BEFORE INSERT OR UPDATE ON
events_experimental FOR EACH ROW EXECUTE PROCEDURE
check_event_for_datelocation_conflicts();

CREATE FUNCTION check_event_for_datelocation_conflicts () RETURNS opaque
AS '
        DECLARE
                row_data                events_experimental%ROWTYPE;
                room_owner      integer;
        BEGIN
                SELECT INTO room_owner key_user FROM events_location
WHERE id = NEW.key_location;
                -- NEW.key_user == 1 indicates that the user is
webmaster
                IF (NEW.key_user != room_owner) AND (NEW.key_user !=
''1'') THEN
                        RAISE EXCEPTION ''Cannot alter a room that you
do not own'';
                END IF;
                FOR row_data IN SELECT * FROM events_experimental WHERE
key_location = NEW.key_location LOOP
                        IF (row_data.id = NEW.id) and
(row_data.bool_lock) and (NEW.bool_lock) THEN
                                RAISE EXCEPTION ''Cannot modify locked
event % (%)'',row_data.txt_title,row_data.id;
                        END IF;
                        IF (row_data.time_start_time <=
NEW.time_start_time) and (NEW.time_start_time < row_data.time_end_time)
and (row_data.id != NEW.id) and (not row_data.bool_parked) and (not
NEW.bool_parked) THEN
                                RAISE EXCEPTION ''Overlap - Starts
within event % (%)'',row_data.txt_title,row_data.id;
                        END IF;
                        IF (row_data.time_start_time <
NEW.time_end_time) and (NEW.time_end_time <= row_data.time_end_time) and
(row_data.id != NEW.id) and (not row_data.bool_parked) and (not
NEW.bool_parked) THEN
                                RAISE EXCEPTION ''Overlap - Ends within
another event % (%)'',row_data.txt_title,row_data.id;
                        END IF;
                        IF (NEW.time_end_time <= NEW.time_start_time)
THEN
                                RAISE EXCEPTION ''Cannot end before you
start'';
                        END IF;
                END LOOP;
                RETURN NEW;
        END;
' LANGUAGE 'plpgsql';



CREATE TRIGGER check_slot BEFORE INSERT OR UPDATE ON volunteer_slots FOR
EACH ROW EXECUTE PROCEDURE check_slot_for_datelocation_conflicts();

CREATE FUNCTION check_slot_for_datelocation_conflicts () RETURNS opaque
AS '
        DECLARE
                row_data                volunteer_slots%ROWTYPE;
        BEGIN
                FOR row_data IN SELECT * FROM volunteer_slots WHERE
key_job = NEW.key_job LOOP
                        IF (row_data.time_start_time <=
NEW.time_start_time) and (NEW.time_start_time < row_data.time_end_time)
and (row_data.id != NEW.id) THEN
                                RAISE EXCEPTION ''Overlap - Starts
within slot %'',row_data.id;
                        END IF;
                        IF (row_data.time_start_time <
NEW.time_end_time) and (NEW.time_end_time <= row_data.time_end_time) and
(row_data.id != NEW.id) THEN
                                RAISE EXCEPTION ''Overlap - Ends within
another slot %'',row_data.id;
                        END IF;
                        IF (NEW.time_end_time <= NEW.time_start_time)
THEN
                                RAISE EXCEPTION ''Cannot end before you
start'';
                        END IF;
                END LOOP;
                RETURN NEW;
        END;
' LANGUAGE 'plpgsql';






-Josh More, RHCE, CISSP, NCLP, GIAC 
 morej at alliancetechnologies.net 
 515-245-7701

>>> "Tim Wilson" <tim_linux at wilson-home.com> 08/13/07 10:02 PM >>>
I'm using Postgres 7.1.3, and I'm trying to set up a trigger.  I have a
last_updated column on all of my tables, that is of type timestamp.  I
want
to set the field every time the record is updated.  I thought a trigger
would be best for that, but every example I find via Google doesn't
work.
One said to create a function that returned type TRIGGER, another said
type
VOID, neither of which worked.  Every example I find uses one of those
return types.  Anyone have any ideas?

-- 
Tim