On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth <st...@rothskeller.net> wrote:
> 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 > > >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; *Your problem is that A. TRIGGERS are meant for TABLES, not views* *and * *B. You CANNOT insert into a VIEW.* *https://www.postgresql.org/docs/9.6/static/sql-createtrigger.html <https://www.postgresql.org/docs/9.6/static/sql-createtrigger.html>*CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] } ON *table_name* [ FROM referenced_table_name ] [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ] [ FOR [ EACH ] { ROW | STATEMENT } ]* [ WHEN ( condition ) ]* EXECUTE PROCEDURE function_name ( arguments ) *Please also note that it is very helpful if you specify PostgreSQL version and O/S when submitting to this list.* -- *Melvin Davidson* *Maj. Database & Exploration Specialist* *Universe Exploration Command – UXC* Employment by invitation only!
