Re: Query about foreign key details for php framework - Mailing list pgsql-sql

<div dir="ltr">Nice find dude and that should work joined to the other tables ;). Once more thing, is there a matching
conversiontable for the human readable result "<span><tt class="LITERAL"
style="font-size:12px;color:rgb(0,0,0);background-color:rgb(239,239,239)">a</tt><span
style="color:rgb(0,0,0);font-family:verdana,sans-serif;font-size:12px;background-color:rgb(239,239,239)"> =no action"
orwill i have to "case" that stuff? </span></span></div><br /><div class="gmail_quote"><div dir="ltr">On Thu, 3 Mar
2016at 00:58 Adrian Klaver <<a href="mailto:adrian.klaver@aklaver.com">adrian.klaver@aklaver.com</a>> wrote:<br
/></div><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On
03/01/201605:38 PM, David Binney wrote:<br /> > Hey Adrian,<br /> ><br /> > Yes, that is the problem of not
beingable to join on the table name, to<br /> > obtain these any fields from that table. Also, it is for a
framework<br/> > which will be managing constrains/rules/adds/deletes etc. , so needs to<br /> > know the
constraindetails against each table.<br /><br /> Short version:<br /><br /><a
href="http://www.postgresql.org/docs/9.5/interactive/catalog-pg-constraint.html"rel="noreferrer"
target="_blank">http://www.postgresql.org/docs/9.5/interactive/catalog-pg-constraint.html</a><br/><br /> SELECT<br />  
     *<br /> FROM<br />         pg_constraint<br /> WHERE<br />         conrelid = 'some_table'::regclass<br /> AND<br
/>        contype='f';<br /><br /> ><br /> > On Tue, 1 Mar 2016 at 00:59 Adrian Klaver <<a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a><br /> > <mailto:<a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a>>> wrote:<br /> ><br />
>    On 02/28/2016 03:42 PM, David Binney wrote:<br /> >      > Hey adrian,<br /> >      ><br /> >   
 >    rc.constraint_name AS name,<br /> >      >    tc.constraint_type AS type,<br /> >      >   
kcu.column_name,<br/> >      >    rc.match_option AS match_type,<br /> >      >    rc.update_rule AS
on_update,<br/> >      >    rc.delete_rule AS on_delete,<br /> >      >    kcu.table_name AS
references_table,<br/> >      >    kcu.column_name AS references_field,<br /> >      >   
kcu.ordinal_position<br/> >      ><br /> >      > Those are the columns that i need as a minimum, but as
youknow<br /> >     they are<br /> >      > all easy apart from the rules "on update" from the "RC" table.<br
/>><br /> >     I am not following, update_rule is just a field in<br /> >   
 information_schema.referential_constraints,how is it any harder then<br /> >     delete_rule?<br /> ><br />
>    The issue from what I understand is that<br /> >     information_schema.referential_constraints does not
havea table_name<br /> >     field to constrain the information to a particular table. This leads<br /> >   
 backto the overriding question, what is the purpose of the query? I<br /> >     suspect it for use by the framework
toset up attributes of a model<br /> >     based on a table, is that correct?<br /> ><br /> >      ><br />
>     > I did start having a crack at the catalog tables but that is pretty<br /> >      > complicated.<br
/>>      ><br /> >      > On Sun, 28 Feb 2016 at 01:21 Adrian Klaver<br /> >     <<a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a> <mailto:<a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a>><br /> >      >
<mailto:<ahref="mailto:adrian.klaver@aklaver.com" target="_blank">adrian.klaver@aklaver.com</a><br /> >   
 <mailto:<ahref="mailto:adrian.klaver@aklaver.com" target="_blank">adrian.klaver@aklaver.com</a>>>>
wrote:<br/> >      ><br /> >      >     On 02/26/2016 08:29 PM, David Binney wrote:<br /> >      >   
 > Hey adrian,<br /> >      >      ><br /> >      >      > You are correct that the distinct will
chompthe resultset<br /> >     down<br /> >      >     to the<br /> >      >      > correct count, I
amjust concerned that there will be<br /> >     cases where it<br /> >      >      > might not be accurate
betweenthe "rc" and the "kcu" joins as<br /> >      >     there is<br /> >      >      > no table
reference.I have simplified the query right down to<br /> >      >     just the<br /> >      >      >
jointhat i am unsure about. You can see below that as<br /> >     soon as i<br /> >      >     add the<br />
>     >      > rc.unique_constraint_name, the distinct is no longer returning<br /> >      >     one
row.<br/> >      >      > In this case its fine because the rc values are the same<br /> >     and would<br
/>>      >      > distinct away, but there might be a case where they are<br /> >     diferent<br /> > 
   >     and you<br /> >      >      > would have two rows and not know which values are correct?<br />
>     >      ><br /> >      ><br /> >      >     Well it comes down to the question that was asked
severaltimes<br /> >      >     upstream:<br /> >      ><br /> >      >     what is the information
youwant to see?<br /> >      ><br /> >      >     I am not talking about a query, but a description of
what<br/> >     attributes you<br /> >      >     want on what database objects.<br /> >      ><br />
>     >     Also given, from previous post:<br /> >      ><br /> >      >     "I am a little in the
darkas well since this is just a<br /> >     broken piece of<br /> >      >     ORM i am attempting to fix, in
theframework."<br /> >      ><br /> >      >     Is this not something that should be discussed with the
framework<br/> >      >     developers, or are we already doing that:)?<br /> >      ><br /> >     
><br/> >      ><br /> >      >     --<br /> >      >     Adrian Klaver<br /> >      > <a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a> <mailto:<a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a>><br /> >     <mailto:<a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a> <mailto:<a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a>>><br /> >      ><br />
>     > --<br /> >      > Cheers David Binney<br /> ><br /> ><br /> >     --<br /> >     Adrian
Klaver<br/> >     <a href="mailto:adrian.klaver@aklaver.com" target="_blank">adrian.klaver@aklaver.com</a>
<mailto:<ahref="mailto:adrian.klaver@aklaver.com" target="_blank">adrian.klaver@aklaver.com</a>><br /> ><br />
>--<br /> > Cheers David Binney<br /><br /><br /> --<br /> Adrian Klaver<br /><a
href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a><br /></blockquote></div><div
dir="ltr">--<br /></div><div dir="ltr">Cheers David Binney</div>