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.

CREATE OR REPLACE FUNCTION update_modified_timestamp() RETURNS trigger LANGUAGE plpgsql  
AS $function$
BEGIN
	NEW.updated_at = CURRENT_TIMESTAMP;  
	RETURN NEW;
END;  
$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.

CREATE TRIGGER trigger_update_modified_timestamp
BEFORE UPDATE ON post
FOR EACH ROW
EXECUTE 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.