Hi,
Currently, we have an option to drop the expression of stored generated columns
as:
ALTER [ COLUMN ] column_name DROP EXPRESSION [ IF EXISTS ]
But don't have support to update that expression. The attached patch provides
that as:
ALTER [ COLUMN ] column_name SET EXPRESSION expression
Note that this form of ALTER is meant to work for the column which is already
generated. It then changes the generation expression in the catalog and rewrite
the table, using the existing table rewrite facilities for ALTER TABLE.
Otherwise, an error will be reported.
To keep the code flow simple, I have renamed the existing function that was in
use for DROP EXPRESSION so that it can be used for SET EXPRESSION as well,
which is a similar design as SET/DROP DEFAULT. I kept this renaming code
changes in a separate patch to minimize the diff in the main patch.
Demo:
-- Create table
CREATE TABLE t1 (x int, y int GENERATED ALWAYS AS (x * 2) STORED);
INSERT INTO t1 VALUES(generate_series(1,3));
-- Check the generated data
SELECT * FROM t1;
x | y
---+---
1 | 2
2 | 4
3 | 6
(3 rows)
-- Alter the expression
ALTER TABLE t1 ALTER COLUMN y SET EXPRESSION (x * 4);
-- Check the new data
SELECT * FROM t1;
x | y
---+----
1 | 4
2 | 8
3 | 12
(3 rows)
Thank you.
--