Raise Multiple Exceptions Postgres

If there is a failure, I want to 'ROLLBACK' the transaction so nothing is inserted

That happens automatically if any exception is raised inside a function (by you or by Postgres). A function is always atomic and can only be run within a transaction context - which is the main distinction from real stored procedures (currently implemented in Postgres). There are a few exceptions: things that cannot be rolled back, like incremented serials or dblink calls.

... return the user friendly error to the user

You might just go with what Postgres returns by default. If you insist on custom error messages, be careful not to catch the wrong exception. You want to return two different messages for the same error code 23502. You would need separate blocks to achieve this in plpgsql.

and log the sql server error message somewhere

If you are happy with the standard log output in the database log, that's plain and simple (and highly configurable). The manual:

Whether messages of a particular priority are reported to the client, written to the server log, or both is controlled by the log_min_messages and client_min_messages configuration variables. See Chapter 18 for more information.

If you think of writing to a table, that would be the tricky part since everything is rolled back in case of an exception. That would include everything written to a log table. You'd have to fake an autonomous transaction to persist the log entries even though the transaction is rolled back. Detailed instructions:

• Persistent inserts in a UDF even if the function aborts

To ensure passwords with proper length, simply create a CHECK constraint with a descriptive name. Like password_needs_8_char_or_more. Then you don't have to do anything extra. Postgres will raise an exception including the name of the constraint in case of a check_violation (error code 23514).

I would make it a simple SQL function with data-modifying CTEs. Fastest, safest, simplest - and go with the built-in error messages and built-in error logging:

CREATE OR REPLACE FUNCTION create_user_func(
   role text, firstname text, lastname text, email text, username text
 , is_enabled boolean, city text, state text  -- char is a dubious data tpye
 , zipcode int, password text, team text
 , OUT new_user_id int
   ) RETURNS integer AS
$func$
   WITH i1 AS (
      INSERT INTO users (firstname, lastname, email, username, is_user_enabled
                       , city, us_state, zipcode, user_password)
      VALUES($2, $3 ,$4, $5, $6, $7, $8, $9, $10)
      RETURNING user_id
      )
   , i2 AS (
      INSERT INTO role_members (user_id, role_name)
      SELECT i1.user_id, $1
      )
   INSERT INTO team_members (user_id, dealership)
   SELECT i1.user_id, $11;
$func$ LANGUAGE sql;

And you probably do not want to use the data type char. Just use text. Or varchar(1) if you need the length restriction. Or possibly the special type "char" (with double quotes) if it's just a single ASCII character:

• Any downsides of using data type “text” for storing strings?