Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aavlm-0003lF-Qs for pgsql-sql@arkaria.postgresql.org; Wed, 02 Mar 2016 01:40:06 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aavlm-0006e4-DW for pgsql-sql@arkaria.postgresql.org; Wed, 02 Mar 2016 01:40:06 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aavkm-0005Q9-WF for pgsql-sql@postgresql.org; Wed, 02 Mar 2016 01:39:05 +0000 Received: from mail-vk0-x22c.google.com ([2607:f8b0:400c:c05::22c]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1aavkg-0001Y6-01 for pgsql-sql@postgresql.org; Wed, 02 Mar 2016 01:39:03 +0000 Received: by mail-vk0-x22c.google.com with SMTP id e6so187089620vkh.2 for ; Tue, 01 Mar 2016 17:38:57 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:references:in-reply-to:from:date:message-id:subject:to; bh=StrlprKE0C66+q0A0WM3I/wbHAZ23H1Z6FfYhegMEA0=; b=R+bKRui6uEShUWyt0CUwc4QIyIX75kOW/7HqfOvrOhEBG6h+eJUax7Zw/tuh8GyiRd vveJ/9xS9i/RuEnRYR3GBE9evlCmK2flyVl2DtwlbP6xgxHqFf4fJbiR3kzTEeT+wOrQ QZdu4khYIa0jE805Y/yDt/in5lvIWi2hLeB/a5XYTn2rXgHh1umE1ppgYv2oNC0xqO6K CiJXnpPmVuUWgttZh+IH1PGjraVhdpwmqoQb4MPtBXAvlHbCSUjrrEH5nivXACPkCjj8 QnvBg4pUjCnNCkUS1amzeZ79NGT0OtEPKWemFnZiAZjYmpnwzYIVc+sda3KxxUMqmE3z Vj4Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to; bh=StrlprKE0C66+q0A0WM3I/wbHAZ23H1Z6FfYhegMEA0=; b=OYqnsv9OBUKUXRKsseLLhvAoxaHcPUguNZqh2Crdxs7vZ5Z1AQgI3Rv7i8W7Ookj/o 5z3KoWnJ7zLhsH2u3sqsa31cqAIcsAzKCgeb+m0pmO7Kvg/qktQlwCiM1y8fMs+dirNx q5P7jayFzA4F85o8XEmO/+Gq0U9lzgm5fr6G4q295GE9LuqwGIWgMlm5KI31vSn3jaxg x3e3A8HxBm3T9tiLcOOPw93RDBfxInjEk/Q2MmpSf472BQAVRzh9cTaGTToUharqZsSv EPFnDalwpuBwwZh8zJJKJ0KanmtI0ROsbST3V0S46K/l755VAwjs4Pp80k/EajQIppbJ /9Vg== X-Gm-Message-State: AD7BkJJlaUYK4u8ZTpxeMhCeUMXFcwSMVhJnph2W4iCeWZyKAnO+z+vp2rO8wBjEDeoqoKoPXONMk9lhRVWAqw== X-Received: by 10.31.58.139 with SMTP id h133mr18183117vka.158.1456882737051; Tue, 01 Mar 2016 17:38:57 -0800 (PST) MIME-Version: 1.0 References: <56CFA136.4000508@aklaver.com> <56CFA4E1.8040204@aklaver.com> <56CFC24A.6030508@aklaver.com> <56D06C02.5040107@aklaver.com> <56D0C4B8.7020200@aklaver.com> <56D1BEA6.5020707@aklaver.com> <56D45C99.4060008@aklaver.com> In-Reply-To: <56D45C99.4060008@aklaver.com> From: David Binney Date: Wed, 02 Mar 2016 01:38:47 +0000 Message-ID: Subject: Re: Query about foreign key details for php framework To: Adrian Klaver , pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=001a11434d8c057fb6052d06f21f X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --001a11434d8c057fb6052d06f21f Content-Type: text/plain; charset=UTF-8 Hey Adrian, Yes, that is the problem of not being able to join on the table name, to obtain these any fields from that table. Also, it is for a framework which will be managing constrains/rules/adds/deletes etc. , so needs to know the constrain details against each table. On Tue, 1 Mar 2016 at 00:59 Adrian Klaver wrote: > 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 > > 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 > > > > -- > > Cheers David Binney > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Cheers David Binney --001a11434d8c057fb6052d06f21f Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hey Adrian,=C2=A0

Yes, that is the prob= lem of not being able to join on the table name, to obtain these any fields= from that table. Also, it is for a framework which will be managing constr= ains/rules/adds/deletes etc. , so needs to know the constrain details again= st each table.=C2=A0

On Tue, 1 Mar 2016 at 00:59 Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 02/28/2016 03:42 PM, David Binney wrote:
> Hey adrian,
>
>=C2=A0 =C2=A0 rc.constraint_name AS name,
>=C2=A0 =C2=A0 tc.constraint_type AS type,
>=C2=A0 =C2=A0 kcu.column_name,
>=C2=A0 =C2=A0 rc.match_option AS match_type,
>=C2=A0 =C2=A0 rc.update_rule AS on_update,
>=C2=A0 =C2=A0 rc.delete_rule AS on_delete,
>=C2=A0 =C2=A0 kcu.table_name AS references_table,
>=C2=A0 =C2=A0 kcu.column_name AS references_field,
>=C2=A0 =C2=A0 kcu.ordinal_position
>
> Those are the columns that i need as a minimum, but as you know they a= re
> all easy apart from the rules "on update" from the "RC&= quot; 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:
>
>=C2=A0 =C2=A0 =C2=A0On 02/26/2016 08:29 PM, David Binney wrote:
>=C2=A0 =C2=A0 =C2=A0 > Hey adrian,
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > You are correct that the distinct will chomp = the resultset down
>=C2=A0 =C2=A0 =C2=A0to the
>=C2=A0 =C2=A0 =C2=A0 > correct count, I am just concerned that there= will be cases where it
>=C2=A0 =C2=A0 =C2=A0 > might not be accurate between the "rc&qu= ot; and the "kcu" joins as
>=C2=A0 =C2=A0 =C2=A0there is
>=C2=A0 =C2=A0 =C2=A0 > no table reference. I have simplified the que= ry right down to
>=C2=A0 =C2=A0 =C2=A0just the
>=C2=A0 =C2=A0 =C2=A0 > join that i am unsure about. You can see belo= w that as soon as i
>=C2=A0 =C2=A0 =C2=A0add the
>=C2=A0 =C2=A0 =C2=A0 > rc.unique_constraint_name, the distinct is no= longer returning
>=C2=A0 =C2=A0 =C2=A0one row.
>=C2=A0 =C2=A0 =C2=A0 > In this case its fine because the rc values a= re the same and would
>=C2=A0 =C2=A0 =C2=A0 > distinct away, but there might be a case wher= e they are diferent
>=C2=A0 =C2=A0 =C2=A0and you
>=C2=A0 =C2=A0 =C2=A0 > would have two rows and not know which values= are correct?
>=C2=A0 =C2=A0 =C2=A0 >
>
>=C2=A0 =C2=A0 =C2=A0Well it comes down to the question that was asked s= everal times
>=C2=A0 =C2=A0 =C2=A0upstream:
>
>=C2=A0 =C2=A0 =C2=A0what is the information you want to see?
>
>=C2=A0 =C2=A0 =C2=A0I am not talking about a query, but a description o= f what attributes you
>=C2=A0 =C2=A0 =C2=A0want on what database objects.
>
>=C2=A0 =C2=A0 =C2=A0Also given, from previous post:
>
>=C2=A0 =C2=A0 =C2=A0"I am a little in the dark as well since this = is just a broken piece of
>=C2=A0 =C2=A0 =C2=A0ORM i am attempting to fix, in the framework."=
>
>=C2=A0 =C2=A0 =C2=A0Is this not something that should be discussed with= the framework
>=C2=A0 =C2=A0 =C2=A0developers, or are we already doing that:)?
>
>
>
>=C2=A0 =C2=A0 =C2=A0--
>=C2=A0 =C2=A0 =C2=A0Adrian Klaver
>=C2=A0 =C2=A0 =C2=A0adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com><= br> >
> --
> Cheers David Binney


--
Adrian Klaver
adrian.klave= r@aklaver.com
--
Cheers Da= vid Binney
--001a11434d8c057fb6052d06f21f--