Re: UPSERT on a view

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Tue, Mar 13, 2018 at 10:02 AM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Tue, Mar 13, 2018 at 6:47 AM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:

On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth <steve@xxxxxxxxxxxxxxx> wrote:

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?

Sorry, not sure...

​

>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.

 

​Your knowledge is this area is out-of-date...you should read the page you linked to again.

I'll admit "table_name" probably could be labelled "relation_name"...though that is then too broad.

David J.

​

 

>Your knowledge is this area is out-of-date...you should read the page you linked to again.
Yes, you can create a TRIGGER on view, but you CANNOT INSERT INTO A VIEW,

IE:

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;

Which is the cause of the Error.!

--

Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!