Thread: Advice for generalizing trigger functions
I've created quite a few functions that log modifications to various history tables. (the history table has the same
nameas the base table but is prefixed by the 'History.' schema.) The only difference between functions I can find is
thetable 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;
--- Richard Broersma Jr <rabroersma@yahoo.com> 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.
the problem is that prepared code is referenced by oid, not name. so
any structural references need to by dynamic.
what I do for change log is to have one change log table with table_id
and column_id attributes that refer by to my internal meta_table and
meta_column tables. this always works and is in the end, I have found,
a bit more flexible, allowing you to search for changed columns, for
example.
but I still generate the change triggers. in this case from in my
meta_table and meta_column tables I note which table/columns I want
changes tracked. those can be changed at any time, but the change log
triggers need to be recompiled.
____________________________________________________________________________________
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
--- On Wed, 12/26/07, chester c young <chestercyoung@yahoo.com> wrote: > what I do for change log is to have one change log table with table_id > and column_id attributes that refer by to my internal meta_table and > meta_column tables. this always works and is in the end, I have found, > a bit more flexible, allowing you to search for changed columns, for > example. I agree that EAV history tables are far more flexible than creating separate history tables for each base table. Especiallysince this design choice does not require modifications when tables are created, altered, or dropped. However,I was looking to utilize functionality that an EAV history table can't provide. For example, I replace joins to the Project.Managers table with joins to the History.Managers table and with a timestampof interest to reproduce query results of the database's state that existed in "times passed". If I didn't need to regularly provide this functionality, I would certainly use an EAV history table. Regards, Richard Broersma Jr.
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® erik@myemma.com 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
--- On Thu, 12/27/07, Erik Jones <erik@myemma.com> 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 neededin the SQL string that will be executed? Regards, Richard Broersma Jr.
On Dec 27, 2007, at 12:03 PM, Richard Broersma Jr wrote: > --- On Thu, 12/27/07, Erik Jones <erik@myemma.com> 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® erik@myemma.com 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
Dnia 25-12-2007, Wt o godzinie 16:20 -0800, Richard Broersma Jr pisze: > 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? > Maybe just try http://pgfoundry.org/projects/audittrail2/ ? Or there's something my friend wrote some time ago (I use audittrail now, but I think that this function works with reasonably recent PostgreSQL releases). Sorry for Polish comments, I've just taken it out from old repository. CREATE OR REPLACE FUNCTION create_history_table(text, text) RETURNS text AS $BODY$ DECLARE tabela ALIAS FOR $1; query TEXT; fields RECORD; grupa ALIAS FOR $2; BEGIN query := ''; -- poczatek definicji zapytania tworzacego historie query := 'CREATE TABLE "H' || tabela || '"(\r\n'; -- petelka wyciagajaca pola tabeli do ktorej tworzona jest historia FOR fields IN SELECT a.attname AS name, format_type(a.atttypid, a.atttypmod) AS type FROM pg_attribute a WHERE a.attrelid = ( SELECT oidFROM pg_class WHERE relname = tabela ) AND a.attnum > 0 LOOP query := query || fields.name|| ' ' || fields.type || ',\r\n'; END LOOP; -- doklejenie pol dotyczacych historii query := query || 'log_usename text default current_user, ' ||'log_time timestamp default now(), ' || 'log_event text default '''' ' || ');\r\n'; -- ustawienie uprawnien do zapisu i odczytu z tabeli historii query := query || 'GRANT SELECT, INSERT ON "H' || tabela|| '" TO GROUP "' || grupa || '";\r\n'; -- EXECUTE query; -- query := ''; -- definicja funkcji dla triggera historii query := query || 'CREATE FUNCTION "H' || tabela || '"() RETURNS trigger AS\r\n' || '$$\r\n' || 'begin\r\n' || 'if ( tg_op = ''INSERT'' ) then\r\n' || ' INSERT INTO "H' || tabela || '" (\r\n'; FOR fields IN SELECT a.attname AS name -- format_type(a.atttypid, a.atttypmod) AS type FROM pg_attribute a WHERE a.attrelid = ( SELECToid FROM pg_class WHERE relname = tabela ) AND a.attnum > 0 LOOP query := query || ' ' || fields.name || ', \r\n'; END LOOP; query := query || ' log_event ) VALUES (\r\n'; FOR fields IN SELECT a.attname AS name -- format_type(a.atttypid, a.atttypmod) AS type FROM pg_attribute a WHERE a.attrelid = ( SELECToid FROM pg_class WHERE relname = tabela ) AND a.attnum > 0 LOOP query := query || ' NEW.' || fields.name || ', \r\n'; END LOOP; query := query || ' ''I'');\r\n'; query := query || 'end if;\r\n' || 'if tg_op = ''UPDATE'' then\r\n' || ' if OLD.id!= NEW.id then\r\n' || ' UPDATE "H' || tabela || '" SET id = NEW.id WHERE id = OLD.id;\r\n' || ' end if;\r\n' || ' INSERT INTO "H' || tabela || '"(\r\n'; FOR fields IN SELECT a.attname AS name -- format_type(a.atttypid, a.atttypmod) AS type FROM pg_attribute a WHERE a.attrelid = ( SELECToid FROM pg_class WHERE relname = tabela ) AND a.attnum > 0 LOOP query := query || ' ' || fields.name || ', \r\n'; END LOOP; query := query || ' log_event ) VALUES (\r\n'; FOR fields IN SELECT a.attname AS name -- format_type(a.atttypid, a.atttypmod) AS type FROM pg_attribute a WHERE a.attrelid = ( SELECToid FROM pg_class WHERE relname = tabela ) AND a.attnum > 0 LOOP query := query || ' NEW.' || fields.name || ', \r\n'; END LOOP; query := query || ' ''U'');\r\n'; query := query || 'end if;\r\n' || 'if tg_op = ''DELETE'' then\r\n' || ' INSERT INTO"H' || tabela || '"('; FOR fields IN SELECT a.attname AS name -- format_type(a.atttypid, a.atttypmod) AS type FROM pg_attribute a WHERE a.attrelid = ( SELECToid FROM pg_class WHERE relname = tabela ) AND a.attnum > 0 LOOP query := query || ' ' || fields.name || ', \r\n'; END LOOP; query := query || ' log_event ) VALUES (\r\n'; FOR fields IN SELECT a.attname AS name -- format_type(a.atttypid, a.atttypmod) AS type FROM pg_attribute a WHERE a.attrelid = ( SELECToid FROM pg_class WHERE relname = tabela ) AND a.attnum > 0 LOOP query := query || ' OLD.' || fields.name || ', \r\n'; END LOOP; query := query || ' ''D'');\r\n'; query := query || 'end if;\r\n' || 'return NEW;\r\n' || 'end;\r\n' || '$$ LANGUAGE ''PLPGSQL'';'; query := query || 'CREATE TRIGGER "H' || tabela || '" AFTER INSERT OR UPDATE OR DELETE ON "' || tabela || '" FOR EACHROW EXECUTE PROCEDURE "H' || tabela || '"()'; EXECUTE query; RETURN query; END;$BODY$ LANGUAGE 'plpgsql';