-- Lecture 7: SQL Constraints and Triggers
-- Example: Waiting List

-- This started as a skeleton, the definitions were filled in during the lecture

-- WaitingList(name,_pos_)
CREATE TABLE WaitingList(
  name TEXT,
  pos INT PRIMARY KEY
);

--------------------------------------------------------------------------------
-- A function that computes the next available position
CREATE FUNCTION next() RETURNS INT AS $$
  SELECT COALESCE(MAX(pos),0)+1 FROM WaitingList
$$ LANGUAGE SQL;

-- Use the next available position as the default value
ALTER TABLE WaitingList ALTER pos SET DEFAULT next();

--------------------------------------------------------------------------------
-- A function to adjust positions after someone is removed from the list
CREATE OR REPLACE FUNCTION compact() RETURNS TRIGGER AS $$
BEGIN
  UPDATE WaitingList SET pos = pos-1 WHERE pos>=OLD.pos;
  RETURN NULL;
END
$$ LANGUAGE plpgsql;

-- A trigger to adjust positions after deletes
CREATE TRIGGER compact AFTER DELETE ON WaitingList
  FOR EACH ROW EXECUTE FUNCTION compact();