pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction To Log Changes
5+ messages / 5 participants
[nested] [flat]
* Function To Log Changes
@ 2004-04-05 17:01 Gavin <gavin@grabias.com>
0 siblings, 4 replies; 5+ messages in thread
From: Gavin @ 2004-04-05 17:01 UTC (permalink / raw)
To: pgsql-sql
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();
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function To Log Changes
@ 2004-04-05 17:18 Josh Berkus <josh@agliodbs.com>
parent: Gavin <gavin@grabias.com>
3 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2004-04-05 17:18 UTC (permalink / raw)
To: Gavin <gavin@grabias.com>; pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function To Log Changes
@ 2004-04-05 17:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gavin <gavin@grabias.com>
3 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2004-04-05 17:39 UTC (permalink / raw)
To: Gavin <gavin@grabias.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function To Log Changes
@ 2004-04-06 19:06 elein <elein@varlena.com>
parent: Gavin <gavin@grabias.com>
3 siblings, 0 replies; 5+ messages in thread
From: elein @ 2004-04-06 19:06 UTC (permalink / raw)
To: Gavin <gavin@grabias.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function To Log Changes
@ 2004-04-07 09:42 Erik Thiele <erik@thiele-hydraulik.de>
parent: Gavin <gavin@grabias.com>
3 siblings, 0 replies; 5+ messages in thread
From: Erik Thiele @ 2004-04-07 09:42 UTC (permalink / raw)
To: Gavin <gavin@grabias.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-04-07 09:42 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-05 17:01 Function To Log Changes Gavin <gavin@grabias.com>
2004-04-05 17:18 ` Josh Berkus <josh@agliodbs.com>
2004-04-05 17:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-06 19:06 ` elein <elein@varlena.com>
2004-04-07 09:42 ` Erik Thiele <erik@thiele-hydraulik.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox