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@xxxxxxxxxxxxxx> 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
