# 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;
``````
• It would help if you provided some sample data, before and after the insert -- it's unclear what you're trying to achieve. Dec 24, 2022 at 15:08
• @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
Dec 24, 2022 at 15:13
• @mustaccio I have edited the post at the bottom
– baxx
Dec 24, 2022 at 15:15

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.