Alter the column with same name in multiple table in PostgreSQL

If you have column that replicated in 500+ tables then to some extent you really do have to execute 800 update statements. But you do not have to type out all 500+ update statements -- it is better to have a function that help you to create the targeted update statements.

this function alter the size of the replicated column, 

CREATE  FUNCTION fn_alter_tbl(schema_name varchar, column_name varchar, length integer) RETURNS varchar
    LANGUAGE plpgsql
    AS $$
DECLARE
-- declare any variable over here
Vname varchar(150);
VnameId varchar(150);
Vcnt int = 0;
VColCnt int;
RtnTblList varchar = '';
-- create cursor, will hold all the tables in given schema
Ctbl_list cursor for select * from pg_stat_user_tables where schemaname=schema_name order by relname;
Vtbl pg_stat_user_tables%rowtype;
BEGIN
open Ctbl_list;
-- iterate cursor to get table
loop fetch next from Ctbl_list into Vtbl;
exit when not found;
Vname = Vtbl.relname;
VnameId = Vtbl.relid;
--process each table to check given column exist in the table, if it is qry will return 1 and alter --statement will be executed
EXECUTE 'select count(*) from pg_attribute where attrelid ='||VnameId||' and attname='||quote_literal(column_name)  into VColCnt;
if  VColCnt != 0 then
EXECUTE 'alter table spms_fixedassets.'|| Vname ||' alter column ' || column_name || ' type  varchar('||length||')';
vcnt = vcnt +1;
RtnTblList = RtnTblList ||Vname || ','||chr(10);
end if;
end loop;
raise notice ' RtnTblList :: %',RtnTblList;
raise notice ' COUNT :: % ',Vcnt;
RETURN RtnTblList;
END;
$$;

 Execute the function

 select * from fn_alter_tbl('fixedassets', 'str_empno',20)

Result :

It return list of altered table 

ex:

d_indent_form,
d_indenteditems,
d_iss_mat_register,
d_indentor_cc,
d_item_verify_issues,
d_mapping_cc, 


above function alter the column size that exist on multiple table, this simply reduce the DBA work by
--> need not find the table with column 
--> write alter statement for each table




parameter  :

1) schema_name : name of the schema in which do you like to 

change the table's column, If you don't created any schema PostgreSQL use by default “public” schema

2) column_name : name of the column, this may exist in multiple table of above schema

3) length : new length of the above column

 

Note : This will work only for the type varchar, and function will return list of altered table