Re: Query which shows FK child columns?

On 11/14/19 11:49 AM, Ron wrote:
> v9.6.16
>
> I have a query which shows the parents and children in FK relations, 
> along with the parent column name, but can't seem to find the child 
> column names.
>
> Is there a way to find the child column names without having to dig 
> into pg_constraint?
>
> Thanks
>

I do not think you can do this without using pg_constraint.

I've been using this function to display those FKs.  The original code 
isn't mine but as I recall I had to tweak it a little.

This is on 10 and I can't remember if this was used on 9.6 but I'd be 
surprised if any of this won't work on 9.6.

client@cargotel_dev> \sf cargotel_common.show_foreign_keys(text)
CREATE OR REPLACE FUNCTION cargotel_common.show_foreign_keys(tablename text)
  RETURNS TABLE(table1 text, column1 text, type text, table2 text, 
column2 text)
  LANGUAGE plpgsql
AS $function$
     declare
         schemaname text;
     begin
         select into schemaname current_schema();
         return query
         execute format('
         select
             conrelid::regclass::text as table1,
             a.attname::text as column1,
             t.typname::text as type,
             confrelid::regclass::text as table2,
             af.attname::text as column2
         from
             pg_attribute af,
             pg_attribute a,
             pg_type t,
             (
                 select
                     conrelid,
                     confrelid,
                     conkey[i] as conkey,
                     confkey[i] as confkey
                 from (
                     select
                         conrelid,
                         confrelid,
                         conkey,
                         confkey,
                         generate_series(1,array_upper(conkey,1)) as i
                     from
                         pg_constraint
                     where contype = ''f''
                     )
                 ss) ss2
         where
             af.attnum = confkey and
             af.attrelid = confrelid and
             a.attnum = conkey and
             a.attrelid = conrelid and
             a.atttypid = t.oid and
             confrelid::regclass = ''%I.%I''::regclass
          order by 1,2;',schemaname,tablename);
     end;
$function$

I use column headings "table 1, column1, table2, column2" but It's easy 
enough to tweak the column labels.

Example:


client@cargotel_dev> \d+ ref_acct_cache
                                                  Table 
"client.ref_acct_cache"
  Column │  Type   │ Collation │ Nullable │ Default                   │ 
Storage  │ Stats target │ Description
────────┼─────────┼───────────┼──────────┼────────────────────────────────────────────┼──────────┼──────────────┼─────────────
  id     │ integer │           │ not null │ 
nextval('ref_acct_cache_id_seq'::regclass) │ plain │              │
  descr  │ text    │           │ 
│                                            │ extended │              │
Indexes:
     "ref_acct_cache_pkey" PRIMARY KEY, btree (id)
Referenced by:
     TABLE "acct_cache" CONSTRAINT 
"acct_cache_type_id_ref_acct_cache_id_fk" FOREIGN KEY (type_id) 
REFERENCES ref_acct_cache(id)


client@cargotel_dev> select * from 
cargotel_common.show_foreign_keys('ref_acct_cache');
    table1   │ column1 │ type │     table2     │ column2
────────────┼─────────┼──────┼────────────────┼─────────
  acct_cache │ type_id │ int4 │ ref_acct_cache │ id
(1 row)


client@cargotel_dev> \d+ acct_cache
                                                              Table 
"client.acct_cache"
     Column     │           Type           │ Collation │ Nullable 
│                Default                 │ Storage  │ Stats target │ 
Description
───────────────┼──────────────────────────┼───────────┼──────────┼────────────────────────────────────────┼──────────┼──────────────┼─────────────
  id            │ integer                  │           │ not null │ 
nextval('acct_cache_id_seq'::regclass) │ plain    │              │
  type_id       │ integer                  │           │ 
│                                        │ plain    │ │
  prefix        │ text                     │           │ 
│                                        │ extended │ │
  data          │ text                     │           │ 
│                                        │ extended │ │
  amount        │ numeric                  │           │ 
│                                        │ main     │ │
  timestamp     │ timestamp with time zone │           │ 
│                                        │ plain    │ │
  check_number  │ text                     │           │ 
│                                        │ extended │ │
  client_number │ text                     │           │ 
│                                        │ extended │ │
  check_date    │ date                     │           │ 
│                                        │ plain    │ │
Indexes:
     "acct_cache_pkey" PRIMARY KEY, btree (id)
     "acct_cache_prefix_type_id_data_idx" btree (prefix, type_id, data)
     "acct_cache_type_id_idx" btree (type_id)
Foreign-key constraints:
     "acct_cache_type_id_ref_acct_cache_id_fk" FOREIGN KEY (type_id) 
REFERENCES ref_acct_cache(id)
Referenced by:
     TABLE "load_trx" CONSTRAINT 
"load_trx_ar_voucher_id_acct_cache_id_fk" FOREIGN KEY (ar_voucher_id) 
REFERENCES acct_cache(id)
     TABLE "loadacct_link" CONSTRAINT 
"loadacct_link_acct_cache_id_acct_cache_id_fk" FOREIGN KEY 
(acct_cache_id) REFERENCES acct_cache(id)
     TABLE "qb_invoice_incomplete" CONSTRAINT 
"qb_invoice_incomplete_acct_cache_id_acct_cache_id_fk" FOREIGN KEY 
(acct_cache_id) REFERENCES acct_cache(id)
     TABLE "qb_payment_log" CONSTRAINT 
"qb_payment_log_acct_cache_id_acct_cache_id_fk" FOREIGN KEY 
(acct_cache_id) REFERENCES acct_cache(id)


And as a bonus:

client@cargotel_dev> select * from 
cargotel_common.show_foreign_keys('acct_cache');
         table1         │    column1    │ type │   table2 │ column2
───────────────────────┼───────────────┼──────┼────────────┼─────────
  loadacct_link         │ acct_cache_id │ int4 │ acct_cache │ id
  load_trx              │ ar_voucher_id │ int4 │ acct_cache │ id
  qb_invoice_incomplete │ acct_cache_id │ int4 │ acct_cache │ id
  qb_payment_log        │ acct_cache_id │ int4 │ acct_cache │ id
(4 rows)

Hope that helps!

Jeff