0

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 other eight columns and updated with a trigger as I insert data to a new line for the first rows. As I show in the attached image.

Table with the new column

I have used this code to alter the table,

ALTER TABLE table_name ADD COLUMN NEW_COLUMN varchar;

UPDATE table_name SET NEW_COLUMN = "PP" || '.' || "MM" || '.' || "DD" || '.' || "BB" || '.' || "QQ" || '.' || "LL" || '.' || "ID"::varchar || '.' || "UU";

But I am having a problem using this trigger because it does not accept:

CREATE OR REPLACE FUNCTION update_en() 
RETURNS TRIGGER AS 
$$ 
BEGIN 
NEW.codigo :=; 
RETURN NEW; 
END; 
$$ 
LANGUAGE plpgsql; 
CREATE TRIGGER update_en 
BEFORE UPDATE OR INSERT ON public."EDIFICACAO_4" 
FOR EACH ROW 
EXECUTE PROCEDURE update_en();
  • 1
    Why are you storing that information to begin with? Create a view that simply returns the concatenated value. – a_horse_with_no_name Oct 16 '18 at 13:07
2

The question on why you think you need that aside, you can simplify the concatenation using concat_ws()which you can easily use in the trigger as well:

CREATE OR REPLACE FUNCTION update_en() 
RETURNS TRIGGER AS 
$$ 
BEGIN 
  NEW.codigo := concat_ws('.', new."PP", new."MM", new."DD", new."BB", new."QQ", new."LL", new."ID", new."UU");
  RETURN NEW; 
END; 
$$ 
LANGUAGE plpgsql; 
|improve this answer|||||
  • Hi, it worked, so the problem is, I Had more two columns i did not want to alter, so when i write this trigger, the values of this columns are no more calculated, I edited the question to see that. – Rosário Dilo Oct 17 '18 at 6:05
  • @RosárioDilo: then include the calculation of those columns in that trigger. – a_horse_with_no_name Oct 17 '18 at 6:07
  • @Janel27, iam having difficult to rearrange that, in that trigger where do i include the calculation of those columns and where do i include also their trigger? Remember that those two columns can note appear to add the code. – Rosário Dilo Oct 17 '18 at 6:29
  • @RosárioDilo: please add a new question for that including your complete trigger code and your attempts to update the other columns as well. – a_horse_with_no_name Oct 17 '18 at 6:54
  • Hi, @Janel27, i have created another answer in the following link dba.stackexchange.com/questions/220329/… – Rosário Dilo Oct 17 '18 at 9:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.