Re: Query about foreign key details for php framework

Nice find dude and that should work joined to the other tables ;). Once
more thing, is there a matching conversion table for the human readable
result "a = no action" or will i have to "case" that stuff?

On Thu, 3 Mar 2016 at 00:58 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:

> 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(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> > > <mailto:adrian(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com
> >>
> > >
> > > --
> > > Cheers David Binney
> >
> >
> > --
> > Adrian Klaver
> > adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> >
> > --
> > Cheers David Binney
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
--
Cheers David Binney