How do I trigger or call a function on INSERT to one table based on result of another table?

Question

I know how to do this using SQL Server or Sybase, but can't seem to get it right using Postgres 9.4. I have a FUNCTION that is working as intended:

CREATE OR REPLACE FUNCTION "public"."welcome"(u_id INT DEFAULT 1234) 
  RETURNS "void" 
  AS $$
  INSERT INTO my_table_1 (title,body,category,user_id,created_at)
  VALUES ('Welcome!', '<some text>','general',u_id,now())
  $$ LANGUAGE sql

This FUNCTION works as expected when called as SELECT welcome(1234);

However, what I'm trying to do is call or trigger this FUNCTION based on the condition AFTER a new user gets inserted into another table and that user is the first and only user:

INSERT INTO my_table_2 (user_id,name,...) VALUES (5678,'John Doe',...);

and the following condition is met:

SELECT COUNT(*) from my_table_2 WHERE <my conditions are met>;

returns exactly and only 1

So, symantically, I'm trying to accomplish:

call TRIGGER welcome(1234) AFTER INSERT into my_table_2 where my conditions are met

I've seen some examples, and don't quite understand the CREATE FUNCTION x() AS TRIGGER syntax, and it seems that Postgres is steering me in this direction, but examples are lacking in my case. Help! (and thanks in advance!)

Answer 1

In the SQL standard, you define a trigger that fires a trigger function when a certain action is taking place. In your case you want to create an AFTER INSERT trigger whose trigger function calls your "welcome" function.

First the trigger function:

CREATE FUNCTION call_welcome() RETURNS trigger AS $$
DECLARE
  cnt integer;
BEGIN
  SELECT count(*) INTO cnt FROM my_table_2 WHERE ...;
  IF cnt = 1 THEN
    PERFORM welcome(NEW.user_id);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

And the trigger:

CREATE TRIGGER tr_call_welcome
AFTER INSERT ON my_table_2
FOR EACH ROW EXECUTE PROCEDURE call_welcome();