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 ();

Whenever users is affected by an INSERT or UPDATE, the trigger is called, and full_name (re)built.