Unable to insert into multi table view using trigger function within DataGrip table UI in PostgreSQL on Windows

已回答

When I try to insert into a multi table view (tables connected by foreign key) using triggers I get this error. Unexpected update count received.

CREATE TABLE first_name (
    id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    first text
);

CREATE TABLE last_name (
    id int REFERENCES first_name(id),
    last text
);

CREATE VIEW first_last AS (
    SELECT first, last FROM first_name
    JOIN last_name on first_name.id = last_name.id
);

CREATE OR REPLACE FUNCTION name_insert_handler()
RETURNS TRIGGER AS
$$
DECLARE
    first_id INT;
BEGIN
    -- insert first name
        INSERT INTO first_name (first) VALUES (NEW.first)
        RETURNING id INTO first_id;
    -- insert last name
        INSERT INTO last_name (id, last) VALUES (first_id, NEW.last);
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER first_last_insert_trigger
INSTEAD OF INSERT
ON first_last
FOR EACH ROW
EXECUTE FUNCTION name_insert_handler();

If I use the console instead, it inserts fine.

0

Hi shylumi,

The issue has been reported in the DBE-17423. You can follow this ticket for updates and upvote it to show your interest and help increase visibility. If you're not familiar with our bug-tracking system, see this article.

0

Thank you, someone on reddit suggested I use RETURN NEW; instead of RETURN NULL; and it seemed to avoid the issue. I will post the same info within the comments of the report page.

0

请先登录再写评论。