How to pass the current row to Generated Column function in Postgres?

Question

Postgres 12+ supports generated columns: https://www.postgresql.org/docs/current/ddl-generated-columns.html

From the docs, the syntax seems limited - it forces one to explicitly name the columns on which the generated column depends.

CREATE TABLE people (
 ...,
 height_cm numeric,
 height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

Is there a way to pass the entire row to the generating function? Something like

CREATE TABLE people (
 ...,
 height_cm numeric,
 height_in numeric GENERATED ALWAYS AS generator_function(current_row) STORED
);

• Vérace: Interesting question (+1). Just as a matter of interest, why do you want to do this?
• Pragy Agarwal: @Vérace-СлаваУкраїні For DRY code. I've multiple tables for which I need to compute a tsvector and add an index on it for full-text search. My idea was to create a function that alters a given table to add the column and the index, and have individual x_tsvector() convertors that take a row from table x and produce a tsvector. Sadly, postgres doesn't support fancy metaprogramming. :( dba.stackexchange.com/questions/302599/…
• Vérace: Does any RDBMS system support fancy metaprogramming?
• Pragy Agarwal: None that I know of. It would be lovely if they start doing it though!

Answer 1

This doesn't seem possible.

If it did work, it could only work by first creating the table, then the function, then adding the generated column.

However, doing that:

CREATE TABLE people 
(
 id int,
 height_cm numeric
);
create function generator_function(p_row people)
 returns numeric
as
$$
 select p_row.height_cm / 2.54;
$$
language sql
immutable;
alter table people
 add height_in numeric GENERATED ALWAYS AS (generator_function(people)) STORED;

Results in:

ERROR: cannot use whole-row variable in column generation expression
Detail: This would cause the generated column to depend on its own value.

So, this does not seem to be possible.

• Pragy Agarwal: "This would cause the generated column to depend on its own value." Ah, that makes a lot of sense!