From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | David Binney <donkeysoft(at)gmail(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Query about foreign key details for php framework |
Date: | 2016-02-26 15:15:14 |
Message-ID: | 56D06C02.5040107@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Binney | 2016-02-26 18:47:36 | Re: Query about foreign key details for php framework |
Previous Message | Tom Lane | 2016-02-26 15:00:05 | Re: Subselect left join / not exists() |