Nao é exatamente o que voce quer, mas talvez este te ajuda:
Para criar a DDL para criação de indices para todas as chaves estrangeiras
usei o seguinte script:
WITH FKS AS (
SELECT
tc.constraint_name,
tc.table_name,
kcu.column_name AS field_name,
rc.update_rule AS on_update,
rc.delete_rule AS on_delete,
ccu.table_name AS references_table,
ccu.column_name AS references_field
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name
LEFT JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name
LEFT JOIN information_schema.constraint_column_usage ccu
ON rc.unique_constraint_catalog = ccu.constraint_catalog
AND rc.unique_constraint_schema = ccu.constraint_schema
AND rc.unique_constraint_name = ccu.constraint_name
where
tc.constraint_type = 'FOREIGN KEY'
AND tc.constraint_schema = 'public'
)
select array_to_string(ARRAY(
select
'CREATE INDEX ix_' || LOWER(constraint_name) ||
' ON ' || LOWER(table_name) || ' (' || LOWER(fields) ||
');'
from (
select
trim(constraint_name) as constraint_name,
trim(references_table) as references_table,
trim(cast ( fields as varchar(1024))) fields,
trim(table_name) as table_name,
trim(cast ( ref_fields as varchar(1024))) ref_fields,
trim(on_delete) as on_delete,
trim(on_update) as on_update
from (
select constraint_name,
table_name,
references_table,
(
select array_to_string(
ARRAY(
select trim(f2.field_name)
from (
select distinct f3.constraint_name,
f3.references_table, f3.field_name, on_delete, on_update
from fks f3
where f3.constraint_name = f1.constraint_name
order by f3.constraint_name, f3.references_table,
f3.field_name, on_delete, on_update
) f2
),
E', ' )
) as fields,
(
select array_to_string(
ARRAY(
select trim(f2.references_field)
from (
select distinct f3.constraint_name,
f3.references_table, f3.references_field, on_delete, on_update
from fks f3
where f3.constraint_name = f1.constraint_name
order by f3.constraint_name, f3.references_table,
f3.references_field, on_delete, on_update
) f2
),
E', ')
) as ref_fields,
(
select distinct f2.on_delete
from (
select distinct f3.constraint_name, f3.references_table,
f3.references_field, f3.field_name, on_delete, on_update
from fks f3
where f3.constraint_name = f1.constraint_name
order by f3.constraint_name, f3.references_table,
f3.references_field, f3.field_name, on_delete, on_update
) f2
) as on_delete,
(
select distinct f2.on_delete
from (
select distinct f3.constraint_name, f3.references_table,
f3.references_field, on_delete, on_update
from fks f3
where f3.constraint_name = f1.constraint_name
) f2
) as on_update
from (
select distinct constraint_name, table_name, references_table
from fks
) f1
) a
) b
order by constraint_name
), E'\n')
Eu rodei ele em BD e funcionou do jeito que eu queria.
Abraços,
--
Moisés P. Sena
(Analista e desenvolvedor de sistemas WEB e mobile)
http://www.moisespsena.com
http://linux.moisespsena.com
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
