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