I have this table called users
. Whenever I sign up a user, I generate an UUID for him. When this UUID is generated I want to insert a row in another table called health
, with the same UUID that was inserted on the users table.
I was trying to make this way, but I can’t make it work:
CREATE OR REPLACE FUNCTION test()
RETURNS trigger AS
'
BEGIN
INSERT INTO health(uuid) VALUES (users.uuid);
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER test_trigger
AFTER INSERT
ON users
FOR EACH ROW
EXECUTE PROCEDURE test(users.uuid);
I’m having trouble, because there’s no data that can be compared on the health table, as the row doesn’t exist. The little change on syntax (the '
) is because of the Spring JPA.
Advertisement
Answer
You seem to be under the impression you’d need to pass the UUID in the function call. But that is not so. The special record NEW
contains the new row. Use it in the function body:
CREATE OR REPLACE FUNCTION test()
RETURNS trigger
LANGUAGE plpgsql AS
'
BEGIN
INSERT INTO health(uuid) VALUES (NEW.uuid); -- this!
RETURN NULL; -- irreleant for AFTER trigger
END
';
CREATE TRIGGER test_trigger
AFTER INSERT ON users
FOR EACH ROW EXECUTE FUNCTION test(); -- no parameter!
Basics in the manual:
For Postgres 10 or older use the (misleading) keyword PROCEDURE
instead (no difference, new syntax is just more reasonable):
FOR EACH ROW EXECUTE PROCEDURE test(); -- no parameter!