Re: Trigger with conditional predicates

On 1/2/21 2:23 AM, Dirk Mika wrote:
>>> 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?
>>
>> PostgreSQL doesn't have an exact equivalent. Typically, the OLD and NEW values are compared and then action is taken based on that. For example, in PL/pgSQL:
>>
>> IF NEW.is_canceled IS NOT DISTINCT FROM OLD.is_canceled THEN
>> NEW.is_canceled := etc etc ;
>> ENDIF;
>
> Unfortunately, this doesn't quite fit the logic I need. I don't need to know if the value was changed, but if the application that sent the UPDATE statement knows the column or not.
> In our case, a number of different applications access the database, which may or may not know the column depending on the version.
> And it is also regularly the case that SQL statements are executed directly in an SQL client by hand. And in the event that the column was not specified in these statements, a trigger is supposed to fill the value.
>
>> There's currently no way to detect if the column was simply not mentioned at all in the UPDATE statement.
>
> Hmm, that's odd.

See thread below:

https://www.postgresql.org/message-id/VisenaEmail.26.7cbf2947c8d23ceb.1769a2755ff%40tc7-visena

>
> BR
> Dirk
>
>
>
> --
> Dirk Mika
> Software Developer
>
> mika:timing GmbH
> Strundepark - Kürtener Str. 11b
> 51465 Bergisch Gladbach
> Germany
>
> fon +49 2202 2401-1197
> dirk(dot)mika(at)mikatiming(dot)de
> www.mikatiming.de
>
> AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
> Geschäftsführer: Harald Mika, Jörg Mika
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com