It seems like there is some flaw here. From my reading, on insert of any
row, you are updating ALL rows in the same table to just remove an
underscore if it matches the pattern of 'US_' at the beginning. That
doesn't seem likely to be what you want. I'd think you would want something
like the below.
CREATE OR REPLACE FUNCTION bonzipay.ussf_accountnumber_update()
RETURNS trigger
LANGUAGE plpgsql
AS $function$ BEGIN
if( left(NEW.accountnumber,3) = 'US_' ) then
NEW.accountnumber=replace(accountnumber,'_',' ');
RETURN NEW; END; $function$