Postgresql – How to create `instead of` trigger on a table in postgres

functionsinsertpostgresqltrigger

I want to create a trigger for a table in postgresql. My table contains data about events and includes room number, start time and event duration. Upon new insertion to the table I want to check if the room number of the new event will be busy for the time of the new event. If so then I want to raise an exception else insert the new event into the table. I thought to declare the trigger as instead of trigger for insert but postgres doesn't allow instead of triggers on insert operation on tables (and I don't want to create a view just for the trigger).

How can I work around this problem? Theoretically I could create an after trigger and then check if the newly inserted data is valid and delete if it's not. But this seems like a wrong approach conceptually and maybe practically as well (I'm not sure whether triggers are atomic and maybe someone could already make an incorrect decision based on the bad data).

This is the table definition:

create table room_schedule(
  start_date date,
  start_time time,
  room_no int,
  event_id int,
  duration interval,
  primary key(start_date, start_time, room_no, event_id)
);

This is my trigger and function definitions:

create or replace function inserttrigfunc() returns trigger as $$
declare count int;

begin

with end_time_table(eid, stime, etime) as (
        select event_id, start_time, (start_time + duration) as etime from room_schedule
        where room_no=new.room_no
    ),
        overlapping_time_table(eid, stime, etime) as(
            select * from end_time_table
            where (stime <= new.start_time and new.start_time <= etime) or
            (stime <= (new.start_time + new.duration) and (new.start_time + new.duration) <= etime)
        )
        select count(*) from overlapping_time_table into count;

        if count > 0
        then
            begin
                raise exception 'the room is already occupied at the time';
            end;
        else
            begin
                insert into room_schedule(start_date, start_time, room_no, event_id, duration)
                    values(new.start_date, new.start_time, new.room_no, new.event_id, new.duration);
            end;
end;
$$language plpgsql;





create trigger TRIG1 instead of insert
  on room_schedule
  for each row
  execute procedure inserttrigfunc();

Best Answer

You can achieve what you want with a regular row level trigger. If that trigger throws an exception, the insert won't happen. If you return new from the trigger, the insert will continue.

Quote from the manual

If a nonnull value is returned then the operation proceeds with that row value. Returning a row value different from the original value of NEW alters the row that will be inserted or updated. Thus, if the trigger function wants the triggering action to succeed normally without altering the row value, NEW (or a value equal thereto) has to be returned

(emphasis mine)

essentially something like this:

if count > 0 then
   raise exception 'the room is already occupied at the time';
else 
   return new;
end if;

Then define the trigger as: 

create trigger TRIG1 before insert
  on room_schedule
  for each row
  execute procedure inserttrigfunc();

I'm not sure whether triggers are atomic and maybe someone could already make an incorrect decision based on the bad data

Yes they are and they are part of the transaction that fired the trigger.

But you don't need a trigger at all.

This can easily be achieved with an exclusion constraint using the two dates as a daterange

create table room_schedule(
  start_date date,
  start_time time,
  room_no int,
  event_id int,
  duration interval,
  primary key(start_date, start_time, room_no, event_id),
  EXCLUDE USING gist (room_no with =, daterange(start_date, end_date, '[]') WITH &&)
);

Note that you need the extension btree gist for the above because by default a GiST index does not support equality which is needed as the constraint should only apply for the same room_no

Related Question