Re: UPSERT on a view

[Tom Lane]

Melvin Davidson  writes:
> On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth  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?

> *Your problem is that A. TRIGGERS are meant for TABLES, not views*
> *and *
> *B. You CANNOT insert into a VIEW.*

No, instead-of triggers are supposed to do exactly that.  The OP's problem
is that he imagines that ON CONFLICT works by catching arbitrary errors and
checking to see if they are labeled unique_violation.  Doesn't work like
that; if it did, it'd be the wrong thing, because we would not know
whether or how to roll back any other side effects the trigger had had.

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

Don't think it's possible unfortunately.  The trigger would have to take
care of the situation, but it doesn't have any way to know that the
calling query has an ON CONFLICT rule.

regards, tom lane

Re: UPSERT on a view

[Melvin Davidson]

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

> On Tue, Mar 13, 2018 at 6:47 AM, Melvin Davidson 
> wrote:
>
>> On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth 
>> 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!

Re: UPSERT on a view

[David G. Johnston]

On Tue, Mar 13, 2018 at 6:47 AM, Melvin Davidson 
wrote:

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

Re: UPSERT on a view

[Melvin Davidson]

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

UPSERT on a view

[Steven Roth]

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