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>
 <56D06C02.5040107@aklaver.com>
 <CAN123gkwOujiAO3pwLqtouitTi0ai1J75iF1tNgfx6Hrrxh-=w@mail.gmail.com>
 <56D0C4B8.7020200@aklaver.com>
 <CAN123gkXG6eMuAUnGzJiTzHO7J-PLUACR=pP2xd3bD-d5r_8jw@mail.gmail.com>
 <56D1BEA6.5020707@aklaver.com>
 <CAN123gkuC7AwEqa2+xCEViAFF58J18YeZDJfingx6a7Sf5L3eg@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <56D45C99.4060008@aklaver.com>
Date: Mon, 29 Feb 2016 06:58:33 -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: 
 <CAN123gkuC7AwEqa2+xCEViAFF58J18YeZDJfingx6a7Sf5L3eg@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/28/2016 03:42 PM, David Binney wrote:
> Hey adrian,
>
>    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
>
> Those are the columns that i need as a minimum, but as you know they are
> all easy apart from the rules "on update" from the "RC" table.

I am not following, update_rule is just a field in 
information_schema.referential_constraints, how is it any harder then 
delete_rule?

The issue from what I understand is that 
information_schema.referential_constraints does not have a table_name 
field to constrain the information to a particular table. This leads 
back to the overriding question, what is the purpose of the query? I 
suspect it for use by the framework to set up attributes of a model 
based on a table, is that correct?

>
> I did start having a crack at the catalog tables but that is pretty
> complicated.
>
> On Sun, 28 Feb 2016 at 01:21 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 02/26/2016 08:29 PM, David Binney wrote:
>      > Hey adrian,
>      >
>      > You are correct that the distinct will chomp the resultset down
>     to the
>      > correct count, I am just concerned that there will be cases where it
>      > might not be accurate between the "rc" and the "kcu" joins as
>     there is
>      > no table reference. I have simplified the query right down to
>     just the
>      > join that i am unsure about. You can see below that as soon as i
>     add the
>      > rc.unique_constraint_name, the distinct is no longer returning
>     one row.
>      > In this case its fine because the rc values are the same and would
>      > distinct away, but there might be a case where they are diferent
>     and you
>      > would have two rows and not know which values are correct?
>      >
>
>     Well it comes down to the question that was asked several times
>     upstream:
>
>     what is the information you want to see?
>
>     I am not talking about a query, but a description of what attributes you
>     want on what database objects.
>
>     Also given, from previous post:
>
>     "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."
>
>     Is this not something that should be discussed with the framework
>     developers, or are we already doing that:)?
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
> --
> Cheers David Binney


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