The attached PG function dba.get_fk_referenced_by() has been tested on PG
9.6 and 14.
A recursive bash function (also attached) calls the PG function, and
displays the whole tree of tables that the table in question depends on.
Output also attached.
Is there a better way to do this? (I'm limited to scripting languages and
what can be installed from RPM files.)
fk8 ()
{
local Tbl=$1
local Padding=$2
local -i Level=$3
local TblFKFld
local t
local ChldFKFld
local ChldIdx
local -i Cnt=0
local sql="SELECT this_column, REPLACE(downstream_table, '\"', ''),
downstream_column, downstream_index
FROM dba.get_fk_referenced_by('$Tbl');"
if [ $Level = 0 ]; then Level=1; fi
if [ -z $Padding ]; then Padding="+++"; fi
while IFS=$'\t' read -r -a R
do
Cnt=$((Cnt+1))
TblFKFld=${R[0]}
t=${R[1]}
ChldFKFld=${R[2]}
ChldIdx="${R[3]}"
printf "%10d%02d %s %-35s %-30s %-30s\n" $Level $Cnt $Padding $t
$ChldFKFld "$ChldIdx"
fk8 $t "${Padding}++++" $((Level*100+Cnt))
done < <(psql CDSLBXW -XtAF$'\t' -c "$sql")
if [[ $Level = 1 ]]
then
printf " %10d %s\n" $Level $Tbl
fi
}
/*
_p_table_name must be schema.table_name (Example: tms.document)
*/
CREATE OR REPLACE FUNCTION dba.get_fk_referenced_by(_p_table_name TEXT)
RETURNS TABLE (this_column TEXT
, downstream_table TEXT
, downstream_column TEXT
, downstream_index TEXT)
LANGUAGE plpgsql
AS
$$
DECLARE
_v_name TEXT;
_v_downstream_index_name NAME;
_c_downstream CURSOR FOR
select att2.attname::text AS this_column
,
cla2.relnamespace::regnamespace::text||'.'||co.conrelid::regclass::text AS
downstream_table
, co.conrelid AS downstream_oid
, att1.attname::text AS downstream_column
from pg_constraint co
inner join pg_class cla1
on co.confrelid = cla1.oid
inner join pg_class cla2
on co.conrelid = cla2.oid
inner join pg_attribute att1
on co.conrelid = att1.attrelid
inner join pg_attribute att2
on co.confrelid = att2.attrelid
where co.contype = 'f'
and cla1.relnamespace::regnamespace::text||'.'||cla1.relname =
_p_table_name
and att1.attnum = co.conkey[1]
and att2.attnum = co.confkey[1]
order by 2;
BEGIN
_v_name := TRIM(BOTH FROM _p_table_name);
PERFORM relname
FROM pg_class
WHERE relnamespace::regnamespace::text||'.'||relname = _v_name
AND relkind = 'r';
IF NOT FOUND THEN
downstream_table := _v_name;
downstream_column := 'does not exist';
downstream_index := NULL;
RETURN NEXT;
RETURN;
END IF;
FOR i IN _c_downstream
LOOP
this_column := i.this_column;
downstream_table := i.downstream_table;
downstream_column := i.downstream_column;
SELECT indexrelid::regclass::text
into downstream_index
FROM pg_index
INNER JOIN pg_attribute ON indexrelid = attrelid
WHERE indrelid = i.downstream_oid
AND attname = i.downstream_column;
IF downstream_index IS NULL THEN
downstream_index := 'ALERT!!! MISSING INDEX';
END IF;
RETURN NEXT;
END LOOP;
END;
$$;
==============================
$ . fk.sh && fk8 tms.batch
101 +++ cds.rel_x937bundle_batch batch_id
idx_cds_rel_x937bundle_batch
102 +++ tms.action batch_id
idx_action_batch_id
103 +++ tms.alerted_watchlist batch_id
ALERT!!! MISSING INDEX
104 +++ tms.batch_association batch_id
idx_batch_association_batch_id
105 +++ tms.batch_clearing_audit batch_id
idx_batch_clearing_audit_batch_id
106 +++ tms.batch_format batch_id
idx_batch_format_batch_id
10601 +++++++ tms.batch_format_item batch_format_id
idx_batch_format_item_batch_format_id
107 +++ tms.batch_item_mapping batch_id
idx_batch_item_mapping_batch_id
108 +++ tms.batch_trigger batch_id
idx_batch_trigger_batch_id
109 +++ tms.operator batch_id
idx_operator_batch_id
110 +++ tms.sub_batch batch_id
idx_sub_batch_batch_id
11001 +++++++ cds.cdssubbatch cdssubbatch_id
pk_cdssubbatch
11002 +++++++ cds.rel_x937bundle_sub_batch sub_batch_id
idx_cds_rel_x937bundle_sub_batch
11003 +++++++ tms.sub_transaction sub_batch_id
idx_sub_transaction_sub_batch_id
111 +++ tms.transaction batch_id
idx_transaction_batch_id
11101 +++++++ cds.cdstransaction cdstransaction_id
pk_cdstransaction
1110101 +++++++++++ cds.rel_cdstransaction_rejectrule cdstransaction_id
idx_cds_rel_cdstransaction_rejectrule
11102 +++++++ cds.rel_x937bundle_transaction transaction_id
idx_cds_rel_x937bundle_transaction
11103 +++++++ tms.document transaction_id
idx_document_transaction_id
1110301 +++++++++++ cds.cdsdocument cdsdocument_id
pk_cdsdocument
111030101 +++++++++++++++ cds.rel_cdsdocument_rejectrule cdsdocument_id
idx_cds_rel_cdsdocument_rejectrule_documentid
1110302 +++++++++++ tms.docformat document_id
idx_docformat_document_id
111030201 +++++++++++++++ tms.docformat_reference format_id
idx_docformat_reference_format_id
11103020101 +++++++++++++++++++ tms.docformat_item
reference_id idx_docformat_item_reference_id
1110303 +++++++++++ tms.doc_image document_id
idx_doc_image_document_id
1110304 +++++++++++ tms.doc_item_mapping document_id
idx_doc_item_mapping_document_id
1110305 +++++++++++ tms.mark_sense_detail document_id
idx_mark_sense_detail_document_id
11104 +++++++ tms.payment transaction_id
idx_payment_transaction_id
1110401 +++++++++++ cds.cdspayment cdspayment_id
pk_cdspayment
111040101 +++++++++++++++ cds.rel_cdspayment_rejectrule cdspayment_id
idx_cds_rel_cdspayment_rejectrule_paymentid
1110402 +++++++++++ tms.check payment_id
idx_check_payment_id
111040201 +++++++++++++++ tms.check_clearing_audit check_id
idx_check_clearing_audit_check_id
111040202 +++++++++++++++ tms.check_item_mapping check_id
idx_check_item_mapping_check_id
111040203 +++++++++++++++ tms.check_reference check_id
idx_check_reference_check_id
11104020301 +++++++++++++++++++ tms.check_item
reference_id idx_check_item_reference_id
111040204 +++++++++++++++ tms.gl_ticket_image check_id
idx_gl_ticket_image_check_id
1110403 +++++++++++ tms.chk_image payment_id
idx_chk_image_payment_id
1110404 +++++++++++ tms.chk_original_image payment_id
idx_chk_original_image_payment_id
1110405 +++++++++++ tms.credit_card payment_id
idx_credit_card_payment_id
1110406 +++++++++++ tms.float payment_id
idx_float_payment_id
11105 +++++++ tms.sub_transaction transaction_id
idx_sub_transaction_transaction_id
1 tms.batch
