Trigger in Postgres DB

Question

What is the most efficient way to create this trigger in Postgres.

I'll present below a very simplified example to what I need for my purposes, but it is based on the same concept.

Consider, we have got the schema defined below:

CREATE TABLE items (
  item_id int4,
  part_no int4,
  description text);

CREATE TABLE blacklist (
  part_no int4,
  reason text);

CREATE TABLE matches (
  item_id int4,
  part_no int4,
  reason text);

Then, every time a new item is added, we check if it is on the blacklist (comparing the part_no), and if it is, we create a new entry on the matches table.

Answer 1

You'd want a before-insert or after-insert trigger on items:

create trigger blacklist_matches after insert on items
for each row execute procedure check_blacklist();

Then the check_blacklist function would look something like this:

create function check_blacklist()
    returns trigger as $$
begin
    insert into matches (item_id, part_no, reason)
    select NEW.item_id, NEW.part_no, blacklist.reason
    from blacklist
    where blacklist.part_no = NEW.part_no;
    return null;
end;
$$ language plpgsql;

The funny looking $$ is, more or less, the SQL version of a heredoc.

That wraps the "is it on the blacklist" check and the matches insertion in one simple bit of SQL. The NEW variable in a trigger is a reference to the new row that you're working with. If NEW.part_no doesn't match anything in blacklist, then the SELECT won't produce anything and the INSERT will not be executed.

Presumably you'd have an index on blacklist.part_no (which looks like a PK anyway) so the above should be quick enough.

The PostgreSQL documentation is pretty good and has a whole section on stored procedures and triggers if you need a reference.