DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=subject:from:to:in-reply-to:references:content-type:date:message-id:mime-version:x-mailer:content-transfer-encoding;
	b=PJcHfVbYPDN9KBxc/kRM/8rlpBT0yHjQtAFd0XzIMKXklHnVYUo016H92zGMLZbvZuWetsbsMHrBNTlQdlTcsNnEq7TWxlGT54blEZKbcqgJOEcjNjTzAG1t+8tA/XMyVa9Kjqh50R7W5N42CCh0jL3/N7eJEoKZMfnsO/pQjnc=
Subject: Re: Advice for generalizing trigger functions
From: Marcin =?UTF-8?Q?St=C4=99pnicki?= <mstepnicki@gmail.com>
To: pgsql-sql@postgresql.org
In-Reply-To: <781842.52422.qm@web31813.mail.mud.yahoo.com>
References: <781842.52422.qm@web31813.mail.mud.yahoo.com>
Content-Type: text/plain
Date: Sat, 29 Dec 2007 03:09:35 +0100
Message-Id: <1198894176.31628.9.camel@localhost>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit


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