Re: Query about foreign key details for php framework - Mailing list pgsql-sql

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