pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedaccess to new/old in dynamic sql for audit table
2+ messages / 2 participants
[nested] [flat]
* access to new/old in dynamic sql for audit table
@ 2005-06-08 15:54 Jay Parker <jbparker@ualr.edu>
0 siblings, 1 reply; 2+ messages in thread
From: Jay Parker @ 2005-06-08 15:54 UTC (permalink / raw)
To: pgsql-sql
I am revisiting the age-old audit table issue, and am curious to see
whether I can get away with not writing custom trigger functions for
every table being audited.
My design has a foo_audit schema for each schema foo containing tables
to be audited, so triggers on foo.bar will insert rows into
foo_audit.bar, which is a clone of foo.bar with some extra columns added.
I would like to have three generic functions for insert/update/delete,
instead of three custom functions for each audited table. The problem,
of course, is handling the different column structures. I suppose I
could go look things up in the catalog and generate dynamic sql based on
the tables structure recorded there, but that seems like way too much
overhead for an audit system.
I tried something like this, but it didn't like the reference to "new"
inside the execute string:
CREATE OR REPLACE FUNCTION
meta.audit_insert_trig() RETURNS trigger SECURITY DEFINER
AS $PROC$
DECLARE
varschema TEXT;
BEGIN
varschema = tg_argv[0];
if varschema is null or char_length(varschema) = 0 then
raise exception 'must create trigger with schema name as arg';
end if;
new.auditrowid := nextval('meta.auditrowid_seq');
execute 'insert into '
|| quote_ident(varschema||'_audit.'||tg_relname)
|| ' select now(),NULL,''I'',new.*';
return new;
END
$PROC$ LANGUAGE plpgsql;
The error I get is: "ERROR: NEW used in query that is not in a rule",
which doesn't seem like an unreasonable limitation. Is there any way to
accomplish this (efficiently) in a generic function, or should I go back
to my previous implementation with dozens of custom trigger functions?
While I've got your attention, I'll toss in some related questions:
- What is the efficiency tradeoff of having a single combined function
with a conditional to detect insert/update/delete, versus having three
specific functions that duplicate some common code but don't have the
conditional?
- Is there an easier way to get the name of the schema associated with a
table from inside a trigger, rather than the trigger argument kludge I
used above?
Thanks,
-jbp
--
Jay Parker - UALR Computing Services - Networks Project Manager
jbparker@ualr.edu - http://www.ualr.edu/jbparker - 501-569-3345
But I have promises to keep, And miles to go before I sleep. -Frost
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: access to new/old in dynamic sql for audit table
@ 2005-06-08 16:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jay Parker <jbparker@ualr.edu>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2005-06-08 16:27 UTC (permalink / raw)
To: Jay Parker <jbparker@ualr.edu>; +Cc: pgsql-sql
Jay Parker <jbparker@ualr.edu> writes:
> I am revisiting the age-old audit table issue, and am curious to see
> whether I can get away with not writing custom trigger functions for
> every table being audited.
You can't do it nohow in plpgsql. I believe it's relatively simple in
pltcl or plperl, though, because those languages don't try to precompile
column references.
Or if you're feeling bold, you could write the trigger in C ;-).
There are some examples that do pretty similar stuff in contrib/spi/.
If performance is a big concern this would be a good plan anyway.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-06-08 16:27 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-08 15:54 access to new/old in dynamic sql for audit table Jay Parker <jbparker@ualr.edu>
2005-06-08 16:27 ` Tom Lane <tgl@sss.pgh.pa.us>
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