Re: UPDATE an updatable view

Hello.

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