Want function to be called only once in query

I have a PL/pgSQL function that I want to call within a query, but the
function is fairly expensive to execute so I only want it executed once
within the query. However the planner seems to reorganize my query so that
it calls the function for every row.

We were previously on Pg 9.6 and this wasn't a problem then. But now that
we have upgraded to Pg 13, the behaviour has changed.

I thought that marking the function as STABLE would mean that the function
would only be called once within a query, but this doesn't seem to be the
case. (Note: the function isn't IMMUTABLE). I've also tried increasing the
cost of the function, but this doesn't make any difference.

From looking at previous posts I discovered that putting "offset 0" on the
function call in a "with" clause means that it only gets called once
(because then the Common Table Expression isn't combined with the rest of
the query).

This does work, however it seems rather a kludge (and might not work in
future versions of PostgreSQL).

There must be a "proper" way to get the planner to call a function only
once.

Postgres version: PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc
(GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

Here's a simple test case that demonstrates the issue:

create or replace function test_caching(v integer)
returns text
as
$BODY$
begin
raise NOTICE 'In test_caching(%) function', v;
return 'Test';
end
$BODY$
LANGUAGE plpgsql STABLE
COST 500;

select n, test_caching(7) from generate_series(1, 10) n;
-- test_caching(...) is called 10 times

with tc as (
select test_caching(7)
)
select n, tc.test_caching
from tc
cross join generate_series(1, 10) n;
-- test_caching(...) is called 10 times
-- (in Pg 9.6, test_caching(...) is only called once)

with tc as (
select test_caching(7) offset 0
)
select n, tc.test_caching
from tc
cross join generate_series(1, 10) n;
-- test_caching(...) is called once
-- works, but a kludge

Steve
--
Steve Pritchard
Database Developer

British Trust for Ornithology, The Nunnery, Thetford, Norfolk IP24 2PU, UK
Tel: +44 (0)1842 750050, fax: +44 (0)1842 750030
Registered Charity No 216652 (England & Wales) No SC039193 (Scotland)
Company Limited by Guarantee No 357284 (England & Wales)