How to drop all of a table's indices
create or replace function drop_indexes_on_table(a_schema text, a_table text)
returns int
language 'plpgsql'
as $$
declare
sql_to_run text;
idx_rec record;
num_idxs_dropped int;
begin
num_idxs_dropped := 0;
raise notice 'looking for indexes for table %.%', a_schema, a_table;
for idx_rec in
select idx_info.relname as index_name
from pg_index as idx
join pg_class as tbl
on tbl.oid = idx.indrelid
join pg_namespace as schm
on tbl.relnamespace = schm.oid
join pg_class as idx_info
on idx.indexrelid = idx_info.oid
where schm.nspname = a_schema
and tbl.relname = a_table
loop
num_idxs_dropped := num_idxs_dropped + 1;
raise notice 'about to drop index % for table %.%', idx_rec.index_name, a_schema, a_table;
sql_to_run := 'drop index if exists ' || a_schema || '.' || idx_rec.index_name;
execute sql_to_run;
end loop;
return num_idxs_dropped;
end;
$$;
commit;