til / automating actions in postgres
Let’s say we have a PostgreSQL table of blog posts. Each post contains some data and a
updated_at (timestamp) column. Whenever we update the information on a post, we also want to update
updated_at. Now, we could do it by passing a new timestamp to the column, but there’s also a nice way using functions and triggers in Postgres.
We’ll start by creating a function.
1CREATE OR REPLACE FUNCTION update_modified_timestamp() RETURNS trigger LANGUAGE plpgsql 2AS $function$ 3BEGIN 4 NEW.updated_at = CURRENT_TIMESTAMP; 5 RETURN NEW; 6END; 7$function$
That’s a bit scary, let’s walk through it. We create, or replace if it already exists, a function,
update_modified_timestamp, that returns a trigger. The main part of the function falls between
$function$. Here, we modify
updated_at with the
CURRENT_TIMESTAMP and return the updated row.
The function won’t do anything on its own, we also need to define a trigger on the post table that tells Postgres when it should run.
1CREATE TRIGGER trigger_update_modified_timestamp 2BEFORE UPDATE ON post 3FOR EACH ROW 4EXECUTE FUNCTION update_modified_timestamp();
This creates a trigger and gives it the name
trigger_update_modified_timestamp. The trigger runs before updating a post, and it will execute
updated_modified_timestamp for each affected row.