Thread: Detection of which attributes should get set in update trigger

Hi all

I have a view, that is a join over 4 tables (the setup of the rule 
question). I want to update the central table over the view (by an 
instead-of trigger). How can I determine, whether an attribute should 
get set to NULL, "new.XYZ is null" or whether it should be left alone. 
Is there a leave-me-alone function determining, whether an attribute is 
in the set clause of an update statement. I am thinking along the line 
code in the trigger function like

update TABLE
    set XYZ = case
                   when leave-me-alone (new.XYZ) then old.XYZ
                   else new.XYZ
              end;

And what about the where condition... hm, guess I am a bit confused.

Kind regards

Thiemo

Hi all

I have a view, that is a join over 4 tables (the setup of the rule question). I want to update the central table over the view (by an instead-of trigger). How can I determine, whether an attribute should get set to NULL, "new.XYZ is null" or whether it should be left alone. Is there a leave-me-alone function determining, whether an attribute is in the set clause of an update statement. I am thinking along the line code in the trigger function like

update TABLE
   set XYZ = case
                  when leave-me-alone (new.XYZ) then old.XYZ
                  else new.XYZ
             end;

And what about the where condition... hm, guess I am a bit confused.

Thanks for the reply. I confirm the behaviour. Your explanation makes sense if I consider having read that with an update the is a complete new record version written.

On Friday, November 10, 2023, Thiemo Kellner <thiemo@gelassene-pferde.biz> wrote:

Hi all

I have a view, that is a join over 4 tables (the setup of the rule question). I want to update the central table over the view (by an instead-of trigger). How can I determine, whether an attribute should get set to NULL, "new.XYZ is null" or whether it should be left alone. Is there a leave-me-alone function determining, whether an attribute is in the set clause of an update statement. I am thinking along the line code in the trigger function like

update TABLE
   set XYZ = case
                  when leave-me-alone (new.XYZ) then old.XYZ
                  else new.XYZ
             end;

And what about the where condition... hm, guess I am a bit confused.

IIUC, The NEW record is complete, with every value as it should be in the newly saved tuple.  There is no leave-me-alone concept.  You don’t get to know if the column was specified or not in the update command.

David J.