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.
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!