Trigger using data from inserted row

Question

I need to write a function that triggers each time a new row is inserted into my table named users. The table has three columns: name, surname, id. Each time a new user is added, I want to automatically trigger CREATE USER, where the username is the id from the newly inserted row. For example, if I add a row with John, Smith, jsmith, I want a new user named jsmith. How can I use NEW in CREATE USER, so that, like in the example, it creates a user with the username jsmith?

Answer 1

I believe what you are looking for is the pl/pgsql EXECUTE statement:

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

This takes a string, which you can build up however you like. So in your case, you need to concatenate a fixed piece of SQL (the words CREATE USER, and any options you want to pass to configure the user) with a variable based on the inserted row (from the NEW record).

As the manual mentions, it's important when creating dynamic SQL to quote input appropriately for its context, in this case, using the quote_ident function to quote the username.

So your trigger function might look roughly like this:

CREATE OR REPLACE FUNCTION user_trigger() RETURNS TRIGGER
    LANGUAGE plpgsql
AS $$
    BEGIN
        EXECUTE 'CREATE USER ' || quote_ident(NEW.id) || ' NOLOGIN';
    END;
$$;