How can I insert a value from another table to another on insertion?

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.

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:

• https://www.postgresql.org/docs/current/triggers.html

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!