How to create a PostgreSQL stored procedure that automatically updates an updated_at column

Posted on by

To create a PostgreSQL stored procedure that automatically updates an updated_at column with the current timestamp when a record is updated, you can use a trigger and a function. Here’s how you can create such a stored procedure:

  1. First, you need to create a function that will update the updated_at column. This function will be called by a trigger whenever an update operation is performed on a specific table.

CREATE OR REPLACE FUNCTION update_updated_at() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;

In this function:

  • CREATE OR REPLACE FUNCTION update_updated_at() creates a new function named update_updated_at.
  • RETURNS TRIGGER specifies that the function returns a trigger type.
  • NEW.updated_at = NOW(); updates the updated_at column of the record being modified with the current timestamp.
  • RETURN NEW; returns the updated record.
  1. Next, you can create a trigger that fires before an UPDATE operation on your table. This trigger will call the update_updated_at() function.

CREATE TRIGGER trigger_update_updated_at BEFORE UPDATE ON your_table FOR EACH ROW EXECUTE FUNCTION update_updated_at();

In this trigger:

  • CREATE TRIGGER trigger_update_updated_at creates a new trigger named trigger_update_updated_at.
  • BEFORE UPDATE ON your_table specifies that the trigger will fire before an UPDATE operation on a table named your_table. Replace your_table with the name of your table.
  • FOR EACH ROW indicates that the trigger will operate on each row being updated.
  • EXECUTE FUNCTION update_updated_at(); specifies that the update_updated_at() function will be executed before the UPDATE operation.

With this setup, whenever you perform an UPDATE operation on the specified table, the updated_at column will automatically be updated with the current timestamp without the need to modify your SQL queries directly.

Leave a comment