Re: UPDATE an updatable view

On 27.8.2015 18:35, David Nelson wrote:
>>> So in the UPDATE statement, I only provided a value for last_user.
>>> But the first test of the trigger function tests for a NULL value of
>>> NEW.empname. Since I did not provide one, I was expecting it to be
>>> NULL and an exception to be thrown. Am I just misunderstanding how
>>> things work? Is there any way to test to see if the UPDATE statement
>>> contained a reference to empname? If the answer is no, I can
>>> certainly work with that, but before I go on I wanted to make sure I
>>> wasn't missing anything.
>>
>>
>> An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is
>> deleted
> and the NEW one inserted with the OLD values unless they where
> explicitly changed. So
>
> Shoot, I went totally brain-dead on that one. I forgot that I'm
> actually doing a DELETE/INSERT, and the behaviour makes perfect sense
> in that light. It's called MVCC. Thanks for setting me straight!
>
>> in your test NEW.empname is still 'John Doe' and therefore NOT NULL.
>> That
> test would only work if someone explicitly set empname = NULL in the
> update. If you want to check whether the value has not been changed then:
>>
>> IF NEW.empname = OLD.empname THEN
>
> That's exactly the solution I hit on. Back to work, and thanks again.

Just for the sake of completeness...

If the value (empname in the above example) can be NULL, the compare does not work, because

SELECT NULL = NULL

returns NULL which is treated as FALSE.

But I am sure you know this :-)

HTH,

Ladislav Lenart

___________________________

Right. And that's why you use very nice option provided by PG:

IF NEW.empname IS DISTINCT FROM OLD.empname THEN

which again you probably know :)

Regards,
Igor Neyman