Thread: updating(column) Porting from Oracle Trigger to PostgreSQL trigger
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
On Fri, 2021-02-05 at 10:57 +0530, Jagmohan Kaintura wrote:
> 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
ORACLEto 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
columnc3 , 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;
There is no way to determine inside the trigger function which columns were
mentioned in the SET clause of the UPDATE statement.
But you can do that in CREATE TRIGGER:
CREATE TRIGGER ... BEFORE UPDATE OF (c3) ON mytable ...
Of course that might mean that you cannot do conditional processing
in the trigger function, but you have to define several triggers
(which could perhaps share a trigger function).
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com