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

All we need is an easy explanation of the problem, so here it is.

Postgres 12+ supports generated columns:

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

    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

    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS generator_function(current_row) STORED

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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:

  id int,
  height_cm numeric

create function generator_function(p_row people)
  returns numeric
  select p_row.height_cm / 2.54;
language sql

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply