Re: Strange behavior of function date_trunc

Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com> writes:
> On 5/5/21 3:23 PM, Pavel Luzanov wrote:
>> It is very likely that the date_trunc function in the following example
>> is executed for each line of the query. Although it marked as a STABLE
>> and could only be called once.

> It could, but that's just an option - the database may do that, but it's
> not required to do it. In this case it might be beneficial, but it'd
> make the planner more complex etc.

Yeah, there simply is not any provision for caching the results of
stable functions in the way Pavel seems to be imagining. People
have played around with patches for that, but nothing's been accepted.

> You can use CTE to execute it just once, I think:
> with x as (select date_trunc('day', '2021-04-01'::timestamptz) as x)
> select * from t where a > (select x from x);

Actually it's sufficient to write

select * from t where a > (select date_trunc('day', '2021-04-01'::timestamptz))

Postgres interprets that as an uncorrelated sub-select, so it's only done
once per outer query. I think that these days, the CTE form would be
flattened into that anyway (without MATERIALIZED).

regards, tom lane