The issue here, though, is that it works as an expression for an index, but doesn't work as a generated column unless I explicitly cast it to text (which should have happened implicitly anyways). (The cast is turning a non-immutable expression to be immutable.)
I'm also able to make generated fields for the individual function calls, but concatenation doesn't work without the explicit cast.
Jim
On September 8, 2023 11:11:42 AM EDT, "David G. Johnston" <david.g.johnston@gmail.com> wrote:
On Thursday, September 7, 2023, PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 18097
Logged by: Jim Keener
Email address: jim@jimkeener.com
PostgreSQL version: 15.0
Operating system: Linux
Description:
However, the following DOES NOT work with an error of (ERROR: generation
expression is not immutable):
* alter table test_table add created_local text GENERATED ALWAYS AS
(EXTRACT(isoyear FROM created_at AT TIME ZONE 'America/New_York') || '|' ||
EXTRACT(week FROM created_at AT TIME ZONE 'America/New_York')) STORED;
Given that casting shouldn't "increase" the immutability of an expression,
and expression indexes need also be immutable afaik, I think that there is a
bug somewhere here?
Casting very much can be a non-immutable activity, dates being the prime example, and I presume going from numeric to text is indeed defined to be stable hence the error. This is probably due to needing to consult locale for deciding how to represent the decimal places divider. This is one of the few places, assuming you write the function to set an environment fixing locale to some know value like you did with the time zones, where creating an immutable function around a stable expression makes sense.
David J.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.