On Fri, Aug 28, 2015 at 6:39 AM, Ladislav Lenart <lenartlad@xxxxxxxx> 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 ...
>
> 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 ...
