Em 4 de abril de 2014 16:02, ALLAN YAHOO <[email protected]> escreveu:
Boa tarde,
Como eu faço uma query nas tabelas do PostGres para me retornar o nome das
tabelas que possui Referential Constraints, exemplificando, eu tenho a tabela
ft_pedido com 5 Referential Constraints com as seguintes tabelas:
cd_cliente
cd_representante
cd_transportadora
cd_tipo_pedido
si_empresa
Como eu faço ?
Algo assim serve?
"select
regclass(cl.oid)
, array_agg(ct.conname)
from
pg_class cl
join pg_constraint ct on (ct.conrelid = cl.oid)
group by
cl.oid
order by
1"
[]'s
Olá Rafael,
Obrigado por responder, quase isso, ct.conname retorna o nome da constraint eu
precisava que retornasse o nome da tabela de refêrencia.
Abraço,
Allan de Sá
--------------------------------------------------------------------------------
Pesquisei na web e achei, segue:
SELECT
tc.constraint_name, tc.table_name, kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='my table';
Fonte:
http://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys
Obrigado,
Allan de Sá
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
--------------------------------------------------------------------------------
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
