Re: [HACKERS] Re: [HACKERS] generated columns

On 12/30/17 16:04, Joe Conway wrote:
> +<para>
> + The generation expression can refer to other columns in the table, but
> + not other generated columns. Any functions and operators used must be
> + immutable. References to other tables are not allowed.
> +</para>
>
> Question -- when the "stored" kind of generated column is implemented,
> will the immutable restriction be relaxed? I would like, for example, be
> able to have a stored generated column that executes now() whenever the
> row is written/rewritten.

That restriction is from the SQL standard, and I think it will stay.
The virtual vs. stored choice is an optimization, but not meant to
affect semantics. For example, you might want to automatically
substitute a precomputed generated column into an expression, but that
will become complicated and confusing if the expression is not
deterministic.

Another problem with your example is that a stored generated column
would only be updated if a column it depends on is updated. So a column
whose generation expression is just now() would never get updated.

Maybe some of this could be relaxed at some point, but we would have to
think it through carefully. For now, a trigger would still be the best
implementation for your use case, I think.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services