PostgreSQL get first letter of each word (except...)
Some options:

-- regexp_matches
SELECT string_agg(arr[1], '') AS string
FROM   regexp_matches('The United States of America', '\y(?!(the|of)\y)\w', 'gi') arr;

-- regexp_split_to_table
SELECT string_agg(left(word, 1), '')
FROM   regexp_split_to_table('The United States of America', '\s+') t(word)
WHERE  NOT (word ILIKE ANY ('{the,of}'::text[]));

-- Without regular expression
SELECT string_agg(left(word, 1), '')
FROM   unnest(string_to_array('The United States of America', ' ')) t(word)
WHERE  NOT (word ILIKE ANY ('{the,of}'::text[]));


SQL Fiddle.

Option 2 splits on any amount of white space. Not exactly the same as your regex.
Option 3 splits on a single space character. If you can rely on that (?), the query is cheaper.