(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
