Re: UPDATE an updatable view

On Fri, Aug 28, 2015 at 6:39 AM, Ladislav Lenart <lenartlad(at)volny(dot)cz> wrote:
>
> 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

Thanks Ladislav. To be able to capture updates to NULL, I ended up going
with

IF NEW.empname IS DISTINCT FROM OLD.empname THEN ...