Re: Can you help me with this query?

<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