Re: Query which shows FK child columns?

[Jeff Ross <jross@xxxxxxxxxxxxxx>]

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