ALTER COLUMN ... SET EXPRESSION to alter stored generated column's expression

amul sul <sulamul@gmail.com>

From: Amul Sul <sulamul@gmail.com>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2023-08-02T10:35:22Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →
  1. ALTER TABLE command to change generation expression

  2. Refactor: separate function to find all objects depending on a column

  3. Turn AT_PASS_* macros into an enum

Attachments

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.
-- 
Regards,
Amul Sul
EDB: http://www.enterprisedb.com