It's trivial to specify columns when a table-returning function is the FROM clause, but how does one specify columns when the table-returning function is a column in a SELECT clause?
I don't have the proper vocabulary to know what to Google for.
Examples below:
Easy:
cdsbmop=# select downstream_table, downstream_column, downstream_index
cdsbmop-# from dba.get_fk_referenced_by('cdsschema.retention_policy_code');
downstream_table | downstream_column | downstream_index
---------------------------------------+--------------------------+-----------------------------------------------------
cdsschema.bank_item_type_ret | retention_policy_code_id | ALERT!!! MISSING INDEX
cdsschema.bank_item_type_ret_hist | retention_policy_code_id | ALERT!!! MISSING INDEX
cdsschema.customer_item_type_ret | retention_policy_code_id | idx_customer_item_type_ret_retention_policy_code_id
cdsschema.customer_item_type_ret_hist | retention_policy_code_id | ALERT!!! MISSING INDEX
cdsschema.ex_data_partition_policy | retention_policy_code_id | ALERT!!! MISSING INDEX
cdsschema.lockbox_item_type_ret | retention_policy_code_id | idx_lockbox_item_type_ret_retention_policy_code_id
cdsschema.lockbox_item_type_ret_hist | retention_policy_code_id | ALERT!!! MISSING INDEX
(7 rows)
cdsbmop=# select table_name
from dba.latest_vacuum_and_analyze
where table_name = 'retention_policy_code';
table_name
-----------------------
retention_policy_code
(1 row)
Here, I want to only specify some of the dba.get_fk_referenced_by() columns:
cdsbmop=# select dba.get_fk_referenced_by('cdsschema.'||table_name)
from dba.latest_vacuum_and_analyze where table_name = 'retention_policy_code';
get_fk_referenced_by
------------------------------------------------------------------------------------------------------------------------------------------
(retention_policy_code_id,cdsschema.bank_item_type_ret,retention_policy_code_id,"ALERT!!! MISSING INDEX")
(retention_policy_code_id,cdsschema.bank_item_type_ret_hist,retention_policy_code_id,"ALERT!!! MISSING INDEX")
(retention_policy_code_id,cdsschema.customer_item_type_ret,retention_policy_code_id,idx_customer_item_type_ret_retention_policy_code_id)
(retention_policy_code_id,cdsschema.customer_item_type_ret_hist,retention_policy_code_id,"ALERT!!! MISSING INDEX")
(retention_policy_code_id,cdsschema.ex_data_partition_policy,retention_policy_code_id,"ALERT!!! MISSING INDEX")
(retention_policy_code_id,cdsschema.lockbox_item_type_ret,retention_policy_code_id,idx_lockbox_item_type_ret_retention_policy_code_id)
(retention_policy_code_id,cdsschema.lockbox_item_type_ret_hist,retention_policy_code_id,"ALERT!!! MISSING INDEX")
(7 rows)
-- Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
