1

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; 
3
  • It would help if you provided some sample data, before and after the insert -- it's unclear what you're trying to achieve.
    – mustaccio
    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

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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