Re: Query about foreign key details for php framework

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
On Feb 26, 2016 04:38, "David Binney" <donkeysoft(at)gmail(dot)com> 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??
>
> -------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
>