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
Re: Detection of which attributes should get set in update trigger
On Friday, November 10, 2023, Thiemo Kellner 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.
Re: Detection of which attributes should get set in update trigger
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. Am 10.11.2023 um 14:35 schrieb David G. Johnston: On Friday, November 10, 2023, Thiemo Kellner 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.
