From: | David Binney <donkeysoft(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Query about foreign key details for php framework |
Date: | 2016-03-03 01:59:13 |
Message-ID: | CAN123gkeQX03CXonj7OTyW4cSB4W73kLTQC9K4zWyV2sMgmr2g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
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> 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
>
--
Cheers David Binney
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-03-03 14:17:58 | Re: Query about foreign key details for php framework |
Previous Message | Adrian Klaver | 2016-03-02 23:45:28 | Re: Query about foreign key details for php framework |