Re: Query about foreign key details for php framework - Mailing list pgsql-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*
FROMpg_constraint
WHEREconrelid = 'some_table'::regclass
ANDcontype='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