code1, PostgreSQL - rextester

code1

declare
v_c numeric(5);
v_tab varchar;
v_cn varchar;
v_cl varchar;
v_col varchar;
v_ind varchar;
v_s varchar;

cursor c_ind is
select distinct cc.table_name, cc.constraint_name
from (select table_name, constraint_name
from (select b.table_name, b.constraint_name,
max(CASE position WHEN 1 THEN column_name ELSE Null END) cnamel,
max(CASE position WHEN 2 THEN column_name ELSE Null END) cname2,
max(CASE position WHEN 3 THEN column_name ELSE Null END) cname3,
max(CASE position WHEN 4 THEN column_name ELSE Null END) cname4,
max(CASE position WHEN 5 THEN column_name ELSE Null END) cname5,
max(CASE position WHEN 6 THEN column_name ELSE Null END) cname6,
max(CASE position WHEN 7 THEN column_name ELSE Null END) cname7,
max(CASE position WHEN 8 THEN column_name ELSE Null END) cname8,
count(*) col_cnt
from (select table_name, constraint_name, column_name, position
from user_cons_columns) a, user_constraints b
where a.constraint_name = b.constraint_name and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
(select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cnamel, cname2, cname3, cname4, cname5, cname6, cname7, cname8)
and i.column_position <= cons.col_cnt
group by i.index_name
)) cc;

cursor c_col is select column_name from USER_CONS_COLUMNS
where table_name = v_tab and constraint_name = v_cn order by position;

begin

select max(to_number(substr(index_name,8,8))) into v_c from user_ind_columns uc
where upper(uc.index_name) like 'IDX_FK_0%';
v_c := nvl(v_c,0);
open c_ind;
loop
fetch c_ind into v_tab, v_cn;
exit when c_ind%notfound;
v_c := v_c + 1;
v_col := ''; v_s := '';
open c_col;
loop
fetch c_col into v_cl;
exit when c_col%notfound;
v_col := v_col||v_s||v_cl;
v_s := ',';
end loop;
close c_col;
v_ind := 'create index IDX_FK_'||lpad(to_char(v_c),8,'0')||' on '||v_tab||'('||v_col||') TABLESPACE SUFD_INDX_TS';
execute immediate v_ind;
end loop;
close c_ind;

end;


run \| edit \| history \| help	0