Re: Can you help me with this query?

mike.griffin@mygenerationsoftware.com wrote:
> The query below will return all of the foreign keys in the current schema,
> I get the ForeignKey name as FK_NAME and both the primary and foreign
> table's name and schema, now I just need the columns involved in the
> foreign key itself, there is a column called confkey in pg_constraint and
> it's an array?  It holds the column id

It is an array because foreign keys can have more than one participating
field -- how do you want that represented? Here's a way that you can get
an array of the participating field names, assuming you're using
Postgres 7.4.x:

create or replace function getattnames(oid, smallint[])
returns name[] as '
   select array(select attname from pg_attribute
                where attrelid = $1
                and attnum = any ($2))
' language sql;

SELECT cl.relname as TABLE_NAME,
        cr.relname as FK_TABLE_NAME,
        getattnames(ct.conrelid, ct.conkey) as TBL_ATTS,
        getattnames(ct.confrelid, ct.confkey) as FK_TBL_ATTS
FROM pg_constraint ct
JOIN pg_class cl ON cl.oid=conrelid
JOIN pg_namespace nl ON nl.oid=cl.relnamespace
JOIN pg_class cr ON cr.oid=confrelid
JOIN pg_namespace nr ON nr.oid=cr.relnamespace
LEFT OUTER JOIN pg_description des ON des.objoid=ct.oid
WHERE contype='f';

      table_name     |   fk_table_name    |  tbl_atts   | fk_tbl_atts
--------------------+--------------------+-------------+-------------
  rule_and_refint_t3 | rule_and_refint_t1 | {id3a,id3b} | {id1a,id1b}
  rule_and_refint_t3 | rule_and_refint_t2 | {id3a,id3c} | {id2a,id2c}
  fktable            | pktable            | {fk}        | {id}
  clstr_tst          | clstr_tst_s        | {b}         | {rf_a}
(4 rows)

HTH,

Joe