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

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@gmail.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@gmail.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