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(at)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(at)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(at)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(at)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(at)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