Creating User Defined Functions and Triggers in PostgreSQL

postgresql_udf_and_triggers.md

UDF and Triggers in PostgreSQL

be aware that MySQL, SQL-Server, and SQLite all use different syntax, this is ONLY for PostgreSQL.

Why would you want to create a UDF that links to a Trigger? A trigger allows something to happen automatically, meaning once you have set it up you don't have to think about it again. The following example allows uses a 'games' table that takes p1_id, p2_id, p1_score, and p2_score. It has a winner_id column. This column could be populated via outside logic, but why should it be? It would be simpler, and avoid accidental error if the structure of the outside logic changed, if it was populated using an internal function, that happens whenever a new row is inserted.

The top of the .sql file looks like this:

DROP TABLE IF EXISTS games CASCADE;
DROP FUNCTION IF EXISTS calculate_winner();
DROP TABLE IF EXISTS players CASCADE;

CREATE TABLE players(
  id SERIAL2 PRIMARY KEY,
  p_name VARCHAR(255),
);

The games table snippet looks like this:

CREATE TABLE games(
  id SERIAL2 PRIMARY KEY,
  p1_id INT4 REFERENCES players(id) ON DELETE CASCADE,
  p2_id INT4 REFERENCES players(id) ON DELETE CASCADE,
  p1_score INT4,
  p2_score INT4,
  winner_id INT4,
);

The function like this:

CREATE OR REPLACE FUNCTION calculate_winner()
RETURNS TRIGGER AS $d$
BEGIN

IF NEW.p1_score > NEW.p2_score THEN
   NEW.winner_id = NEW.p1_id;
ELSIF NEW.p1_score < NEW.p2_score THEN
  NEW.winner_id = NEW.p2_id;
ELSE
 NEW.winner_id = 0;
END IF;

RETURN NEW;
END;
$d$ LANGUAGE plpgsql;

Notice that it returns a TRIGGER, not an INT4.

The trigger looks like this:

CREATE TRIGGER determine_winner 
BEFORE INSERT ON games
FOR EACH ROW 
EXECUTE PROCEDURE calculate_winner();

Notice that it happens BEFORE not AFTER insert.
The function is able to reference the values of the inserted row via the NEW.column syntax.

So what if you wanted to reference another table in your trigger function? Say the players table now looks like this:

CREATE TABLE players(
  id SERIAL2 PRIMARY KEY,
  p_name VARCHAR(255),
  primary_org_id INT4 REFERENCES organisations(id) ON DELETE CASCADE
);

And the games table like this:

CREATE TABLE games(
  id SERIAL2 PRIMARY KEY,
  p1_id INT4 REFERENCES players(id) ON DELETE CASCADE,
  p2_id INT4 REFERENCES players(id) ON DELETE CASCADE,
  p1_score INT4,
  p2_score INT4,
  winner_id INT4,
  p1_org_id INT4 REFERENCES organisations(id) ON DELETE CASCADE,
  p2_org_id INT4 REFERENCES organisations(id) ON DELETE CASCADE
);

A trigger can be created like this:

CREATE TRIGGER add_primaries
BEFORE INSERT ON games
FOR EACH ROW
EXECUTE PROCEDURE find_primaries();

That uses this function:

CREATE OR REPLACE FUNCTION find_primaries()
RETURNS TRIGGER AS $d$
DECLARE 
p1_found_org_id INT4 := (
  SELECT primary_org_id 
  FROM players 
  WHERE players.id = NEW.p1_id
);
p2_found_org_id INT4 := (
  SELECT primary_org_id 
  FROM players 
  WHERE players.id = NEW.p2_id
);

BEGIN
NEW.p1_org_id = p1_found_org_id;
NEW.p2_org_id = p2_found_org_id;

RETURN NEW;
END;
$d$ LANGUAGE plpgsql;

This will reference the players table, find the needed linked data, and assign it to the variable in the new game row.