From: | Steven Roth <steve(at)rothskeller(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | UPSERT on a view |
Date: | 2018-03-12 18:26:20 |
Message-ID: | CAAnpqKH7B0aWnAPUsXTPQBi3_52tAD_PQ_mAf1x7NsdE8_fXdQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Why does the following code raise an error?
CREATE TABLE ttest (x integer);
CREATE VIEW vtest AS SELECT x FROM ttest;
CREATE FUNCTION vtest_insert() RETURNS trigger LANGUAGE plpgsql AS $$
RAISE 'foo' USING ERRCODE='unique_violation';
END $$;
CREATE TRIGGER vtest_insert INSTEAD OF INSERT ON vtest
FOR EACH ROW EXECUTE PROCEDURE vtest_insert();
INSERT INTO vtest VALUES (1) ON CONFLICT DO NOTHING;
This code raises the error 'foo', even though the insert says DO NOTHING
and the error type is unique_violation. Why?
More generally: how can one write trigger functions for a view (that is
not automatically updatable), such that INSERT ... ON CONFLICT DO UPDATE
will work with the correct semantics? What can one do in the INSERT
trigger that will cause PostgreSQL to execute the caller-supplied UPDATE
clause?
Thanks,
Steve
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Johnson | 2018-03-12 18:32:24 | Re: psql in a bash function |
Previous Message | Eric Raskin | 2018-03-12 18:24:01 | Re: psql in a bash function |