Re: Trigger with conditional predicates

2021-01-04 Thread Alban Hertroys
> On 4 Jan 2021, at 20:02, Dirk Mika wrote: > >>> On 1 Jan 2021, at 16:56, Dirk Mika 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, column

Re: Trigger with conditional predicates

2021-01-04 Thread Christophe Pettus
> On Jan 4, 2021, at 11:06, Dirk Mika wrote: > > See thread below: > > https://www.postgresql.org/message-id/VisenaEmail.26.7cbf2947c8d23ceb.1769a2755ff%40tc7-visena > > I found that thread already, but It doesn't not provide a solution to my > problem. One possibility, which is admittedly

Re: Trigger with conditional predicates

2021-01-04 Thread Dirk Mika
>>> 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

Re: Trigger with conditional predicates

2021-01-04 Thread Dirk Mika
-- Dirk Mika Software Developer mika:timing GmbH Strundepark - Kürtener Str. 11b 51465 Bergisch Gladbach Germany fon +49 2202 2401-1197 dirk.m...@mikatiming.de www.mikatiming.de AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884 Geschäftsführer: Harald Mika, Jörg Mika > > On 1 Jan 2021, at 16:56,

Re: Trigger with conditional predicates

2021-01-02 Thread Adrian Klaver
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

Re: Trigger with conditional predicates

2021-01-02 Thread Alban Hertroys
> On 1 Jan 2021, at 16:56, Dirk Mika 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

Re: Trigger with conditional predicates

2021-01-02 Thread Dirk Mika
> > 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 fou

Re: Trigger with conditional predicates

2021-01-02 Thread Dirk Mika
> > 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; > > There's cur

Re: Trigger with conditional predicates

2021-01-01 Thread Tom Lane
Christophe Pettus writes: > 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 ; > EN

Re: Trigger with conditional predicates

2021-01-01 Thread Christophe Pettus
> On Jan 1, 2021, at 07:56, 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 := ...; >