Subject: Re: Query about foreign key details for php framework
To: David Binney <donkeysoft@gmail.com>, pgsql-sql@postgresql.org
References: 
 <CAN123gmPUc2vutkE+H0FyPJSyf+xkHam2yuiGC2U8BTin6kaOA@mail.gmail.com>
 <56CFA136.4000508@aklaver.com>
 <CAN123gnvg5fC-SOBRwYT+eK6AOzfNU2+K4+AqjqJgPi9-Kh8Pw@mail.gmail.com>
 <56CFA4E1.8040204@aklaver.com>
 <CAN123gn7AL2mSpZitFb5bvF7VCbn0fVVv4qenyQj472P4AxdpQ@mail.gmail.com>
 <56CFC24A.6030508@aklaver.com>
 <CAN123g=5BbeEUz=q7w3_KdYZgirSxBL57V45sh4Aa5E3HCZ+Aw@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <56D06C02.5040107@aklaver.com>
Date: Fri, 26 Feb 2016 07:15:14 -0800
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:38.0) Gecko/20100101
 Thunderbird/38.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CAN123g=5BbeEUz=q7w3_KdYZgirSxBL57V45sh4Aa5E3HCZ+Aw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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.klaver@aklaver.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql