In the previous post, I showed a simple example on using a database trigger to set the "updated" field when data changes. Now I will show a more complex example.
Let's take a schema for a task list:
CREATE TABLE IF NOT EXISTS tasklist (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE IF NOT EXISTS task (
tasklist_id INTEGER NOT NULL,
name VARCHAR(100) NOT NULL,
"order" INTEGER NOT NULL CHECK ("order" > 0),
PRIMARY KEY (tasklist_id, name),
FOREIGN KEY (tasklist_id) REFERENCES tasklist (id)
);
Here we have two tables, tasklist and task, the latter containing a column called order, which is, obviously, an order number for the tasks of a specific list.
Now starts our problems: To insert a new task, we need to fetch the max order number for the list, increment by 1, and insert the new task. But what will happen when two people try to insert a task at the same time?
Another problem is changing the order number of a task, because you need to shift the order of the others tasks. Deleting a task has the same issue.
To solve these problems, we can tell the database to set the order number when inserting a new task and shift the order of the tasks when a task is updated or deleted.
First, let's create a database function to help us:
CREATE OR REPLACE FUNCTION task_shift_order(
list_id INTEGER,
new_order INTEGER,
old_order INTEGER
) RETURNS INTEGER AS $$
DECLARE
max_order INTEGER;
BEGIN
-- if the task table is empty, max("order") will return null
-- use coalesce to return 0 instead of null
SELECT coalesce(max("order"), 0) INTO max_order
FROM task
WHERE tasklist_id = list_id;
-- do not allow order number greater then max order
IF new_order > max_order THEN
RAISE EXCEPTION 'New order (%) exceeds number of tasks (%) in tasklist(id = %)', new_order, max_order, list_id;
END IF;
IF new_order IS NULL THEN
-- task is being inserted without order
new_order := max_order + 1;
ELSIF old_order IS NULL THEN
-- task is being inserted at specific order
UPDATE task SET "order" = "order" + 1
WHERE tasklist_id = list_id
AND "order" >= new_order;
ELSIF new_order > old_order THEN
-- task is assigned a higher order
UPDATE task SET "order" = "order" - 1
WHERE tasklist_id = list_id
AND "order" > old_order AND "order" <= new_order;
ELSE
-- task is assigned a lower order
UPDATE task SET "order" = "order" + 1
WHERE tasklist_id = list_id
AND "order" >= new_order AND "order" < old_order;
END IF;
-- return new order number when inserting without specifying the order
RETURN new_order;
END;
$$ LANGUAGE plpgsql;
This function will update the orders on the task table and return the new order number in the case of a new task being inserted.
Now we just need to setup three triggers, before insert a task, before update a task order and after delete a task:
Before Insert:
CREATE FUNCTION task_insert_order() RETURNS TRIGGER AS $$
BEGIN
NEW."order" := task_shift_order(NEW.tasklist_id, NEW."order", NULL);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER task_insert_order BEFORE INSERT ON task
FOR EACH ROW
EXECUTE PROCEDURE task_insert_order();
Before Update:
CREATE FUNCTION task_update_order() RETURNS TRIGGER AS $$
BEGIN
NEW."order" := task_shift_order(NEW.tasklist_id, NEW."order", OLD."order");
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER task_update_order BEFORE UPDATE ON task
FOR EACH ROW
WHEN (pg_trigger_depth() = 0 -- prevent trigger recursion
AND NEW."order" <> OLD."order") -- only execute when order changes
EXECUTE PROCEDURE task_update_order();
We needed to set the condition WHEN (pg_trigger_depth() = 0 AND NEW."order" <> OLD."order") to avoid recursion, since the trigger updates the task table itself.
For the after delete trigger, we do not need to call task_shift_order() since the logic is simpler:
CREATE FUNCTION task_delete_order() RETURNS TRIGGER AS $$
BEGIN
UPDATE task SET "order" = "order" - 1
WHERE tasklist_id = OLD.tasklist_id
AND "order" > OLD."order";
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER task_delete_order AFTER DELETE ON task
FOR EACH ROW
EXECUTE PROCEDURE task_delete_order();
You may have noticed that I did not use a unique constraint on tasklist_id and order columns. This is because I was not able to update the orders without causing a conflict. If you know a way to do it, please tell it in the comments.
Top comments (0)