Re: What is the proper query

[Igor Korot <ikorot01@xxxxxxxxx>]

Hi, Melvin,

On Tue, Aug 22, 2017 at 12:55 PM, Melvin Davidson <melvin6925@xxxxxxxxx> 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 <ikorot01@xxxxxxxxx> wrote:

Hi, David,

On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
<david.g.johnston@xxxxxxxxx> wrote:
> On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot <ikorot01@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
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.