From: | Marcin Stępnicki <mstepnicki(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Advice for generalizing trigger functions |
Date: | 2007-12-29 02:09:35 |
Message-ID: | 1198894176.31628.9.camel@localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 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';
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Myers | 2007-12-29 06:40:03 | PL/pgsql: function passing argument to IN operator |
Previous Message | Erik Jones | 2007-12-27 18:22:33 | Re: Advice for generalizing trigger functions |