Re: Query about foreign key details for php framework

That is exactly the desired result, but in my db it is returning 2k rows
with exactly the same query, even filtered to a specific table.

On Sat, 27 Feb 2016 at 01:16 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 02/25/2016 07:19 PM, David Binney wrote:
> > Ah sorry adrian,
> >
> > I am a little in the dark as well since this is just a broken piece of
> > ORM i am attempting to fix, in the framework. So, maybe if you could
> > help to reproduce that select list as a start that would be great. But,
> > I am suspecting they were trying to pull similar datasets from mysql or
> > postgres as an end goal.
> >
>
> Alright I ran the Postgres query you provided and it threw an error:
>
> ERROR: missing FROM-clause entry for table "cu"
> LINE 26: cu.ordinal_position;
>
> in the ORDER BY clause. Changing cu.ordinal_position to
> kcu.ordinal_position obtained a result when run for a table in one of my
> databases:
>
> production=# 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 = 'projection'
> AND rc.constraint_schema = 'public'
> AND tc.constraint_type = 'FOREIGN KEY'
> ORDER BY
> rc.constraint_name,
> kcu.ordinal_position;
>
> -[ RECORD 1 ]----+------------
> name | con_fkey
> type | FOREIGN KEY
> column_name | c_id
> match_type | NONE
> on_update | CASCADE
> on_delete | CASCADE
> references_table | projection
> references_field | c_id
> ordinal_position | 1
> -[ RECORD 2 ]----+------------
> name | con_fkey
> type | FOREIGN KEY
> column_name | c_id
> match_type | NONE
> on_update | CASCADE
> on_delete | CASCADE
> references_table | projection
> references_field | c_id
> ordinal_position | 1
> -[ RECORD 3 ]----+------------
> name | pno_fkey
> type | FOREIGN KEY
> column_name | p_item_no
> match_type | NONE
> on_update | CASCADE
> on_delete | CASCADE
> references_table | projection
> references_field | p_item_no
>
> If this is not the desired result, then we will need more information.
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
--
Cheers David Binney