Re: Trigger with conditional predicates

> On 4 Jan 2021, at 20:02, Dirk Mika <Dirk(dot)Mika(at)mikatiming(dot)de> wrote:
>
>>> On 1 Jan 2021, at 16:56, Dirk Mika <Dirk(dot)Mika(at)mikatiming(dot)de> wrote:
>>>
>>> Hi all and a happy new Year!
>>>
>>> We have an Oracle schema that is to be converted to PostgreSQL, where conditional predicates are used in some triggers.
>>>
>>> In particular, columns are populated with values if they are not specified in the update statement which is used.
>>> Usually with an expression like this:
>>>
>>> IF NOT UPDATING('IS_CANCELED')
>>> THEN
>>> :new.is_canceled := ...;
>>> END IF;
>>>
>>> I have not found anything similar in PostgreSQL. What is the common approach to this problem?
>
>> Can't you use column defaults to handle these cases?
>
> That would work for inserts, but not for updates.

Usually, if you don’t mention a column in an UPDATE, you want the value to remain as it was, which is precisely what happens by default. That certainly makes sense to me when you’re dealing with an application that doesn’t know about the existence of said column; overwriting an existing value that some other application put there looks like a problem to me. But of course, that depends a lot on what you’re trying to achieve.

What is your use-case that that is not the desired behaviour? Or are we talking about a mixed problem here, where this approach works for some fields, but other fields (such as a status change date) always need to be updated (regardless of whether a value was specified)?

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.