From: | Jerry Sievers <gsievers19(at)comcast(dot)net> |
---|---|
To: | David Nelson <dnelson77808(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: UPDATE an updatable view |
Date: | 2015-08-27 18:21:20 |
Message-ID: | 86zj1ch9sv.fsf@jerry.enova.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
David Nelson <dnelson77808(at)gmail(dot)com> 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.
>> 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(dot)klaver(at)aklaver(dot)com
>
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2015-08-27 18:25:11 | Re: [GENERAL] $libdir/mysql_fdw |
Previous Message | ss | 2015-08-27 17:51:01 | Re: ERROR: collation "latin1_general_ci" for encoding "UTF8" does not exist |