Hi, Melvin, On Tue, Aug 22, 2017 at 12:55 PM, Melvin Davidson <melvin6...@gmail.com> wrote:
> > > > *While the information_schema is useful, there is no substitute for > learning how to use the pg_catalog and system information functions.* > > > *See if this query gives you what you are looking for:* > > > > > > > *SELECT rel.relname, con.conname, con.contype, > con.consrc, pg_get_constraintdef(con.oid, true) FROM pg_class rel > JOIN pg_constraint con ON (con.conrelid = rel.oid)* > > *ORDER by relname, contype, conname;* > Here is what I'm after: select x.ordinal_position AS pos, x.position_in_unique_constraint AS field_pos, c.constraint_name AS name, x.table_schema as schema, x.table_name AS table, x.column_name AS column, y.table_schema as ref_schema, y.table_name as ref_table, y.column_name as ref_column, c.update_rule, c.delete_rule from information_schema.referential_constraints c, information_schema.key_column_usage x, information_schema.key_column_usage y where x.constraint_name = c.constraint_name and y.ordinal_position = x.position_in_unique_constraint and y.constraint_name = c.unique_constraint_name AND x.table_schema = $1 AND x.table_name = $2 order by c.constraint_name, x.ordinal_position; Then in my C++ code: std::map<int, std::vector<FKField> >; foreign_keys[pos].push_back( new FKField( field_pos, name, column, ref_schema, ref_table, ref_column, update_rule, delete_rule ) ); This is my target. Thank you. > > > On Tue, Aug 22, 2017 at 12:42 PM, Igor Korot <ikoro...@gmail.com> wrote: > >> Hi, David, >> >> On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston >> <david.g.johns...@gmail.com> wrote: >> > On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot <ikoro...@gmail.com> wrote: >> >> >> >> Or this is the bug in 9.1? >> >> Since it looks like there are 2 columns with the same info in 1 >> >> table/view.... >> > >> > >> > This old email thread sounds similar to what you are describing here. >> > >> > https://www.postgresql.org/message-id/56D0C4B8.7020200%40aklaver.com >> >> Consider following table creation command: >> >> CREATE TABLE leaguescorehitter(id integer, playerid integer, scoreid >> integer, value double, foreign key(id) references leagues(id), foreign >> key(id, playerid) references playersinleague(id, playerid), foreign >> key(scoreid) references scorehits(scoreid)); >> >> There are 3 foreign keys in this table for which there are 4 rows >> displayed in my query as it should be: >> >> 1 for leagues(id) >> 1 for scorehits(scoreid) >> 2 for playersinleague(id,playerid) - 1 row per field >> >> However what I would expect to see is: >> >> [code] >> ordinal_position | position_in_unique_constraint >> 0 1 >> - this is for leagues(id) >> 1 1 >> 1 2 >> - those 2 are for >> playersinleague(id,playerid) >> 2 1 >> - this is for scorehits(scoreid) >> [/code] >> >> Instead I got ordinal_positionv = position_in_unique_constraints and >> can't tell >> which constraint is which, or more precisely, when the one ends and >> second starts. >> >> Hopefully this above will not be mangled and the spacing will be kept. >> >> Thank you. >> >> > >> > David J. >> > >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > > > -- > *Melvin Davidson* > I reserve the right to fantasize. Whether or not you > wish to share my fantasy is entirely up to you. >
