Re: [SQL] Advice for generalizing trigger functions
On Dec 25, 2007, at 6:20 PM, Richard Broersma Jr wrote: I've created quite a few functions that log modifications to various history tables. (the history table has the same name as the base table but is prefixed by the 'History.' schema.) The only difference between functions I can find is the table name. Is there any way to generalize these myriad of functions into one? Below is a sample of a typical logging trigger function. Regards, Richard Broersma Jr. CREATE OR REPLACE FUNCTION "project"."log_managers_ops"() RETURNS trigger AS $BODY$ BEGIN IF TG_OP IN ( 'UPDATE', 'DELETE' ) THEN UPDATE History.Managers AS M SET endts = now() WHERE M.manager_id = OLD.manager_id AND now() BETWEEN M.startts AND M.endts; end IF; IF TG_OP IN ( 'UPDATE', 'INSERT' ) THEN INSERT INTO History.Managers VALUES ( now()::timestamptz, 'INFINITY'::timestamptz, NEW.*); RETURN NEW; END IF; RETURN OLD; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER; TG_TABLE_NAME will have the name of the table the trigger was fired on. With that and using EXECUTE for your INSERT statements, you'll probably be set. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Advice for generalizing trigger functions
--- On Thu, 12/27/07, Erik Jones <[EMAIL PROTECTED]> wrote: > TG_TABLE_NAME will have the name of the table the trigger > was fired on. With that and using EXECUTE for your INSERT > statements, you'll probably be set. True the table name is the easy part, but how do I determine the Primary Key components of the table that will also be needed in the SQL string that will be executed? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Advice for generalizing trigger functions
On Dec 27, 2007, at 12:03 PM, Richard Broersma Jr wrote: --- On Thu, 12/27/07, Erik Jones <[EMAIL PROTECTED]> wrote: TG_TABLE_NAME will have the name of the table the trigger was fired on. With that and using EXECUTE for your INSERT statements, you'll probably be set. True the table name is the easy part, but how do I determine the Primary Key components of the table that will also be needed in the SQL string that will be executed? That depends on how generalized you want this trigger function to be. If you have a set number of tables you can branch on the table name to determine the id column. That's brittle and would require updating the the function every time you want to use if for a new table type but if there's a limited number of tables it may work for you. Another way to go would be to the table name to join across pg_class, pg_attribute, and pg_constraint. Take a look at the table layouts for those in the manual and it should be pretty clear how to do that. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
