(please keep posts on the list so that others can follow along)
[EMAIL PROTECTED] 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
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
