Re: Can you help me with this query?

(please keep posts on the list so that others can follow along)

mike.griffin@mygenerationsoftware.com wrote:
> Joe, I'm sorry but I cannot create functions or anything like that, it's
> all has to be a select statement (or series of select statements). Users
> pull up our product and browse all the databases and scheme's and such, if
> we asked them to create functions or if we did it through calls it would
> be intrusive.
>
> I tried to flatten out the function into a nested query but couldn't quite
> get it right, is it possible to embed the select statement in the function
> into the main select statement?
>

SELECT cl.relname as TABLE_NAME,
         cr.relname as FK_TABLE_NAME,
         array(select attname from pg_attribute
               where attrelid = ct.conrelid
               and attnum = any (ct.conkey)) as TBL_ATTS,
         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';

      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