Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table a(b varchar(60));
insert into a values ('123456789'),('12345');

CREATE TABLE

INSERT 0 2

select data_type,character_maximum_length 
from information_schema.columns 
where table_name='a' and table_schema='public' and column_name='b';

data_type
character_maximum_length

character varying
60

SELECT 1

alter table a alter column b type varchar(select 5);

ERROR:  syntax error at or near "select"
LINE 1: alter table a alter column b type varchar(select 5);
                                                  ^

create function max_b_len() returns int language sql as 'select 5';

CREATE FUNCTION

alter table a alter column b type varchar(max_b_len());

ERROR:  syntax error at or near "max_b_len"
LINE 1: alter table a alter column b type varchar(max_b_len());
                                                  ^

do $f$
declare new_len_limit int;
begin
select max(length(b)) from a into new_len_limit;
execute format('alter table a alter column b type varchar(%s)',new_len_limit);
end $f$ language plpgsql;

DO

select data_type,character_maximum_length 
from information_schema.columns 
where table_name='a' and table_schema='public' and column_name='b';

data_type
character_maximum_length

character varying
9

SELECT 1