From: | David Nelson <dnelson77808(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: UPDATE an updatable view |
Date: | 2015-08-27 16:35:56 |
Message-ID: | CANxyCUH6beLSzsq0sGduwX7T9R54QKcVExB524jAuCj0r3d-vQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>> 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.
>
>>
>> Thanks!
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2015-08-27 17:00:37 | Re: two factor authentication for pgadmin iii |
Previous Message | cchee-ob | 2015-08-27 16:35:20 | BDR setup - bdr_node_join_wait_for_ready() |