1

Say I have a function which returns an array. Can I access its items inline inside a select query? I can not use sub-querying since function relies on query parameters. And I can not declare outside variable for the same reason.

I expect somewhat like this:

select foo,bar, function(foo,bar)[1] as array_item_1, function (foo,bar)[2]  as array_item_2 from table...

Currently I return function(foo,bar) as array_items and iterate it in application which is really cumbersome. I want db to return prepared outputs.

Thanks.

Improve this question

1 Answer 1

Reset to default
1

Can I access its items inline inside a select query?

Yes, but you need an extra pair of parentheses (as documented in the manual)

select foo, bar, 
       (function(foo,bar))[1] as array_item_1, 
       (function(foo,bar))[2] as array_item_2 
from table...

However, unless the function is defined as stable or immutable, each expression will call the function. So in the above, if the function is defined as volatile (the default!) then it would be called twice. In that case using a derived table is a better approach:

select foo, bar, 
       result[1] as array_item_1, 
       result[2] as array_item_2 
from ( 
  select foo, bar, function(foo,bar) as result
  from table...
) as t
Improve this answer
1
  • Thank you very much! :) Commented Apr 29, 2020 at 9:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.