Re: Query about foreign key details for php framework

On 03/02/2016 05:59 PM, David Binney wrote:
> Hey Adrian,
>
> Dude you are a legend. I have attempted to mod the query to use just
> those tables and i think its ok just need confirmation. It would be nice
> if it had the full text descriptors but I can always use a case to fix
> it up if necessary.

I can confirm it works and returns values. Since I am not entirely sure
what the framework needs I cannot go any further then that.

>
> SELECT c.conrelid::regclass::text AS table_name,
> c.contype AS constraint_type, --c = check constraint, f =
> foreign key constraint, p = primary key constraint, u = unique
> constraint, t = constraint trigger, x = exclusion constraint
> a.attname AS column_name,
> c.confmatchtype AS match_type, --f = full, p = partial, s = simple
> c.confupdtype AS on_update, --a = no action, r = restrict, c =
> cascade, n = set null, d = set default
> c.confdeltype AS on_delete, --a = no action, r = restrict, c =
> cascade, n = set null, d = set default
> c.confrelid::regclass AS references_table,
> ab.attname AS references_field
> FROM pg_catalog.pg_constraint c, pg_catalog.pg_attribute a,
> pg_catalog.pg_attribute ab
> WHERE conrelid::regclass = a.attrelid::regclass
> AND conkey[1] = a.attnum
> AND a.attrelid = ab.attrelid
> AND a.attnum = ab.attnum
> AND c.conrelid = 'products'::regclass
> AND c.contype='f';
>
>
>
> On Thu, 3 Mar 2016 at 09:46 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> On 03/02/2016 03:42 PM, David Binney wrote:
> > 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?
>
> Not that I know of.
>
>
>
>
> --
> 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