12/31/ would be Y10K problem. :)
Dunno about you but I sorta don't care if somebody has to rewrite my app
8000 years from now. :)
Ian Harding wrote:
Oh yes. I do that a lot for attributes that need a history (last name,
which changes when you get married, etc) It is a bit more complicated
Oh yes. I do that a lot for attributes that need a history (last name,
which changes when you get married, etc) It is a bit more complicated
for queries though, since I use null to indicate an unknown end date
instead of the Y2K problem solution below.
-Ian
>>> William Yu <[EMAIL PROTECTED]>
Have you thought about unifying the audit + the current table and add
from/to datestamps for every record?
Example:
from_dt to_dt value
9/1/2004 9/30/2004 ABC
9/30/2004 10/5/2004 XYZ
10/6/2004 12/31/ 123
This would let you use the following query on the same table whether you
w
Ah, time travel. I don't think it will be quite that easy since if
there was no modification of a record on that day, there will be no data
returned, and if there were several modifications on that day, there
will be several records returned. I think you will need a correlated
subquery for each t
Hi Ian,
I created one audit table for each table in the database just because
that seemed to me to be the sensible thing to do. The reason we want
audit tables is so that we can ask the question: "what was the state of
the database 6 months ago" and the easiest way to answer that question
is with
Hmm. You have an audit_ table for each table that is audited. I chose
to have one big ugly audit table for all audited tables. I wonder which
is more flexible/useful.
Right off the bat I can see that if you add or rename a column you would
need to add or rename a column in your audit_ table a
Hi Tom,
You are probably right that the performance will become an issue. I do
have a working solution using plpgsql, though, so I will at least try it
out for a while.
For anyone who is interested, I created a template file (using the perl
module Template.pm syntax) that works with the perl mod
Scott Cain <[EMAIL PROTECTED]> writes:
> Heck! So much for feeling close. It is somewhat frustrating to me that
> such an obviously useful tool (having and using audit tables) should be
> so difficult to implement.
The only really reasonable way to implement this is as a C function
anyway. I th
Here's what I do... It's not pretty but it works.
create table auditlog (
auditwhen timestamp not null default CURRENT_TIMESTAMP,
auditwhat char(10) not null,
audittable varchar not null,
auditkeyval int not null,
auditfield varchar not null,
oldval
Heck! So much for feeling close. It is somewhat frustrating to me that
such an obviously useful tool (having and using audit tables) should be
so difficult to implement. I thought I had a reasonable chance of doing
it in plpgsql because I've written functions in that before--I have no
idea how t
Scott Cain wrote:
I feel like I am getting very close, but I am still not quite there. I
rewrote the trigger function below to use execute, but now I get the
following error:
ERROR: OLD used in query that is not in a rule
CONTEXT: PL/pgSQL function "audit_update" line 5 at execute statement
It s
I feel like I am getting very close, but I am still not quite there. I
rewrote the trigger function below to use execute, but now I get the
following error:
ERROR: OLD used in query that is not in a rule
CONTEXT: PL/pgSQL function "audit_update" line 5 at execute statement
It seems that I am n
I think you want to EXECUTE that sql so it doesn't get compiled into the
function.
http://www.postgresql.org/docs/7.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
- Ian
>>> Scott Cain <[EMAIL PROTECTED]> 10/14/04 8:01 AM >>>
OK, I've reworked my function and I can now create m
OK, I've reworked my function and I can now create my functions and
triggers; however, when I try to do a test update, I get the following
error:
ERROR: syntax error at or near "$1" at character 14
CONTEXT: PL/pgSQL function "audit_update" line 7 at SQL statement
Which I think corresponds to 'a
Hello,
I am trying to create audit tables for all of the tables in my
database. The function, table and trigger create statements are below.
Apparently, I am not doing it quite right, because I get these messages
when I try to run the create statements below:
CREATE FUNCTION
CREATE FUNCTION
CRE
Scott Cain <[EMAIL PROTECTED]> writes:
> I am trying to create audit tables for all of the tables in my
> database. The function, table and trigger create statements are below.
> Apparently, I am not doing it quite right, because I get these messages
> when I try to run the create statements belo
16 matches
Mail list logo
