Return to Answer

added 113 characters in body

edited Jan 4, 2023 at 14:37

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.

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;

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.

Source Link

answered Jan 4, 2023 at 14:30

Sotis

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;