Re: Advice for generalizing trigger functions - Mailing list pgsql-sql

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