Re: UPSERT on a view

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth <steve@xxxxxxxxxxxxxxx> 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

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!