Are plpgsql functions atomic?

Question

I'm wondering about the atomicity of a plpgsql function call, as well as nesting calls:

So for example, I'd like to put two pieces of information into two different tables:

CREATE OR REPLACE FUNCTION email_registration(
  i_email TEXT,
  i_nickname TEXT
) RETURNS JSONB AS $$
BEGIN
  DECLARE
    o_account "UserAccount";
    o_identity "UserIdentity";
  BEGIN
    INSERT INTO "UserAccount"  (nickname) VALUES (i_nickname) RETURNING * INTO o_account;
    INSERT INTO "UserIdentity" (email) VALUES (i_email) RETURNING * INTO o_identity;
    RETURN jsonb_build_object(
        'account',row_to_json(o_account), 
         'identity',row_to_json(o_identity)
    );
  END;
END;
$$ LANGUAGE 'plpgsql'; 

will calling email_registration('hello', 'world') ever result in hello being in the UserAccount table and world missing in the UserIdentity table?

---

Also, if the two inserts where refactored out into functions, will the function call still be atomic?

ie.

CREATE OR REPLACE FUNCTION email_registration(
  i_email TEXT,
  i_nickname TEXT
) RETURNS JSONB AS $$
BEGIN
  DECLARE
    o_account "UserAccount";
    o_identity "UserIdentity";
  BEGIN
    o_account := insert_email(i_email);
    o_identity := insert_nickname(i_nickname);
    RETURN jsonb_build_object(
        'account',row_to_json(o_account), 
         'identity',row_to_json(o_identity)
    );
  END;
END;
$$ LANGUAGE 'plpgsql'; 

Answer 1

A function always runs in the single transaction that was active when the function call was started, so either all statements succeed or all statements fail. That is guaranteed.

There are other things that could cause an INSERT not to have an effect, for example a row level BEFORE trigger that returns NULL.