Thread: Query about foreign key details for php framework

On 02/25/2016 04:38 PM, David Binney wrote:
> Hey guys,
>
> I am having a tricky problem which I have not needed to solve before.
> Basically one of the php frameworks I am using needs to get the same
> dataset from mysql and postgres but I am not sure how to do the joins.
>
> Below i have the mysql version of the query which work ok, and after
> that i have my attempt at the postgresql version, which is not joined
> correctly. Any help would be greatly appreciated, and in the meantime i
> will keep guessing which columns need to be joined for those three
> tables, but I am thinking there could be a view or something to solve my
> problem straight away??

The * in your MySQL query hides what it is you are trying retrieve.

So what information are you after?

Or to put it another way, what fails in the Postgres version?

>
> -------mysql working version----------
> SELECT
>   *
> FROM
>   information_schema.key_column_usage AS kcu
>   INNER JOIN information_schema.referential_constraints AS rc ON (
>     kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
>     AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
>   )
> WHERE
>   kcu.TABLE_SCHEMA = 'timetable'
>   AND kcu.TABLE_NAME = 'issues'
>   AND rc.TABLE_NAME = 'issues'
>
> ---- postgresql partial working version--------------
>
> select
>    rc.constraint_name AS name,
>    tc.constraint_type AS type,
>    kcu.column_name,
>    rc.match_option AS match_type,
>    rc.update_rule AS on_update,
>    rc.delete_rule AS on_delete,
>    kcu.table_name AS references_table,
>    kcu.column_name AS references_field,
>    kcu.ordinal_position
> FROM
>    (select distinct * from information_schema.referential_constraints) rc
>    JOIN information_schema.key_column_usage kcu
>    ON  kcu.constraint_name   = rc.constraint_name
>    AND kcu.constraint_schema = rc.constraint_schema
>    JOIN information_schema.table_constraints tc ON tc.constraint_name =
> rc.constraint_name
>    AND tc.constraint_schema = rc.constraint_schema
>    AND tc.constraint_name = rc.constraint_name
>    AND tc.table_schema = rc.constraint_schema
> WHERE
>    kcu.table_name = 'issues'
>    AND rc.constraint_schema = 'public'
>    AND tc.constraint_type = 'FOREIGN KEY'
> ORDER BY
>    rc.constraint_name,
>    cu.ordinal_position;
>
> --
> Cheers David Binney


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 02/25/2016 04:56 PM, David Binney wrote:
> Hey Adrian,
>
>    rc.constraint_name AS name,
>    tc.constraint_type AS type,
>    kcu.column_name,
>    rc.match_option AS match_type,
>    rc.update_rule AS on_update,
>    rc.delete_rule AS on_delete,
>    kcu.table_name AS references_table,
>    kcu.column_name AS references_field,
>    kcu.ordinal_position
>
> Those are the needed columns, as the end resultset.

But that is different then what you are asking from the MySQL query:

http://dev.mysql.com/doc/refman/5.7/en/information-schema.html

There is no constraint_type in the information_schema tables you 
reference in the MySQL query, unless I am missing something.

>
> On Fri, 26 Feb 2016 at 10:51 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 02/25/2016 04:38 PM, David Binney wrote:
>      > Hey guys,
>      >
>      > I am having a tricky problem which I have not needed to solve before.
>      > Basically one of the php frameworks I am using needs to get the same
>      > dataset from mysql and postgres but I am not sure how to do the
>     joins.
>      >
>      > Below i have the mysql version of the query which work ok, and after
>      > that i have my attempt at the postgresql version, which is not joined
>      > correctly. Any help would be greatly appreciated, and in the
>     meantime i
>      > will keep guessing which columns need to be joined for those three
>      > tables, but I am thinking there could be a view or something to
>     solve my
>      > problem straight away??
>
>     The * in your MySQL query hides what it is you are trying retrieve.
>
>     So what information are you after?
>
>     Or to put it another way, what fails in the Postgres version?
>
>      >
>      > -------mysql working version----------
>      > SELECT
>      > *
>      > FROM
>      > information_schema.key_column_usage AS kcu
>      > INNER JOIN information_schema.referential_constraints AS rc ON (
>      > kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
>      > AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
>      > )
>      > WHERE
>      > kcu.TABLE_SCHEMA = 'timetable'
>      > AND kcu.TABLE_NAME = 'issues'
>      > AND rc.TABLE_NAME = 'issues'
>      >
>      > ---- postgresql partial working version--------------
>      >
>      > select
>      >    rc.constraint_name AS name,
>      >    tc.constraint_type AS type,
>      >    kcu.column_name,
>      >    rc.match_option AS match_type,
>      >    rc.update_rule AS on_update,
>      >    rc.delete_rule AS on_delete,
>      >    kcu.table_name AS references_table,
>      >    kcu.column_name AS references_field,
>      >    kcu.ordinal_position
>      > FROM
>      >    (select distinct * from
>     information_schema.referential_constraints) rc
>      >    JOIN information_schema.key_column_usage kcu
>      >    ON  kcu.constraint_name   = rc.constraint_name
>      >    AND kcu.constraint_schema = rc.constraint_schema
>      >    JOIN information_schema.table_constraints tc ON
>     tc.constraint_name =
>      > rc.constraint_name
>      >    AND tc.constraint_schema = rc.constraint_schema
>      >    AND tc.constraint_name = rc.constraint_name
>      >    AND tc.table_schema = rc.constraint_schema
>      > WHERE
>      >    kcu.table_name = 'issues'
>      >    AND rc.constraint_schema = 'public'
>      >    AND tc.constraint_type = 'FOREIGN KEY'
>      > ORDER BY
>      >    rc.constraint_name,
>      >    cu.ordinal_position;
>      >
>      > --
>      > Cheers David Binney
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
> --
> Cheers David Binney


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 02/25/2016 05:36 PM, David Binney wrote:
> Hey Adrian,
>
> In this case the type is just being referenced from the
> "constraint_type" column in the "information_schema.table_constraints".
>
> select * from information_schema.table_constraints tc where table_name =
> 'products';
>
> Hopefully that helps

My confusion is that in your original post you said:

"... get the same dataset from mysql and postgres ..."

and what you are asking for is different datasets. Just trying to pin 
down exactly the data you need and whether it really is the same for 
MySQL and Postgres  or if it is different?

>
> On Fri, 26 Feb 2016 at 11:06 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 02/25/2016 04:56 PM, David Binney wrote:
>      > Hey Adrian,
>      >
>      >    rc.constraint_name AS name,
>      >    tc.constraint_type AS type,
>      >    kcu.column_name,
>      >    rc.match_option AS match_type,
>      >    rc.update_rule AS on_update,
>      >    rc.delete_rule AS on_delete,
>      >    kcu.table_name AS references_table,
>      >    kcu.column_name AS references_field,
>      >    kcu.ordinal_position
>      >
>      > Those are the needed columns, as the end resultset.
>
>     But that is different then what you are asking from the MySQL query:
>
>     http://dev.mysql.com/doc/refman/5.7/en/information-schema.html
>
>     There is no constraint_type in the information_schema tables you
>     reference in the MySQL query, unless I am missing something.
>
>      >
>      > On Fri, 26 Feb 2016 at 10:51 Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>      > <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>>> wrote:
>      >
>      >     On 02/25/2016 04:38 PM, David Binney wrote:
>      >      > Hey guys,
>      >      >
>      >      > I am having a tricky problem which I have not needed to
>     solve before.
>      >      > Basically one of the php frameworks I am using needs to
>     get the same
>      >      > dataset from mysql and postgres but I am not sure how to
>     do the
>      >     joins.
>      >      >
>      >      > Below i have the mysql version of the query which work ok,
>     and after
>      >      > that i have my attempt at the postgresql version, which is
>     not joined
>      >      > correctly. Any help would be greatly appreciated, and in the
>      >     meantime i
>      >      > will keep guessing which columns need to be joined for
>     those three
>      >      > tables, but I am thinking there could be a view or
>     something to
>      >     solve my
>      >      > problem straight away??
>      >
>      >     The * in your MySQL query hides what it is you are trying
>     retrieve.
>      >
>      >     So what information are you after?
>      >
>      >     Or to put it another way, what fails in the Postgres version?
>      >
>      >      >
>      >      > -------mysql working version----------
>      >      > SELECT
>      >      > *
>      >      > FROM
>      >      > information_schema.key_column_usage AS kcu
>      >      > INNER JOIN information_schema.referential_constraints AS
>     rc ON (
>      >      > kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
>      >      > AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
>      >      > )
>      >      > WHERE
>      >      > kcu.TABLE_SCHEMA = 'timetable'
>      >      > AND kcu.TABLE_NAME = 'issues'
>      >      > AND rc.TABLE_NAME = 'issues'
>      >      >
>      >      > ---- postgresql partial working version--------------
>      >      >
>      >      > select
>      >      >    rc.constraint_name AS name,
>      >      >    tc.constraint_type AS type,
>      >      >    kcu.column_name,
>      >      >    rc.match_option AS match_type,
>      >      >    rc.update_rule AS on_update,
>      >      >    rc.delete_rule AS on_delete,
>      >      >    kcu.table_name AS references_table,
>      >      >    kcu.column_name AS references_field,
>      >      >    kcu.ordinal_position
>      >      > FROM
>      >      >    (select distinct * from
>      >     information_schema.referential_constraints) rc
>      >      >    JOIN information_schema.key_column_usage kcu
>      >      >    ON  kcu.constraint_name   = rc.constraint_name
>      >      >    AND kcu.constraint_schema = rc.constraint_schema
>      >      >    JOIN information_schema.table_constraints tc ON
>      >     tc.constraint_name =
>      >      > rc.constraint_name
>      >      >    AND tc.constraint_schema = rc.constraint_schema
>      >      >    AND tc.constraint_name = rc.constraint_name
>      >      >    AND tc.table_schema = rc.constraint_schema
>      >      > WHERE
>      >      >    kcu.table_name = 'issues'
>      >      >    AND rc.constraint_schema = 'public'
>      >      >    AND tc.constraint_type = 'FOREIGN KEY'
>      >      > ORDER BY
>      >      >    rc.constraint_name,
>      >      >    cu.ordinal_position;
>      >      >
>      >      > --
>      >      > Cheers David Binney
>      >
>      >
>      >     --
>      >     Adrian Klaver
>      > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>     <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>      >
>      > --
>      > Cheers David Binney
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
> --
> Cheers David Binney


-- 
Adrian Klaver
adrian.klaver@aklaver.com

David,

Just to toss my 2 cents in here, the initial question to your original post was, "what's broken with the postgresql version of select"? Your statement says it's partially working. What part is working and what part isn't working? Do you get errors from the postgresql select or does it just not give you all that you need?

Stuart

David,

Can you elaborate.  Can you explain what you mean by "joins to those tables are incorrect" and "not joining correctly"? How are you determining this incorrectness? 

If you believe results are incorrect, what is incorrect/missing and why? The answer will be in how the tables and constraints are defined.

On 02/25/2016 07:19 PM, David Binney wrote:
> Ah sorry adrian,
>
> I am a little in the dark as well since this is just a broken piece of
> ORM i am attempting to fix, in the framework. So, maybe if you could
> help to reproduce that select list as a start that would be great. But,
> I am suspecting they were trying to pull similar datasets from mysql or
> postgres as an end goal.
>

Alright I ran the Postgres query you provided and it threw an error:

ERROR:  missing FROM-clause entry for table "cu"
LINE 26:   cu.ordinal_position;

in the ORDER BY clause. Changing cu.ordinal_position to 
kcu.ordinal_position obtained a result when run for a table in one of my 
databases:

production=# select  rc.constraint_name AS name,  tc.constraint_type AS type,  kcu.column_name,  rc.match_option AS
match_type, rc.update_rule AS on_update,  rc.delete_rule AS on_delete,  kcu.table_name AS references_table,
kcu.column_nameAS references_field,  kcu.ordinal_position
 
FROM  (select distinct * from information_schema.referential_constraints) rc  JOIN information_schema.key_column_usage
kcu ON  kcu.constraint_name   = rc.constraint_name  AND kcu.constraint_schema = rc.constraint_schema  JOIN
information_schema.table_constraintstc ON tc.constraint_name = 
 
rc.constraint_name  AND tc.constraint_schema = rc.constraint_schema  AND tc.constraint_name = rc.constraint_name  AND
tc.table_schema= rc.constraint_schema
 
WHERE  kcu.table_name = 'projection'  AND rc.constraint_schema = 'public'  AND tc.constraint_type = 'FOREIGN KEY'
ORDER BY  rc.constraint_name,  kcu.ordinal_position;

-[ RECORD 1 ]----+------------
name             | con_fkey
type             | FOREIGN KEY
column_name      | c_id
match_type       | NONE
on_update        | CASCADE
on_delete        | CASCADE
references_table | projection
references_field | c_id
ordinal_position | 1
-[ RECORD 2 ]----+------------
name             | con_fkey
type             | FOREIGN KEY
column_name      | c_id
match_type       | NONE
on_update        | CASCADE
on_delete        | CASCADE
references_table | projection
references_field | c_id
ordinal_position | 1
-[ RECORD 3 ]----+------------
name             | pno_fkey
type             | FOREIGN KEY
column_name      | p_item_no
match_type       | NONE
on_update        | CASCADE
on_delete        | CASCADE
references_table | projection
references_field | p_item_no

If this is not the desired result, then we will need more information.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

<div dir="ltr">That is exactly the desired result, but in my db it is returning 2k rows with exactly the same query,
evenfiltered to a specific table. </div><br /><div class="gmail_quote"><div dir="ltr">On Sat, 27 Feb 2016 at 01:16
AdrianKlaver <<a href="mailto:adrian.klaver@aklaver.com">adrian.klaver@aklaver.com</a>> wrote:<br
/></div><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On
02/25/201607:19 PM, David Binney wrote:<br /> > Ah sorry adrian,<br /> ><br /> > I am a little in the dark as
wellsince this is just a broken piece of<br /> > ORM i am attempting to fix, in the framework. So, maybe if you
could<br/> > help to reproduce that select list as a start that would be great. But,<br /> > I am suspecting they
weretrying to pull similar datasets from mysql or<br /> > postgres as an end goal.<br /> ><br /><br /> Alright I
ranthe Postgres query you provided and it threw an error:<br /><br /> ERROR:  missing FROM-clause entry for table
"cu"<br/> LINE 26:   cu.ordinal_position;<br /><br /> in the ORDER BY clause. Changing cu.ordinal_position to<br />
kcu.ordinal_positionobtained a result when run for a table in one of my<br /> databases:<br /><br /> production=#
select<br/>    rc.constraint_name AS name,<br />    tc.constraint_type AS type,<br />    kcu.column_name,<br />  
 rc.match_optionAS match_type,<br />    rc.update_rule AS on_update,<br />    rc.delete_rule AS on_delete,<br />  
 kcu.table_nameAS references_table,<br />    kcu.column_name AS references_field,<br />    kcu.ordinal_position<br />
FROM<br/>    (select distinct * from information_schema.referential_constraints) rc<br />    JOIN
information_schema.key_column_usagekcu<br />    ON  kcu.constraint_name   = rc.constraint_name<br />    AND
kcu.constraint_schema= rc.constraint_schema<br />    JOIN information_schema.table_constraints tc ON tc.constraint_name
=<br/> rc.constraint_name<br />    AND tc.constraint_schema = rc.constraint_schema<br />    AND tc.constraint_name =
rc.constraint_name<br/>    AND tc.table_schema = rc.constraint_schema<br /> WHERE<br />    kcu.table_name =
'projection'<br/>    AND rc.constraint_schema = 'public'<br />    AND tc.constraint_type = 'FOREIGN KEY'<br /> ORDER
BY<br/>    rc.constraint_name,<br />    kcu.ordinal_position;<br /><br /> -[ RECORD 1 ]----+------------<br /> name   
        | con_fkey<br /> type             | FOREIGN KEY<br /> column_name      | c_id<br /> match_type       | NONE<br
/>on_update        | CASCADE<br /> on_delete        | CASCADE<br /> references_table | projection<br />
references_field| c_id<br /> ordinal_position | 1<br /> -[ RECORD 2 ]----+------------<br /> name             |
con_fkey<br/> type             | FOREIGN KEY<br /> column_name      | c_id<br /> match_type       | NONE<br />
on_update       | CASCADE<br /> on_delete        | CASCADE<br /> references_table | projection<br /> references_field |
c_id<br/> ordinal_position | 1<br /> -[ RECORD 3 ]----+------------<br /> name             | pno_fkey<br /> type       
    | FOREIGN KEY<br /> column_name      | p_item_no<br /> match_type       | NONE<br /> on_update        | CASCADE<br
/>on_delete        | CASCADE<br /> references_table | projection<br /> references_field | p_item_no<br /><br /> If this
isnot the desired result, then we will need more information.<br /><br /> --<br /> Adrian Klaver<br /><a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a><br /></blockquote></div><div
dir="ltr">--<br /></div><div dir="ltr">Cheers David Binney</div> 

On 02/26/2016 10:47 AM, David Binney wrote:
> That is exactly the desired result, but in my db it is returning 2k rows
> with exactly the same query, even filtered to a specific table.

Note to self, read the entire doc page:

http://www.postgresql.org/docs/9.5/interactive/information-schema.html
"
Note: When querying the database for constraint information, it is 
possible for a standard-compliant query that expects to return one row 
to return several. This is because the SQL standard requires constraint 
names to be unique within a schema, but PostgreSQL does not enforce this 
restriction. PostgreSQL automatically-generated constraint names avoid 
duplicates in the same schema, but users can specify such duplicate names.
    This problem can appear when querying information schema views such 
as check_constraint_routine_usage, check_constraints, 
domain_constraints, and referential_constraints. Some other views have 
similar issues but contain the table name to help distinguish duplicate 
rows, e.g., constraint_column_usage, constraint_table_usage, 
table_constraints.
"


Best guess it is this line:

tc.constraint_name = rc.constraint_name

If you look at the output from my query you will see that is has two 
entries for name = con_fkey. There is actually only one such FK on that 
table, but another of the same name on another table. As written now it 
will find that constraint_name across all tables.

Rewriting see ^^^^^ in line:

production=# select  rc.constraint_name AS name,  tc.constraint_type AS type,  kcu.column_name,  rc.match_option AS
match_type, rc.update_rule AS on_update,  rc.delete_rule AS on_delete,  kcu.table_name AS references_table,
kcu.column_nameAS references_field,  kcu.ordinal_position
 
FROM  (select distinct * from information_schema.referential_constraints) rc  JOIN information_schema.key_column_usage
kcu ON  kcu.constraint_name   = rc.constraint_name  AND kcu.constraint_schema = rc.constraint_schema  JOIN
information_schema.table_constraintstc ON tc.constraint_name = 
 
rc.constraint_name  AND tc.constraint_schema = rc.constraint_schema  AND tc.constraint_name = rc.constraint_name  AND
tc.table_schema= rc.constraint_schema  AND tc.table_name = kcu.table_name  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 
WHERE  kcu.table_name = 'projection'  AND rc.constraint_schema = 'public'  AND tc.constraint_type = 'FOREIGN KEY'
ORDER BY  rc.constraint_name,  kcu.ordinal_position;
   name   |    type     | column_name | match_type | on_update | 
on_delete | references_table | references_field | ordinal_position

----------+-------------+-------------+------------+-----------+-----------+------------------+------------------+------------------
con_fkey| FOREIGN KEY | c_id        | NONE       | CASCADE   | 
 
CASCADE   | projection       | c_id             |                1 pno_fkey | FOREIGN KEY | p_item_no   | NONE       |
CASCADE  | 
 
CASCADE   | projection       | p_item_no        |                1


Going back to your MySQL query I came up with this:

production=# SELECT    distinct *
FROM    information_schema.key_column_usage AS kcu
INNER JOIN    information_schema.referential_constraints AS rc
ON    (kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME    AND    kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA)
--join
--    information_schema.tables
--ON
--    (tables.table_name = kcu.table_name AND tables.TABLE_SCHEMA = 
kcu.TABLE_SCHEMA)
WHERE    kcu.TABLE_SCHEMA = 'public'
AND    kcu.TABLE_NAME = 'projection';

-[ RECORD 1 ]-----------------+---------------
constraint_catalog            | production
constraint_schema             | public
constraint_name               | pno_fkey
table_catalog                 | production
table_schema                  | public
table_name                    | projection
column_name                   | p_item_no
ordinal_position              | 1
position_in_unique_constraint | 1
constraint_catalog            | production
constraint_schema             | public
constraint_name               | pno_fkey
unique_constraint_catalog     | production
unique_constraint_schema      | public
unique_constraint_name        | p_no_pkey
match_option                  | NONE
update_rule                   | CASCADE
delete_rule                   | CASCADE
-[ RECORD 2 ]-----------------+---------------
constraint_catalog            | production
constraint_schema             | public
constraint_name               | con_fkey
table_catalog                 | production
table_schema                  | public
table_name                    | projection
column_name                   | c_id
ordinal_position              | 1
position_in_unique_constraint | 1
constraint_catalog            | production
constraint_schema             | public
constraint_name               | con_fkey
unique_constraint_catalog     | production
unique_constraint_schema      | public
unique_constraint_name        | container_pkey
match_option                  | NONE
update_rule                   | CASCADE
delete_rule                   | CASCADE




>
> On Sat, 27 Feb 2016 at 01:16 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 02/25/2016 07:19 PM, David Binney wrote:
>      > Ah sorry adrian,
>      >
>      > I am a little in the dark as well since this is just a broken
>     piece of
>      > ORM i am attempting to fix, in the framework. So, maybe if you could
>      > help to reproduce that select list as a start that would be
>     great. But,
>      > I am suspecting they were trying to pull similar datasets from
>     mysql or
>      > postgres as an end goal.
>      >
>
>     Alright I ran the Postgres query you provided and it threw an error:
>
>     ERROR:  missing FROM-clause entry for table "cu"
>     LINE 26:   cu.ordinal_position;
>
>     in the ORDER BY clause. Changing cu.ordinal_position to
>     kcu.ordinal_position obtained a result when run for a table in one of my
>     databases:
>
>     production=# select
>         rc.constraint_name AS name,
>         tc.constraint_type AS type,
>         kcu.column_name,
>         rc.match_option AS match_type,
>         rc.update_rule AS on_update,
>         rc.delete_rule AS on_delete,
>         kcu.table_name AS references_table,
>         kcu.column_name AS references_field,
>         kcu.ordinal_position
>     FROM
>         (select distinct * from
>     information_schema.referential_constraints) rc
>         JOIN information_schema.key_column_usage kcu
>         ON  kcu.constraint_name   = rc.constraint_name
>         AND kcu.constraint_schema = rc.constraint_schema
>         JOIN information_schema.table_constraints tc ON tc.constraint_name =
>     rc.constraint_name
>         AND tc.constraint_schema = rc.constraint_schema
>         AND tc.constraint_name = rc.constraint_name
>         AND tc.table_schema = rc.constraint_schema
>     WHERE
>         kcu.table_name = 'projection'
>         AND rc.constraint_schema = 'public'
>         AND tc.constraint_type = 'FOREIGN KEY'
>     ORDER BY
>         rc.constraint_name,
>         kcu.ordinal_position;
>
>     -[ RECORD 1 ]----+------------
>     name             | con_fkey
>     type             | FOREIGN KEY
>     column_name      | c_id
>     match_type       | NONE
>     on_update        | CASCADE
>     on_delete        | CASCADE
>     references_table | projection
>     references_field | c_id
>     ordinal_position | 1
>     -[ RECORD 2 ]----+------------
>     name             | con_fkey
>     type             | FOREIGN KEY
>     column_name      | c_id
>     match_type       | NONE
>     on_update        | CASCADE
>     on_delete        | CASCADE
>     references_table | projection
>     references_field | c_id
>     ordinal_position | 1
>     -[ RECORD 3 ]----+------------
>     name             | pno_fkey
>     type             | FOREIGN KEY
>     column_name      | p_item_no
>     match_type       | NONE
>     on_update        | CASCADE
>     on_delete        | CASCADE
>     references_table | projection
>     references_field | p_item_no
>
>     If this is not the desired result, then we will need more information.
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
> --
> Cheers David Binney


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 02/26/2016 08:29 PM, David Binney wrote:
> Hey adrian,
>
> You are correct that the distinct will chomp the resultset down to the
> correct count, I am just concerned that there will be cases where it
> might not be accurate between the "rc" and the "kcu" joins as there is
> no table reference. I have simplified the query right down to just the
> join that i am unsure about. You can see below that as soon as i add the
> rc.unique_constraint_name, the distinct is no longer returning one row.
> In this case its fine because the rc values are the same and would
> distinct away, but there might be a case where they are diferent and you
> would have two rows and not know which values are correct?
>

Well it comes down to the question that was asked several times upstream:

what is the information you want to see?

I am not talking about a query, but a description of what attributes you 
want on what database objects.

Also given, from previous post:

"I am a little in the dark as well since this is just a broken piece of 
ORM i am attempting to fix, in the framework."

Is this not something that should be discussed with the framework 
developers, or are we already doing that:)?



-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 02/28/2016 03:42 PM, David Binney wrote:
> Hey adrian,
>
>    rc.constraint_name AS name,
>    tc.constraint_type AS type,
>    kcu.column_name,
>    rc.match_option AS match_type,
>    rc.update_rule AS on_update,
>    rc.delete_rule AS on_delete,
>    kcu.table_name AS references_table,
>    kcu.column_name AS references_field,
>    kcu.ordinal_position
>
> Those are the columns that i need as a minimum, but as you know they are
> all easy apart from the rules "on update" from the "RC" table.

I am not following, update_rule is just a field in 
information_schema.referential_constraints, how is it any harder then 
delete_rule?

The issue from what I understand is that 
information_schema.referential_constraints does not have a table_name 
field to constrain the information to a particular table. This leads 
back to the overriding question, what is the purpose of the query? I 
suspect it for use by the framework to set up attributes of a model 
based on a table, is that correct?

>
> I did start having a crack at the catalog tables but that is pretty
> complicated.
>
> On Sun, 28 Feb 2016 at 01:21 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 02/26/2016 08:29 PM, David Binney wrote:
>      > Hey adrian,
>      >
>      > You are correct that the distinct will chomp the resultset down
>     to the
>      > correct count, I am just concerned that there will be cases where it
>      > might not be accurate between the "rc" and the "kcu" joins as
>     there is
>      > no table reference. I have simplified the query right down to
>     just the
>      > join that i am unsure about. You can see below that as soon as i
>     add the
>      > rc.unique_constraint_name, the distinct is no longer returning
>     one row.
>      > In this case its fine because the rc values are the same and would
>      > distinct away, but there might be a case where they are diferent
>     and you
>      > would have two rows and not know which values are correct?
>      >
>
>     Well it comes down to the question that was asked several times
>     upstream:
>
>     what is the information you want to see?
>
>     I am not talking about a query, but a description of what attributes you
>     want on what database objects.
>
>     Also given, from previous post:
>
>     "I am a little in the dark as well since this is just a broken piece of
>     ORM i am attempting to fix, in the framework."
>
>     Is this not something that should be discussed with the framework
>     developers, or are we already doing that:)?
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
> --
> Cheers David Binney


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 03/01/2016 05:38 PM, David Binney wrote:
> Hey Adrian,
>
> Yes, that is the problem of not being able to join on the table name, to
> obtain these any fields from that table. Also, it is for a framework
> which will be managing constrains/rules/adds/deletes etc. , so needs to
> know the constrain details against each table.

Short version:

http://www.postgresql.org/docs/9.5/interactive/catalog-pg-constraint.html

SELECT*
FROMpg_constraint
WHEREconrelid = 'some_table'::regclass
ANDcontype='f';

>
> On Tue, 1 Mar 2016 at 00:59 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 02/28/2016 03:42 PM, David Binney wrote:
>      > Hey adrian,
>      >
>      >    rc.constraint_name AS name,
>      >    tc.constraint_type AS type,
>      >    kcu.column_name,
>      >    rc.match_option AS match_type,
>      >    rc.update_rule AS on_update,
>      >    rc.delete_rule AS on_delete,
>      >    kcu.table_name AS references_table,
>      >    kcu.column_name AS references_field,
>      >    kcu.ordinal_position
>      >
>      > Those are the columns that i need as a minimum, but as you know
>     they are
>      > all easy apart from the rules "on update" from the "RC" table.
>
>     I am not following, update_rule is just a field in
>     information_schema.referential_constraints, how is it any harder then
>     delete_rule?
>
>     The issue from what I understand is that
>     information_schema.referential_constraints does not have a table_name
>     field to constrain the information to a particular table. This leads
>     back to the overriding question, what is the purpose of the query? I
>     suspect it for use by the framework to set up attributes of a model
>     based on a table, is that correct?
>
>      >
>      > I did start having a crack at the catalog tables but that is pretty
>      > complicated.
>      >
>      > On Sun, 28 Feb 2016 at 01:21 Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>      > <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>>> wrote:
>      >
>      >     On 02/26/2016 08:29 PM, David Binney wrote:
>      >      > Hey adrian,
>      >      >
>      >      > You are correct that the distinct will chomp the resultset
>     down
>      >     to the
>      >      > correct count, I am just concerned that there will be
>     cases where it
>      >      > might not be accurate between the "rc" and the "kcu" joins as
>      >     there is
>      >      > no table reference. I have simplified the query right down to
>      >     just the
>      >      > join that i am unsure about. You can see below that as
>     soon as i
>      >     add the
>      >      > rc.unique_constraint_name, the distinct is no longer returning
>      >     one row.
>      >      > In this case its fine because the rc values are the same
>     and would
>      >      > distinct away, but there might be a case where they are
>     diferent
>      >     and you
>      >      > would have two rows and not know which values are correct?
>      >      >
>      >
>      >     Well it comes down to the question that was asked several times
>      >     upstream:
>      >
>      >     what is the information you want to see?
>      >
>      >     I am not talking about a query, but a description of what
>     attributes you
>      >     want on what database objects.
>      >
>      >     Also given, from previous post:
>      >
>      >     "I am a little in the dark as well since this is just a
>     broken piece of
>      >     ORM i am attempting to fix, in the framework."
>      >
>      >     Is this not something that should be discussed with the framework
>      >     developers, or are we already doing that:)?
>      >
>      >
>      >
>      >     --
>      >     Adrian Klaver
>      > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>     <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>      >
>      > --
>      > Cheers David Binney
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
> --
> Cheers David Binney


-- 
Adrian Klaver
adrian.klaver@aklaver.com

<div dir="ltr">Nice find dude and that should work joined to the other tables ;). Once more thing, is there a matching
conversiontable for the human readable result "<span><tt class="LITERAL"
style="font-size:12px;color:rgb(0,0,0);background-color:rgb(239,239,239)">a</tt><span
style="color:rgb(0,0,0);font-family:verdana,sans-serif;font-size:12px;background-color:rgb(239,239,239)"> =no action"
orwill i have to "case" that stuff? </span></span></div><br /><div class="gmail_quote"><div dir="ltr">On Thu, 3 Mar
2016at 00:58 Adrian Klaver <<a href="mailto:adrian.klaver@aklaver.com">adrian.klaver@aklaver.com</a>> wrote:<br
/></div><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On
03/01/201605:38 PM, David Binney wrote:<br /> > Hey Adrian,<br /> ><br /> > Yes, that is the problem of not
beingable to join on the table name, to<br /> > obtain these any fields from that table. Also, it is for a
framework<br/> > which will be managing constrains/rules/adds/deletes etc. , so needs to<br /> > know the
constraindetails against each table.<br /><br /> Short version:<br /><br /><a
href="http://www.postgresql.org/docs/9.5/interactive/catalog-pg-constraint.html"rel="noreferrer"
target="_blank">http://www.postgresql.org/docs/9.5/interactive/catalog-pg-constraint.html</a><br/><br /> SELECT<br />  
     *<br /> FROM<br />         pg_constraint<br /> WHERE<br />         conrelid = 'some_table'::regclass<br /> AND<br
/>        contype='f';<br /><br /> ><br /> > On Tue, 1 Mar 2016 at 00:59 Adrian Klaver <<a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a><br /> > <mailto:<a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a>>> wrote:<br /> ><br />
>    On 02/28/2016 03:42 PM, David Binney wrote:<br /> >      > Hey adrian,<br /> >      ><br /> >   
 >    rc.constraint_name AS name,<br /> >      >    tc.constraint_type AS type,<br /> >      >   
kcu.column_name,<br/> >      >    rc.match_option AS match_type,<br /> >      >    rc.update_rule AS
on_update,<br/> >      >    rc.delete_rule AS on_delete,<br /> >      >    kcu.table_name AS
references_table,<br/> >      >    kcu.column_name AS references_field,<br /> >      >   
kcu.ordinal_position<br/> >      ><br /> >      > Those are the columns that i need as a minimum, but as
youknow<br /> >     they are<br /> >      > all easy apart from the rules "on update" from the "RC" table.<br
/>><br /> >     I am not following, update_rule is just a field in<br /> >   
 information_schema.referential_constraints,how is it any harder then<br /> >     delete_rule?<br /> ><br />
>    The issue from what I understand is that<br /> >     information_schema.referential_constraints does not
havea table_name<br /> >     field to constrain the information to a particular table. This leads<br /> >   
 backto the overriding question, what is the purpose of the query? I<br /> >     suspect it for use by the framework
toset up attributes of a model<br /> >     based on a table, is that correct?<br /> ><br /> >      ><br />
>     > I did start having a crack at the catalog tables but that is pretty<br /> >      > complicated.<br
/>>      ><br /> >      > On Sun, 28 Feb 2016 at 01:21 Adrian Klaver<br /> >     <<a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a> <mailto:<a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a>><br /> >      >
<mailto:<ahref="mailto:adrian.klaver@aklaver.com" target="_blank">adrian.klaver@aklaver.com</a><br /> >   
 <mailto:<ahref="mailto:adrian.klaver@aklaver.com" target="_blank">adrian.klaver@aklaver.com</a>>>>
wrote:<br/> >      ><br /> >      >     On 02/26/2016 08:29 PM, David Binney wrote:<br /> >      >   
 > Hey adrian,<br /> >      >      ><br /> >      >      > You are correct that the distinct will
chompthe resultset<br /> >     down<br /> >      >     to the<br /> >      >      > correct count, I
amjust concerned that there will be<br /> >     cases where it<br /> >      >      > might not be accurate
betweenthe "rc" and the "kcu" joins as<br /> >      >     there is<br /> >      >      > no table
reference.I have simplified the query right down to<br /> >      >     just the<br /> >      >      >
jointhat i am unsure about. You can see below that as<br /> >     soon as i<br /> >      >     add the<br />
>     >      > rc.unique_constraint_name, the distinct is no longer returning<br /> >      >     one
row.<br/> >      >      > In this case its fine because the rc values are the same<br /> >     and would<br
/>>      >      > distinct away, but there might be a case where they are<br /> >     diferent<br /> > 
   >     and you<br /> >      >      > would have two rows and not know which values are correct?<br />
>     >      ><br /> >      ><br /> >      >     Well it comes down to the question that was asked
severaltimes<br /> >      >     upstream:<br /> >      ><br /> >      >     what is the information
youwant to see?<br /> >      ><br /> >      >     I am not talking about a query, but a description of
what<br/> >     attributes you<br /> >      >     want on what database objects.<br /> >      ><br />
>     >     Also given, from previous post:<br /> >      ><br /> >      >     "I am a little in the
darkas well since this is just a<br /> >     broken piece of<br /> >      >     ORM i am attempting to fix, in
theframework."<br /> >      ><br /> >      >     Is this not something that should be discussed with the
framework<br/> >      >     developers, or are we already doing that:)?<br /> >      ><br /> >     
><br/> >      ><br /> >      >     --<br /> >      >     Adrian Klaver<br /> >      > <a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a> <mailto:<a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a>><br /> >     <mailto:<a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a> <mailto:<a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a>>><br /> >      ><br />
>     > --<br /> >      > Cheers David Binney<br /> ><br /> ><br /> >     --<br /> >     Adrian
Klaver<br/> >     <a href="mailto:adrian.klaver@aklaver.com" target="_blank">adrian.klaver@aklaver.com</a>
<mailto:<ahref="mailto:adrian.klaver@aklaver.com" target="_blank">adrian.klaver@aklaver.com</a>><br /> ><br />
>--<br /> > Cheers David Binney<br /><br /><br /> --<br /> Adrian Klaver<br /><a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a><br /></blockquote></div><div
dir="ltr">--<br /></div><div dir="ltr">Cheers David Binney</div> 

On 03/02/2016 03:42 PM, David Binney wrote:
> Nice find dude and that should work joined to the other tables ;). Once
> more thing, is there a matching conversion table for the human readable
> result "a = no action" or will i have to "case" that stuff?

Not that I know of.




-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 03/02/2016 05:59 PM, David Binney wrote:
> Hey Adrian,
>
> Dude you are a legend. I have attempted to mod the query to use just
> those tables and i think its ok just need confirmation. It would be nice
> if it had the full text descriptors but I can always use a case to fix
> it up if necessary.

I can confirm it works and returns values. Since I am not entirely sure 
what the framework needs I cannot go any further then that.

>
> SELECT  c.conrelid::regclass::text AS table_name,
>          c.contype AS constraint_type, --c = check constraint, f =
> foreign key constraint, p = primary key constraint, u = unique
> constraint, t = constraint trigger, x = exclusion constraint
>          a.attname AS column_name,
>          c.confmatchtype AS match_type, --f = full, p = partial, s = simple
>          c.confupdtype AS on_update, --a = no action, r = restrict, c =
> cascade, n = set null, d = set default
>          c.confdeltype AS on_delete,  --a = no action, r = restrict, c =
> cascade, n = set null, d = set default
>          c.confrelid::regclass AS references_table,
>          ab.attname AS references_field
> FROM pg_catalog.pg_constraint c, pg_catalog.pg_attribute a,
> pg_catalog.pg_attribute ab
> WHERE conrelid::regclass = a.attrelid::regclass
> AND conkey[1] = a.attnum
> AND a.attrelid = ab.attrelid
> AND a.attnum = ab.attnum
> AND c.conrelid = 'products'::regclass
> AND c.contype='f';
>
>
>
> On Thu, 3 Mar 2016 at 09:46 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 03/02/2016 03:42 PM, David Binney wrote:
>      > Nice find dude and that should work joined to the other tables
>     ;). Once
>      > more thing, is there a matching conversion table for the human
>     readable
>      > result "a = no action" or will i have to "case" that stuff?
>
>     Not that I know of.
>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
> --
> Cheers David Binney


-- 
Adrian Klaver
adrian.klaver@aklaver.com