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.attn
