title: PostgreSQL - multiple aggregate queries from the same function call
body: <p>I have a function that returns a <code>setof</code> from a table:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE OR REPLACE FUNCTION get_assoc_addrs_from_bbl(_bbl text)
  RETURNS SETOF wow_bldgs AS $$
    SELECT bldgs.* FROM wow_bldgs AS bldgs
    ...
  $$ LANGUAGE SQL STABLE;
  </code></pre>
  
  <p>Here's a sample of what the table would return: </p>
  
  <p><a href="https://i.stack.imgur.com/xsOFx.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xsOFx.png" alt="enter image description here"></a></p>
  
  <p>Now I'm writing an "aggregate" function that will return only one row that with various (aggregated) data points about the table that this function returns. Here is my current working (&amp; naive) example:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT 
    count(distinct registrationid) as bldgs,
    sum(unitsres) as units,
    round(avg(yearbuilt), 1) as age,
    (SELECT first(corpname) FROM (
      SELECT unnest(corpnames) as corpname
      FROM get_assoc_addrs_from_bbl('3012380016')
      GROUP BY corpname ORDER BY count(*) DESC LIMIT 1
    ) corps) as topcorp,
    (SELECT first(businessaddr) FROM (
      SELECT unnest(businessaddrs) as businessaddr
      FROM get_assoc_addrs_from_bbl('3012380016')
      GROUP BY businessaddr ORDER BY count(*) DESC LIMIT 1
    ) rbas) as topbusinessaddr
  FROM get_assoc_addrs_from_bbl('3012380016') assocbldgs
  </code></pre>
  
  <p>As you can see, for the two "subqueries" that require a custom grouping/ordering method, I need to repeat the call to <code>get_assoc_addrs_from_bbl()</code>. Ideally, I'm looking for a structure that would avoid the repeated calls as the function requires a lot of processing and I want the capacity for an arbitrary number of subqueries. I've looked into CTEs and window expressions and the like but no luck.</p>
  
  <p>Any tips? Thank you!</p>