Calculated columns in PostgreSQL using triggers
Sometimes is useful to have columns that are automatically created using operations with other ones. Certainly this violates the Relational Model, but sometimes you don't have a choice if the database schema was given to you by a legacy system.
In my case, I found a living system with a table like this:
CREATE TABLE users ( first_name VARCHAR, family_name VARCHAR, full_name VARCHAR PRIMARY KEY );
It's certainly aberrant; the
full_name column is just a concatenation of the other two. For additional fun, it's the primary key.
PostgreSQL makes maintaining this monster easier by using triggers.
First, create the following function (this is in plpgsql, but it could be done in any programming language linked to the PostgreSQL engine):
CREATE FUNCTION build_user_full_name_func () RETURNS trigger AS ' BEGIN NEW.full_name = NEW.first_name || '' '' || NEW.family_name; RETURN NEW; END; ' LANGUAGE plpgsql;
What this function does should be obvious: it updates
full_name by concatenating the other two columns with a space in between.
The magic is done creating this trigger:
CREATE TRIGGER build_user_full_name_trg BEFORE INSERT OR UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE build_user_full_name_func ();
users is affected by an INSERT or UPDATE, the trigger is called, and