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 := ...; >

Trigger with conditional predicates

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