CODEWITHSUNDEEP
postgresqlviewtriggersinsert
kcdgkn
kcdgkn

Reputation: 116

Postgres INSERT ON CONFLICT behavior with INSTEAD OF trigger

Suppose I have the following tables and view:

CREATE TABLE table_a(
    field_x INTEGER PRIMARY KEY,
    id SERIAL UNIQUE
);
CREATE TABLE table_b(
    a_id INTEGER PRIMARY KEY REFERENCES table_a(id),
    field_y INTEGER NOT NULL
);
CREATE VIEW v AS SELECT * FROM table_a JOIN table_b ON table_a.id=table_b.a_id;

I want to be able to insert into the view, so I created the following function and trigger:

CREATE FUNCTION insert_into_view()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $function$
   DECLARE new_id INTEGER;
   BEGIN
      IF TG_OP = 'INSERT' THEN
        INSERT INTO table_a (field_x) VALUES (NEW.field_x) ON CONFLICT DO NOTHING RETURNING id INTO new_id;
        IF new_id IS NULL THEN
          SELECT id FROM table_a WHERE field_x=NEW.field_x INTO new_id;
        END IF;
        INSERT INTO table_b (a_id, field_y) VALUES (new_id, NEW.field_y);
      END IF;
      RETURN NEW;
    END;
$function$;
CREATE TRIGGER view_insert_trigger
    INSTEAD OF INSERT ON
      v FOR EACH ROW EXECUTE PROCEDURE insert_into_view();

Now I want to insert values into the view only if there does not exist a row for field_x yet in the view, e.g.:

INSERT INTO v (field_x, field_y) VALUES (5,6);
INSERT INTO v (field_x, field_y) VALUES (5,8) ON CONFLICT DO NOTHING;

Where I want the second insert to silently do nothing. However, I get this:

ERROR:  duplicate key value violates unique constraint "table_b_pkey"
DETAIL:  Key (a_id)=(2) already exists.
CONTEXT:  SQL statement "INSERT INTO table_b (a_id, field_y) VALUES (new_id, NEW.field_y)"

I know why I'm getting this error: the function insert_into_view does not specify ON CONFLICT behavior when inserting into table_b and by default the query fails. Thus my question: can I make the ON CONFLICT behavior to ripple from the view insert into the table insert? (I may want to specify different conflict behavior at a later time, so I don't want to hard-code this in the trigger function if I can avoid it.)

Thanks!

Upvotes: 2

Views: 1112

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51446

I'm still not sure If I understand you right. But I try:

if you change 

INSERT INTO table_b (a_id, field_y) VALUES (new_id, NEW.field_y)

to

INSERT INTO table_b (a_id, field_y) VALUES (new_id, NEW.field_y) ON CONFLICT DO NOTHING

in function it will start working silently.

Regarding 

INSERT INTO v (field_x, field_y) VALUES (5,8) ON CONFLICT DO NOTHING;

I think you can use ON CONFLICT only on tables with unique constraint, so and foreign table, and instead rule will ignore ON CONFLICT DO NOTHING and fail when you specify target_name of constraint_name

Upvotes: 1

Related Questions