agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: David Binney <donkeysoft@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Query about foreign key details for php framework
Date: Wed, 2 Mar 2016 06:56:41 -0800
Message-ID: <56D6FF29.1000309@aklaver.com> (raw)
In-Reply-To: <CAN123gmFAMdSVggkdg2-XMss6qmAALfhuMS8wdMqM4mpK7408A@mail.gmail.com>
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>
<56D45C99.4060008@aklaver.com>
<CAN123gmFAMdSVggkdg2-XMss6qmAALfhuMS8wdMqM4mpK7408A@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On 03/01/2016 05:38 PM, David Binney wrote:
> 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.
Short version:
http://www.postgresql.org/docs/9.5/interactive/catalog-pg-constraint.html
SELECT
*
FROM
pg_constraint
WHERE
conrelid = 'some_table'::regclass
AND
contype='f';
>
> On Tue, 1 Mar 2016 at 00:59 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> 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
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> > <mailto: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>
> <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
> >
> > --
> > Cheers David Binney
>
>
> --
> 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
view thread (30+ messages) latest in thread
Message-ID: <56D6FF29.1000309@aklaver.com>
Permalink: ../56D6FF29.1000309@aklaver.com/
Also on: postgresql.org/message-id/56D6FF29.1000309@aklaver.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@aklaver.com, donkeysoft@gmail.com
Subject: Re: Query about foreign key details for php framework
In-Reply-To: <56D6FF29.1000309@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox