Fórum [DÚVIDA] SELECT CHAVE ESTRANGEIRA #26984

Bom Dia, coisa simples é (+-) assim pelo que pude entender:

SELECT DISTINCT
ON (pg_constraint.conname
, pg_type1.typname
, pg_attribute1.attname)
pg_constraint.conname AS nome_chave
, pg_type1.typname AS tabela_p
, pg_attribute1.attname AS campo_p
, pg_type2.typname AS tabela_fk
, pg_attribute2.attname AS campo_fk
, pg_get_constraintdef(pg_constraint.oid, TRUE) AS chave_estrangeira
FROM pg_constraint
INNER JOIN pg_type pg_type1
ON (pg_type1.typrelid = pg_constraint.conrelid)
INNER JOIN pg_type pg_type2
ON (pg_type2.typrelid = pg_constraint.confrelid)
INNER JOIN pg_attribute pg_attribute1
ON (pg_attribute1.attrelid = pg_constraint.conrelid)
AND (pg_attribute1.attnum = ANY (pg_constraint.conkey))
INNER JOIN pg_attribute pg_attribute2
ON (pg_attribute2.attrelid = pg_constraint.confrelid)
AND (pg_attribute2.attnum = ANY (pg_constraint.confkey))
WHERE (pg_constraint.contype = ´f´)

Ninguem trabalha porquê gosta, trabalho é o meio que utilizamos para realizar algo, originado de ideias ou desafios.

desculpe, cometi um erro, o código de consulta correto para buscar os campos da foreing key é:
SELECT pg_constraint.conname AS nome_chave
, pg_type1.typname AS tabela_p
, pg_attribute1.attname AS campo_p
, pg_description1.description AS desc_p
, pg_type2.typname AS tabela_fk
, pg_attribute2.attname AS campo_fk
, pg_description2.description AS desc_fk
, pg_get_constraintdef(pg_constraint.oid, TRUE) AS chave_estrangeira
FROM pg_constraint
INNER JOIN pg_type pg_type1
ON (pg_type1.typrelid = pg_constraint.conrelid)
INNER JOIN pg_type pg_type2
ON (pg_type2.typrelid = pg_constraint.confrelid)
INNER JOIN pg_attribute pg_attribute1
ON (pg_attribute1.attrelid = pg_constraint.conrelid)
AND (pg_attribute1.attnum = ANY (pg_constraint.conkey))
INNER JOIN pg_description pg_description1
ON (pg_description1.objoid = pg_attribute1.attrelid)
AND (pg_description1.objsubid = pg_attribute1.attnum)
INNER JOIN pg_attribute pg_attribute2
ON (pg_attribute2.attrelid = pg_constraint.confrelid)
AND (pg_attribute2.attnum = pg_constraint.confkey[
INDEX(CAST(ARRAY_TO_STRING(pg_constraint.conkey, ´.´) AS ltree),
CAST(CAST(pg_attribute1.attnum AS VARCHAR) AS ltree)) + 1])
INNER JOIN pg_description pg_description2
ON (pg_description2.objoid = pg_attribute2.attrelid)
AND (pg_description2.objsubid = pg_attribute2.attnum)
WHERE (pg_constraint.contype = ´f´)
AND (pg_constraint.conname LIKE ´fk_¬´)
ORDER BY pg_constraint.conname
, pg_type1.typname
, pg_attribute1.attname

.