CODEWITHSUNDEEP
sqlpostgresql
cclark
cclark

Reputation: 1412

How to display the contents and size of an array storing the results of a subquery in postgres?

I'd like to display the contents of an array in one column and the size of an array in the next column. The array is generated off a subselect in my query. Is there a way I can reference the same array twice so I do not have to repeat the subselect query?

A simplified version of the query which works now is: 

SELECT b.id, 
       (array_to_string(array 
       ( 
                select   currency_id 
                FROM     RECORD 
                WHERE    business_id = b.id 
                GROUP BY business_id, 
                         currency_id), '|')) AS currencies, 
       array_length(ARRAY 
       ( 
                SELECT   currency_id 
                FROM     RECORD 
                WHERE    business_id = b.id 
                GROUP BY business_id, 
                         currency_id), 1) AS num_currencies 
FROM   business b

from which I expect to get a result like:

 id  | currencies | num_currencies
-----+------------+----------------
  53 | 38|36      |              2
 235 | 36         |              1
 289 |            |
(3 rows)

Does the query planner see that it is the exact same subquery an optimize out running the second query or is there a better approach to getting this result?

Upvotes: 0

Views: 33

Answers (1)

user330315
user330315

Reputation:

You don't need the sub-select twice. If you put your "main" query into a derived table, you can simply re-use the array

SELECT id, 
       array_to_string(currencies, '|') as currencies, 
       array_length(currencies, 1) as num_currencies
FROM (
  SELECT b.id, 
         array(select   currency_id 
               FROM     record
               WHERE    business_id = b.id 
               GROUP BY business_id, 
                        currency_id) AS currencies 
  FROM   business b
) t;

Whether or not the optimizer detects the identical sub-queries should be visible from the execution plan.

But I think you can simplify this even more: 

select b.id, 
       string_agg(r.currency_id::text, '|') as currencies,
       count(*) as num_currencies
from business b
  join record r on r.business_id = r.id
group by b.id
order by b.id;

Upvotes: 1

Related Questions