Re: Query about foreign key details for php framework

Hey Stuart,

Well I should be more specific that it is not valid at all, because the
joins to those tables are incorrect. When joining between those three
tables it is not joining correctly, which means the data is potentially
invalid, however its close, looking if you know what I mean.

On Fri, 26 Feb 2016 at 15:18 Stuart <sfbarbee(at)gmail(dot)com> wrote:

> 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
>>
> --
Cheers David Binney