Populate a column using the values of other ones

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

I would like to add a new column in my table using SQL code in PostgreSQL / PostGIS, the values in this column must be filled from the first eight columns and updated with a trigger as I insert data to a new line for the first rows, maintaining the Area, x, and y columns with their values. As I show in the attached image.

Table

So, the “New Column” just not receives the values from Area, x and y.
Those three last columns have got the following codes that area working normally

Codes for Area Column:

CREATE OR REPLACE FUNCTION update_area()
  RETURNS TRIGGER AS
    $$
    BEGIN
      NEW.area := round(ST_Area(NEW.geom):: numeric,2);   
      RETURN NEW;
    END;
    $$
  LANGUAGE plpgsql;

the trigger of that:

CREATE TRIGGER update_area
  BEFORE UPDATE OR INSERT ON public."EDIFICACAO_4"
  FOR EACH ROW
  EXECUTE PROCEDURE update_area();

Code for the x and y columns:

ALTER TABLE "EDIFICACAO_4" ADD X double precision;
ALTER TABLE "EDIFICACAO_4" ADD y double precision;
UPDATE "EDIFICACAO_4" SET X = ST_X(ST_Centroid(geom));
UPDATE "EDIFICACAO_4" SET Y = ST_Y(ST_Centroid(geom));

The trigger of that:

CREATE OR REPLACE FUNCTION update_en()
  RETURNS TRIGGER AS
    $$
    BEGIN

      NEW.X := round(ST_X(ST_Centroid(NEW.geom))::numeric,3);   
      NEW.Y := round(ST_Y(ST_Centroid(NEW.geom))::numeric,3);   
      RETURN NEW;

    END;
    $$
  LANGUAGE plpgsql;

CREATE TRIGGER update_en
  BEFORE UPDATE OR INSERT ON public."EDIFICACAO_4"
  FOR EACH ROW
  EXECUTE PROCEDURE update_en();

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

Just create a single function that handles all columns you want to update. Essentially the same as you already do with X and Y:

CREATE OR REPLACE FUNCTION update_edificacao()
  RETURNS TRIGGER AS
    $$
    BEGIN

      NEW.X := round(ST_X(ST_Centroid(NEW.geom))::numeric,3);   
      NEW.Y := round(ST_Y(ST_Centroid(NEW.geom))::numeric,3);
      NEW.area := round(ST_Area(NEW.geom):: numeric,2);  

      -- the following was taken from your other question
      NEW.codigo := concat_ws('.', new."PP", new."MM", new."DD", new."BB", new."QQ", new."LL", new."ID", new."UU"); 

      -- if you have more columns to deal with, add them here
      NEW.some_other_column := ....;
      NEW.yet_another_column := ....;   

      RETURN NEW;

    END;
    $$
  LANGUAGE plpgsql;

Then create one trigger that calls the function that handles all columns:

CREATE TRIGGER update_en
  BEFORE UPDATE OR INSERT ON public."EDIFICACAO_4"
  FOR EACH ROW
  EXECUTE PROCEDURE update_edificacao();

And drop all other trigger functions and triggers.

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