Re: 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 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;

--- Richard Broersma Jr <rabroersma(at)yahoo(dot)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(at)yahoo(dot)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. Especially since 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 timestamp of 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(at)myemma(dot)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(at)myemma(dot)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?

Regards,
Richard Broersma Jr.

On Dec 27, 2007, at 12:03 PM, Richard Broersma Jr wrote:

> --- On Thu, 12/27/07, Erik Jones <erik(at)myemma(dot)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(at)myemma(dot)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 oid FROM 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 = ( SELECT oid 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 = ( SELECT oid 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 = ( SELECT oid 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 = ( SELECT oid 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 = ( SELECT oid 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 = ( SELECT oid 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 EACH ROW EXECUTE PROCEDURE "H' || tabela || '"()';

EXECUTE query;
RETURN query;
END;$BODY$
LANGUAGE 'plpgsql';