CODEWITHSUNDEEP
postgresqltriggers
Nicole
Nicole

Reputation: 13

Linking record created in trigger to the trigger source

I have two tables, contact and user. Contact has a field called user_id which (as the name suggest) stores the ID of the user belonging to that contact.

The user record is created by a before insert trigger on contact. This works great, however I got stuck with trying to update the user_id field of the contact. I tried to do it this way:

CREATE OR REPLACE FUNCTION create_user()
    RETURNS trigger AS
$BODY$
BEGIN
    NEW.user_id = INSERT INTO user(name)
        VALUES (NEW.name)
        RETURNING id;
    RETURN NEW;
END;
$BODY$ LANGUAGE PLPGSQL;

but it doesn't work.

How can I link the newly created record to the source of the trigger?

Upvotes: 0

Views: 32

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324711

You need the INTO clause, which is PL/pgSQL specific and unrelated to INSERT INTO or SELECT INTO:

INSERT INTO user(name)
VALUES (NEW.name)
RETURNING id
INTO STRICT NEW.user_id;

INTO STRICT causes it to raise an error if not exactly one result is inserted.

Upvotes: 1

Related Questions