How do I call or invoke an INSERT function on one table based on the result of another table?
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 works 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 SELECT welcome(1234);
However, what I am trying to do is call or run this FUNCTION based on a condition AFTER
, the new user will be inserted into another table and this user will be 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, with sympathy, I am 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 syntax CREATE FUNCTION x() AS TRIGGER
, and it seems like Postgres is leading me in this direction, but there are no examples in my case. Help! (and thanks in advance!)
source to share
In the SQL standard, you define trigger
who fires a trigger function
when a certain action occurs. In your case, you want to create a trigger AFTER INSERT
whose trigger function calls your "hello" 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();
source to share