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

From: Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Specifying columns returned by a function, when the function is in a SELECT column list?
Date: 2024-11-19 15:47:52
Message-ID: CANzqJaBWNharGuTCY1OCzgU7S5zOCdKS84fCW=X8=AiVy1ibyg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2024-11-19 15:55:39 Re: Specifying columns returned by a function, when the function is in a SELECT column list?
Previous Message Moreno Andreo 2024-11-19 13:30:37 Re: Functions and Indexes