Create postgres trigger that will populate additional table column on insert

Question

Problem

(Note: I have added potential solution to the bottom of the post)

I don't know what's wrong with the approach that I currently have, but when I try to run it dbeaver just instantly quits.

I think it's because I want to insert into the same table, but a different column, so it's creating a recursive error.

To be clear - when data is inserted into the tables x column I want to insert it into the y column at the same time with some alteration. In this example the alteration is just x + 1.

Attempt at a solution:

DROP TABLE IF EXISTS tbl; 
CREATE TABLE tbl (
 x integer,
 z integer
);
INSERT INTO tbl(x)
SELECT x FROM generate_series(1, 5) AS t(x); 
ALTER TABLE tbl
ADD COLUMN y integer;
CREATE OR REPLACE FUNCTION copy_to_new_column() RETURNS TRIGGER AS
$BODY$
BEGIN
 INSERT INTO
 tbl(y)
 VALUES(NEW.x + 1);
 RETURN new;
END;
$BODY$
language plpgsql;
CREATE OR REPLACE TRIGGER trig_copy_to_new
AFTER INSERT ON tbl 
FOR EACH ROW 
EXECUTE 
PROCEDURE 
copy_to_new_column(); 
-- would want to have a row (x, y, z) with (22, 23, 91) after this insert
INSERT INTO tbl(x, z) VALUES 
(22, 91)
;

It makes sense that there's a recursive error, as the function is inserting into the same table that there's a INSERT trigger on, but I don't know how else to go about this.

To be explicit

• Enter a row with integers a, b into the columns x, z
• The trigger will cause the row a, a+1, b to be inserted into columns x, y, z

Edit

Here's an approach that might work:

DROP TABLE IF EXISTS tbl; 
CREATE TABLE tbl (
 x integer, 
 y integer, 
 z integer
);
INSERT INTO tbl(x, z)
SELECT x, x + cast(3 * random() AS integer) FROM generate_series(1, 2) AS t(x); 
CREATE OR REPLACE FUNCTION copy_column_on_insert()
RETURNS TRIGGER AS $$
BEGIN
 NEW.y := NEW.x + 1;
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE TRIGGER copy_column_on_insert_trigger
BEFORE INSERT ON tbl
FOR EACH ROW
EXECUTE PROCEDURE copy_column_on_insert();
INSERT INTO tbl(x, z)
VALUES 
(12, 22)
;
SELECT * FROM tbl; 

• mustaccio: It would help if you provided some sample data, before and after the insert -- it's unclear what you're trying to achieve.
• baxx: @mustaccio Sorry but can you say exactly what you want, as i think i've provided a full MWE. I've also commented what I would like to have after the insert. I don't understand what isn't clear to you, assuming you've read the code and the post.
• baxx: @mustaccio I have edited the post at the bottom

Answer 1

Can you add a column to the table? If so, I would solve the problem like this.

DROP TABLE IF EXISTS tbl; 
CREATE TABLE tbl (
 x integer, 
 y integer, 
 z integer,
 is_copy bool
);
INSERT INTO tbl(x, z)
SELECT x, x + cast(3 * random() AS integer) FROM generate_series(1, 2) AS t(x); 
CREATE OR REPLACE FUNCTION copy_column_on_insert()
RETURNS TRIGGER AS $$
begin
 if new.is_copy is not true then 
 INSERT INTO
 tbl(x, y, z, is_copy)
 VALUES(NEW.x, NEW.x + 1, new.z, true);
 end if; 
 RETURN new;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE TRIGGER copy_column_on_insert_trigger
BEFORE INSERT ON tbl
FOR EACH ROW
EXECUTE PROCEDURE copy_column_on_insert();
INSERT INTO tbl(x, z)
VALUES 
(12, 22)
;
SELECT * FROM tbl; 

In this way there is no infinite loop because the conditional IF with the boolean value makes sure to break it.