On Thu, Aug 27, 2015 at 1:21 PM, Jerry Sievers <gsievers19@xxxxxxxxxxx> wrote:
>
> David Nelson <dnelson77808@xxxxxxxxx> writes:
>
> >>> 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!
>
> Huh?
>
> I think any DB platform regardless of how it does MVCC is going to leave
> existing fields as-is in an update if same fields aren't specified.
>
> This has nothing specifically to do with Postgres, MVCC, updatable views
> etc. IMO.
>
You are of course correct, but I could swear I've done an updatable view oin
another system in the past and could count on having the values for column
names that were not called in the UPDATE statement on the view as being either
NULL or maybe undefined values. But I haven't done an updable view in anything
other than PostgreSQL in so long that I most likely dreamed that up (because,
as you seem to be saying, that wouldn't make much sense). So I humbly retract
that part of my reply. Regardless, the DELETE/INSERT explanation makes it
all clear, and I've gotten the trigger function seemingly working the way
I need for it to. At least it's passed all 30 something tests I've thought
of to throw at it. Still testing though. And I'm sure the one test I fail to
think of will be the first thing the front-end developers throw at it. It
always works that way...
Regards
> >> 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.
> >
> >>
> >>>
> >>> Thanks!
> >>
> >>
> >>
> >> --
> >> Adrian Klaver
> >> adrian.klaver@xxxxxxxxxxx
> >
>
> --
> Jerry Sievers
> Postgres DBA/Development Consulting
> e: postgres.consulting@xxxxxxxxxxx
> p: 312.241.7800
>
> David Nelson <dnelson77808@xxxxxxxxx> writes:
>
> >>> 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!
>
> Huh?
>
> I think any DB platform regardless of how it does MVCC is going to leave
> existing fields as-is in an update if same fields aren't specified.
>
> This has nothing specifically to do with Postgres, MVCC, updatable views
> etc. IMO.
>
You are of course correct, but I could swear I've done an updatable view oin
another system in the past and could count on having the values for column
names that were not called in the UPDATE statement on the view as being either
NULL or maybe undefined values. But I haven't done an updable view in anything
other than PostgreSQL in so long that I most likely dreamed that up (because,
as you seem to be saying, that wouldn't make much sense). So I humbly retract
that part of my reply. Regardless, the DELETE/INSERT explanation makes it
all clear, and I've gotten the trigger function seemingly working the way
I need for it to. At least it's passed all 30 something tests I've thought
of to throw at it. Still testing though. And I'm sure the one test I fail to
think of will be the first thing the front-end developers throw at it. It
always works that way...
Regards
> >> 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.
> >
> >>
> >>>
> >>> Thanks!
> >>
> >>
> >>
> >> --
> >> Adrian Klaver
> >> adrian.klaver@xxxxxxxxxxx
> >
>
> --
> Jerry Sievers
> Postgres DBA/Development Consulting
> e: postgres.consulting@xxxxxxxxxxx
> p: 312.241.7800
