public inbox for [email protected]
help / color / mirror / Atom feedSpecifying columns returned by a function, when the function is in a SELECT column list?
2+ messages / 2 participants
[nested] [flat]
* Specifying columns returned by a function, when the function is in a SELECT column list?
@ 2024-11-19 15:47 Ron Johnson <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Ron Johnson @ 2024-11-19 15:47 UTC (permalink / raw)
To: 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!
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Specifying columns returned by a function, when the function is in a SELECT column list?
@ 2024-11-19 15:55 David G. Johnston <[email protected]>
parent: Ron Johnson <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2024-11-19 15:55 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-general
On Tue, Nov 19, 2024 at 8:48 AM Ron Johnson <[email protected]> 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-11-19 15:55 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-19 15:47 Specifying columns returned by a function, when the function is in a SELECT column list? Ron Johnson <[email protected]>
2024-11-19 15:55 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox