Subject: Specifying columns returned by a function, when the function is in a SELECT column list?

From: Ron Johnson <[email-masked]>
Date: Tue, 19 Nov 2024 10:47:52 -0500

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!

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 19 Nov 2024 08:55:39 -0700

On Tue, Nov 19, 2024 at 8:48 AM Ron Johnson <[email-masked]> wrote:

> 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?
>

You don't/cannot.  And with lateral you shouldn't find the need to hack
around it either.  Non-scalar function calls in the select clause are now
obsolete.

In the select clause the function call returns a single-column of composite
type with the names of the fields in the composite already known.  As the
query scope where you invoke the function you should only use the
composite.  If you need to dive into its fields you'd need a subquery -
ideally one that doesn't risk being optimized away due to a
multiple-evaluation hazard.

David J.