How to create `instead of` trigger on a table in postgres?
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 &gt; 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 &amp;&amp;)
);


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