Problem

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

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; 

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

Problem

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

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.

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