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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.