Home Writing

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.


  • Loading next post...
  • Loading previous post...