[GENERAL] comparing OLD and NEW in update trigger..
Hi there, i'm planning to use the following trigger function to update a timestamp of a row when it is UPDATEd: CREATE OR REPLACE modified_trigger() RETURNS opaque AS $$ BEGIN NEW.modify_timestamp := now(); END; $$ LANGUAGE SQL; Since i like to use the same trigger procedure for various tables, i'm planning to keep it very generic. What i'd like to do now is to just update the modify_timestamp column if OLD and NEW are different. I'd LOOP over the row elements, and compare each column of OLD with NEW, and bailing out if there's a difference. I'd appreciate your help on the following two questions: - How can i get the column names of NEW/OLD? Is there a set returning function for this? - Is there a more efficient way to compare whole rows? thanks, Alex Mayrhofer --- http://nona.net/features/map/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] comparing OLD and NEW in update trigger..
Alex Mayrhofer wrote: Hi there, i'm planning to use the following trigger function to update a timestamp of a row when it is UPDATEd: CREATE OR REPLACE modified_trigger() RETURNS opaque AS $$ BEGIN NEW.modify_timestamp := now(); END; $$ LANGUAGE SQL; I don't think you can write a trigger function in SQL - you'll want one of the procedural languages: plpgsql / plperl / pltcl etc. Since i like to use the same trigger procedure for various tables, i'm planning to keep it very generic. What i'd like to do now is to just update the modify_timestamp column if OLD and NEW are different. I'd LOOP over the row elements, and compare each column of OLD with NEW, and bailing out if there's a difference. I'd appreciate your help on the following two questions: - How can i get the column names of NEW/OLD? Is there a set returning function for this? You'll want one of the interpreted languages: pltcl / plperl / plphp etc. You'll find plpgsql can't cope with the sort of dynamic-typing required to do this easily. - Is there a more efficient way to compare whole rows? No. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] comparing OLD and NEW in update trigger..
Hi there, i'm using the following trigger function to update a timestamp of a row when it is UPDATEd: CREATE OR REPLACE modified_trigger() RETURNS opaque AS $$ BEGIN NEW.modify_timestamp := now(); END; $$ LANGUAGE SQL; Since i like to use the same trigger for various tables, i'm planning to keep it very generic. What i'd like to do now is to just update the modify_timestamp column if OLD and NEW are different. I'd LOOP over the row elements, and compare each column of OLD with NEW, and bailing out if there's a difference. I'd appreciate your help on the following two questions: - How can i get the column names of NEW/OLD? Is there a set returning function for this? - Is there a more efficient way to compare whole rows? thanks, Alex Mayrhofer --- http://nona.net/features/map/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
