Re: What is the proper query

Hi, Melvin,

On Tue, Aug 22, 2017 at 12:55 PM, Melvin Davidson <melvin6925(at)gmail(dot)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 <ikorot01(at)gmail(dot)com> wrote:
>
>> Hi, David,
>>
>> On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
>> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>> > On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot <ikorot01(at)gmail(dot)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(at)postgresql(dot)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.
>