Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Ron Johnson
Date:
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!

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

From
"David G. Johnston"
Date:
On Tue, Nov 19, 2024 at 8:48 AM Ron Johnson <ronljohnsonjr@gmail.com> 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.