Re: Query which shows FK child columns? - Mailing list pgsql-general
| From | Jeff Ross |
|---|---|
| Subject | Re: Query which shows FK child columns? |
| Date | |
| Msg-id | 3c146287-5d90-2710-a449-077d01a97975@openvistas.net Whole thread Raw |
| In response to | Query which shows FK child columns? (Ron <ronljohnsonjr@gmail.com>) |
| Responses |
Re: Query which shows FK child columns?
|
| List | pgsql-general |
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
pgsql-general by date: