Re: [SQL] Advice for generalizing trigger functions

[Marcin Stępnicki]

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



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster