On Wed, Jan 19, 2011 at 12:08 PM, Esmin Gracic <esmin.gracic@xxxxxxxxx> wrote: > Knowing only tablename (schema_name.table_name) how to retrieve foreign keys > and related values. (using pg_catalog is preferred). I developing framework > for adobe flex (on php and postgresql). > Already figured out how to get primary key, not null columns and column > types, but kinda stuck here. > I can post these queries, if someone needs (it would be nice to have these > in wiki - where I've seen the query for primary key retrieval). > > I need following format: > > select .... 'schema_name.table_name'::regclass.... > > no | fk_name| table_column_name | foreign_table_name | foreign_column_name > > no is used for composite foreign keys. If you fire up psql with the -E argument, it will spit out all the SQL it uses to figure out such things, such as when you enter "\d tablename". For instance, in psql: test=# \d parent -- lots of queries displayed to find the table's OID (24584, in this case), -- indexes, constraints, etc. ********* QUERY ********** SELECT conname, pg_catalog.pg_get_constraintdef(r.oid, true) as condef FROM pg_catalog.pg_constraint r WHERE r.conrelid = '24584' AND r.contype = 'f' ORDER BY 1 ************************** -- etc. You should be able to adapt those queries (particularly the one shown above, though you might have to parse out the "condef" column) to suit your needs. Josh -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
