<snip>
>
> 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';
>
How about:
SELECT cl.relname as TABLE_NAME,
cr.relname as FK_TABLE_NAME,
(select array(select attname from pg_attribute
where attrelid = ct.conrelid
and attnum = any (ct.conkey))) AS TBL_ATTS,
(select array(select attname from pg_attribute
where attrelid = ct.confrelid
and attnum = any (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';
Note the function is no longer there ;-)
Nick
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
