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

<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>