PostgreSQL reuse function call result

Question

I want to reuse an expensive function call in PostgreSQL:

SELECT name,
  expensive_function(bar) as bars,
  array_length(bars) as total
FROM foos
GROUP BY name

but of course bars isn't a column so the following error is raised:

ERROR:  column "bars" does not exist

Is there a syntax or trick that will make this work?

@JustKim Reuse meaning run the function once per row and use the resultant value for multiple return columns on that row. — user1454117
– user1454117, Commented Mar 6, 2014 at 20:19

Catherine Devlin · Accepted Answer · 2014-03-06 19:01:32Z

2

A common table expression (CTE) would work:

WITH subqry AS
  ( SELECT expensive_function(bar) AS bars
    FROM   foos )
SELECT bars,
       array_length(bars)
FROM   subqry;

answered Mar 6, 2014 at 19:01

Catherine Devlin

7,8032 gold badges27 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1