Thread: Function To Log Changes
Hi All, I have been tinkering with a function to log the changes made on
any column through a function and trigger. However, I cant think of a way
to make this work through pl/pgsql. Any one have any ideas, or is it just
not possible?
SNIP
create or replace function logchange2() returns OPAQUE as '
DECLARE columnname record; c2 VARCHAR(64);
BEGIN
/* Cycle through the column names so we can find the changes being made */
FOR columnname IN SELECT attname FROM pg_attribute, pg_type WHERE attnum > 0 AND typrelid=attrelid AND
typname=''SOMETABLE''LOOP
c2 := CAST(columnname.attname AS VARCHAR(64));
/* here lies the problem. How would I make plpgsql see OLD.columnname in
a dynamic fashion. I know this wont work whats below, but I am just
trying to express what I am trying to do */ IF ''OLD.'' || c2 != ''NEW.'' || c2 THEN /* IF CHANGED
DOSOMETHING */ RAISE NOTICE ''Update on column %'', c2; END IF;
END LOOP;
return NULL;
END;
'
LANGUAGE plpgsql;
create trigger logchange2 AFTER UPDATE on TABLENAME FOR EACH ROW EXECUTE
PROCEDURE logchange2();
Gavin, > Hi All, I have been tinkering with a function to log the changes made on > any column through a function and trigger. However, I cant think of a way > to make this work through pl/pgsql. Any one have any ideas, or is it just > not possible? It could be done, but would be extremely slow and awkward using current PL/ pgsql syntax. You'd have to query the system tables for a list of columns, and then execute a series of dynamic queries. I recommend instead one of the following two approaches: 1) Simply log the whole row of each archived table and don't worry about logging the individual columns, or 2) Use PL/tcl, PL/Pyton, or C where you can select columnns by ordinal position or other dynamic factor. -- -Josh BerkusAglio Database SolutionsSan Francisco
"Gavin" <gavin@grabias.com> writes:
> Hi All, I have been tinkering with a function to log the changes made on
> any column through a function and trigger. However, I cant think of a way
> to make this work through pl/pgsql.
plpgsql won't do it, but you could do it in pltcl, I believe.
Or resort to C ;-)
regards, tom lane
A plpython solution is available in Issue #66 of PostgreSQL GeneralBits. http://www.varlena.com/GeneralBits/66 Let me know if this helps. elein On Mon, Apr 05, 2004 at 01:01:39PM -0400, Gavin wrote: > Hi All, I have been tinkering with a function to log the changes made on > any column through a function and trigger. However, I cant think of a way > to make this work through pl/pgsql. Any one have any ideas, or is it just > not possible? > > SNIP > create or replace function logchange2() returns OPAQUE as ' > DECLARE > columnname record; > c2 VARCHAR(64); > > BEGIN > > /* Cycle through the column names so we can find the changes being made */ > FOR columnname IN SELECT attname FROM pg_attribute, pg_type > WHERE attnum > 0 AND typrelid=attrelid AND typname=''SOMETABLE'' LOOP > > c2 := CAST(columnname.attname AS VARCHAR(64)); > /* here lies the problem. How would I make plpgsql see OLD.columnname in > a dynamic fashion. I know this wont work whats below, but I am just > trying to express what I am trying to do */ > IF ''OLD.'' || c2 != ''NEW.'' || c2 THEN > /* IF CHANGED DO SOMETHING */ > RAISE NOTICE ''Update on column %'', c2; > END IF; > > END LOOP; > > return NULL; > END; > ' > LANGUAGE plpgsql; > > create trigger logchange2 AFTER UPDATE on TABLENAME FOR EACH ROW EXECUTE > PROCEDURE logchange2(); > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings
On Mon, 5 Apr 2004 13:01:39 -0400 (EDT) "Gavin" <gavin@grabias.com> wrote: > Hi All, I have been tinkering with a function to log the changes made on > any column through a function and trigger. However, I cant think of a way > to make this work through pl/pgsql. Any one have any ideas, or is it just > not possible? http://gborg.postgresql.org/project/tablelog/projdisplay.php is this what you are looking for? cu erik -- Erik Thiele