Re: Advice for generalizing trigger functions

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';