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: 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
);

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:

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.

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

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

Leave a Reply