Refreshing a stored generated column in Postgres

How in PostgreSQL can I refresh the values of a stored generated column.

Example

CREATE TABLE people (
    first_name TEXT,
    last_name TEXT

);

CREATE OR REPLACE FUNCTION name(people) RETURNS text AS
$
SELECT $1.first_name || ' ' || $1.last_name
$ LANGUAGE SQL STRICT
                IMMUTABLE
        ;

alter table people add column   full_name text GENERATED ALWAYS AS (name(people)) STORED;

insert into people(first_name, last_name) values('John', 'Smith');

select full_name, full_name='John Smith' as pass from people;

If one then needs to change the function used to generate a stored column value, in this case name how can you ask postgres to recalculate these fields.

eg.

CREATE OR REPLACE FUNCTION name(people) RETURNS text AS
$
SELECT $1.first_name || ' ' || $1.last_name  ||  ' function change'
$ LANGUAGE SQL STRICT
                IMMUTABLE
        ;

 -- refresh generated column here

select full_name, full_name='John Smith function change' as pass from people;

Sorry this is a bit of an odd example.

Fiddle

The only way is to rewrite the whole table with a no-operation update like:

UPDATE people SET first_name = first_name;

That will re-evaluate the function.

You may want to run VACUUM (FULL) on the table afterwards to reclain the wasted space.

StackExchange Database Administrators Q#258391, answer score: 6