updating(column) Porting from Oracle Trigger to PostgreSQL trigger

From: Jagmohan Kaintura <jagmohan(at)tecorelabs(dot)com>
To: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: updating(column) Porting from Oracle Trigger to PostgreSQL trigger
Date: 2021-02-05 05:27:37
Message-ID: CA+cYFttzsT1fLQekwFn-2MxM8Aj5ZcwiYQz8j09BALvbuKDDZQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi Team,

When we do an implementation from Oracle to PostgreSQL in trigger for
clause like :
updating(column_name) ==> (TG_OP='UPDATE' and OLD.column_name IS
DISTINCT FROM NEW.column_name)
But this condition would mostly not be sufficient when we are updating any
column and with similar value somehow.
Like : column_name OLD values is 2 and New values updates is 2.

In PG it would become (TG_OP="UPDATE' and *2 is DISTINCT FROM 2*), Overall
it becomes FALSE , so from conversion from ORACLE to PostgreSQL it
doesn't give a clear picture for exact implementation for these UPDATING
clause.

Now why I brought up this as I got into one of the implementations which
made me crazy to implement.

Scenario :
Column c3 is right not updating to the same value of column, for some
bypass implementation.

update table
c1 = 'abc',
c2 ='xyz',
*c3=c3;*

Inside trigger we have implementation like :

*Oracle:*
--THis block is being placed at the start of Trigger to bypass all other
trigger blocks whenever we are updating column c3 , along with other
columns.
IF UPDATING('C3') THEN
RETURN;
END IF;

If this column c3 is not placed in the update clause, then trigger would go
through and execute other statements.

*PostgreSQL:*
We use the same method as :
IF TC_OP='UPDATE' and OLD.c3 is DISTINCT FROM NEW.C3 THEN
RETURN NEW;
END IF;

But here it won't ever go inside the IF clause and will never return out
the start of trigger only. So technically not able to map the same Oracle
fundamentals.

Is there any other method in POstgreSQL using which I can check which
column is getting updated. Will just replace at least this clause with any
other block.

Help would be really appreciated.

*Best Regards,*
Jagmohan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2021-02-05 05:33:03 Re: ltree and PHP
Previous Message Open _ 2021-02-05 04:44:33 ltree and PHP