Re: UPSERT on a view

On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth <steve(at)rothskeller(dot)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!