PostgreSQL trigger to make default value refer to another table

Question

PostgreSQL default values cannot contain variables or refer to any other columns in the table, or in a different table.

However, is it possible to use a trigger to create a "Default value" that will behave in the following manner. First, let me illustrate with two example tables:

create table projects
(
id serial primary key,
created_at timestamp with time zone default now()
);

create table last_updated
(
project_id integer primary key references projects,
updated_at timestamp with time zone default ...
);

In the second table (last_updated) I would like the default to be something like default projects(created_at). I.e. if a date is not specified for updated_at, look at the project_id referenced in the projects table, find the created_at date, and set the updated_at to this date. However, you cannot write this as per the first paragraph of my question.

So how do you write a trigger that will give this functionality?

Presumably, you'd create an insert trigger to check whether NEW.updated_at IS NULL and select the appropriate value from projects into NEW.updated_at if it is, in fact, NULL. — Politank-Z
– Politank-Z, Commented May 6, 2015 at 0:56
Do note that the sole purpose of such a trigger would be to maintain redundancy. Referring from last_updated.project_id -> projects. created_at would yield the same value; even at a later stage. — wildplasser
– wildplasser, Commented May 6, 2015 at 10:37

Patrick · Accepted Answer · 2015-05-06 08:58:54Z

1

The correct answer depends on what you do not specify. Typically, one would make updates to the projects table and then audit that in the last_updated table, using an AFTER UPDATE trigger on table projects:

CREATE FUNCTION audit_project_update () RETURNS trigger AS $$
BEGIN
  INSERT INTO last_updated VALUES
    (NEW.id,   -- NEW refers to the updated record in the projects table
     now()     -- this would be the logical value, but can use NEW.created_at
     -- other columns, possibly log session_user
  );
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER tr_projects_update
  AFTER UPDATE ON projects
  FOR EACH ROW EXECUTE PROCEDURE audit_project_update();

Note that in this approach there is never a situation where an INSERT is made on table last_updated without specifying a value for updated_at, assuming that you will not GRANT INSERT to any role on table last_updated, because the trigger function always specifies now(). In the table definition you do not have to specify a default value anymore: the trigger gives you the automated behavior you are looking for.

Your stated question - and confirmed in the comment below - would also use a trigger, but then on the last_updated table:

CREATE FUNCTION project_last_updated () RETURNS trigger AS $$
BEGIN
  IF (NEW.updated_at IS NULL) THEN
    SELECT created_at INTO NEW.updated_at
    FROM projects
    WHERE id = NEW.project_id;
  END IF;
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER tr_projects_update
  BEFORE INSERT ON last_updated
  FOR EACH ROW EXECUTE PROCEDURE project_last_updated();

This specification begs the question why you do not simply add a column updated_at to the projects table. Since the project_id column is PK in the last_update table, you can only store a single last update date per project.

edited May 6, 2015 at 8:58

answered May 6, 2015 at 2:46

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Over a year ago

Thanks, this would create an entry in last_updated every time an entry is added to projects, correct? What I would actually like is to be able to manually create an entry in last_updated with a reference to an existing projects.id, and if the last_updated.updated_at field is not specified, to find the created_at date from the projects table and use it as the default.

Alex Over a year ago

Thanks, this looks like what I want. To answer your question " begs the question why you do not simply add a column updated_at to the projects table.", the pair of tables is the bare minimum example I could give. The last_updated at table, for example, could be used to generate reports for selected project_ids.

Collectives™ on Stack Overflow

PostgreSQL trigger to make default value refer to another table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related