"David G. Johnston" <david.g.johnston@gmail.com> writes:
> I am curious whether the SQL standard is imposing this immutable-only
> limitation or whether we choose to do it ourselves.
Fair question. What I read in SQL:2021 11.4 <column definition>
SR 10 is
10) If <generation clause> GC is specified, then:
a) Let GE be the <generation expression> contained in GC.
b) C is a generated column.
c) Every <column reference> contained in GE shall reference a base column of T.
d) GE shall not contain a possible source of non-determinism.
e) GE shall not contain a <routine invocation> whose subject routine possibly reads SQL-data.
f) GE shall not contain a <query expression>.
Now, (d) is referring to 9.16 "Potential sources of non-determinism",
which calls out a whole bunch of stuff that we would refer to as
either volatile or stable; for example 9.16 1) k) says that all
<datetime value function>s (that is, CURRENT_TIMESTAMP etc) are
potentially nondeterministic. So although the spec's two classes
of function stability
<deterministic characteristic> ::=
DETERMINISTIC
| NOT DETERMINISTIC
don't map exactly to our IMMUTABLE/STABLE/VOLATILE classification,
it seems correct to me to identify DETERMINISTIC with IMMUTABLE.
It's certainly indisputable that the letter of the spec forbids
CURRENT_TIMESTAMP in GENERATED expressions, and there's nothing
suggesting that hiding that within a user-defined function makes
it okay.
regards, tom lane