Re: ERROR: only immutable functions supported in continuous aggregate view

Ok, now I understand, thank you.

My solution is to create materialized view with intermediate values and
the create a normal view on top that uses the intermediate values and
the jsonb_build_object function to create the desired result.

Kind regards,

Martijn de Munnik

On 2023-03-11 16:47, Tom Lane wrote:
> Martijn de Munnik <martijndemunnik(at)protonmail(dot)com> writes:
>> The error is caused by the jsonb_build_object function, is there any
>> reason why this function is not IMMUTABLE?
> It can invoke arbitrary datatype output functions, some of which are
> not immutable. We unfortunately lack any infrastructure that would
> allow reasoning that "all the types used in this specific invocation
> have immutable output functions, so it'd be OK to consider this
> invocation immutable". So we have to assume the worst when labeling
> jsonb_build_object. (Even if we had such infrastructure, I doubt
> it would save you in this use-case, because it looks like some of
> the values you're converting are timestamps, which respond to the
> DateStyle and TimeZone settings.)
>
> You could, on these grounds, argue that jsonb_build_object and other
> functions with this problem actually need to be VOLATILE. But we've
> established a project convention that I/O functions should be at
> worst STABLE, allowing calling functions to be STABLE as well.
>
> regards, tom lane