Amazon Postgres RDS: Update pg_attribute to update my custom attribute
If you want to change the length of many varchar columns (which isn't actually a custom attribute), you are better off using ALTER TABLE commands. You don't risk having unwanted side-effects, just in case PostgreSQL needs to do something more than changing the pg_attributes table. It might not do it today, but might do it in any future version.

You can have the database help you generate the ALTER TABLE DDL statements (or even, generate and execute them within a UDF).

To generate the DDL statements, you can use the information_schema.columns view; which is simpler to understand than using pg_attributes together with more catalog tables all joined. [The view actually joins several tables in non-obvious ways, but that's already done for you.]

You first check the columns involved:

-- Check the columns you are interested in
SELECT 
    table_schema, table_name, column_name, 
    data_type, character_maximum_length  
FROM
    information_schema.columns
WHERE
    column_name like '%varchar_column%' 
    AND table_schema = 'my_schema' ;


You can get something like:

| table_schema | table_name |            column_name |         data_type | character_maximum_length |
|--------------|------------|------------------------|-------------------|--------------------------|
|    my_schema |         t1 |     one_varchar_column | character varying |                        5 |
|    my_schema |         t1 | another_varchar_column | character varying |                        6 |
|    my_schema |         t2 | a_third_varchar_column | character varying |                        3 |


Once you're sure the list of columns you get is the one you're actually interested, make the database compute the necessary DDL to perform what you want:

--
-- This will generate a collection of `ALTER TABLE` statements
--
-- This is what you're looking for
--
SELECT 
    'ALTER TABLE "' || table_schema || '"."' || table_name || '" ' ||
    '    ALTER COLUMN "' || column_name || '" TYPE VARCHAR(9);' AS ddl_to_execute
FROM
    information_schema.columns
WHERE
    column_name like '%varchar_column%' 
    -- AND table_schema = 'my_schema'  
    -- AND whatever else you need
ORDER BY
    table_schema, table_name;


This will result in the following list of ALTER TABLE statements:

ALTER TABLE "my_schema"."t1" ALTER COLUMN "one_varchar_column" TYPE VARCHAR(9);
ALTER TABLE "my_schema"."t1" ALTER COLUMN "another_varchar_column" TYPE VARCHAR(9);
ALTER TABLE "my_schema"."t2" ALTER COLUMN "a_third_varchar_column" TYPE VARCHAR(9);


Copy them and execute them.

You can check the example at http://rextester.com/GYHR58531