PostgreSQL – Populate Column Using Values of Other Columns

postgresql

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

Best Answer

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.

Related Question