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.
<Redacted> lobster!
