-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi!
Raphael Bauduin wrote:
Raphael Bauduin wrote:
Hi,
I'm looking at the logging of a database we'll put in production soon.
I've seen some posts on this list about history tables, like mentioned
in http://www-106.ibm.com/developerwork...a-dbdsgn2.html .
I think I'll go that way too, but I still have some questions on the
approach, and would appreciate any advice on it.
Here are some questions I have:
- is it possible to write only one function used for all logging
triggers? As illustrated in
http://www.varlena.com/varlena/GeneralBits/57.php , it is possible to use
one function for tracking last update times for all tables:
CREATE OR REPLACE FUNCTION setmodtime() RETURNS TRIGGER AS '
BEGIN
NEW.modified_timestamp = now();
RETURN NEW;
END
' LANGUAGE 'plpgsql';
Is it possible to create only one function to insert rows in the
corresponding history table? The name of the history table can be
constructed from the original table.
and I guess all fields of the table can be retrieved from the db's
metadata. Would that be feasible, and more importantly, would it be
usable?
I found a solution to this one, thanks to a post of Tom Lane on the
postgres-novice mailing list:
CREATE FUNCTION "update_log"(text) RETURNS trigger AS '
BEGIN
insert into $1_log select new.*,''UPDATE'';
return new;
END;
' LANGUAGE 'plpgsql';
I can then create a trigger and pass the table name as argument (does
the function know
which table fired the trigger?):
CREATE TRIGGER "customers_update_log_t" after UPDATE on "customers" for
each row execute procedure "customers_update_log"('customers');
This creates entries in the customers_log table each time I update a
customer.
And this construct indeed works? I'm stunned!
Which PostgreSQL version is this?
As far as I know your function should have the following problems:
*) Trigger functions can not be declared with arguments in the
CREATE FUNCTION statement. They can have arguments when they
are used in the CREATE TRIGGER statement, but trigger functions
have to read the values of their arguments from the array TG_ARGV[]
*) You can not use variables as a placeholder for table- or column-
names in SQL statements. You would have to create the SQL statement
dynamically and execute it in your function with EXECUTE
IMHO this is true at least for PostgreSQL 7.4
See the thread "Trigger functions with dynamic SQL" on pgsql-sql
around July 24th where I described my problems with dynamically
created SQL statements. Finally I got around all the hassels with
quotation marks and my trigger functions work as expected.
Could you please confirm that your function works as you described?
I tried with similar functions and the failed with syntax errors,
so I had to use dynamically created SQL statements.
- - andreas
- --
Andreas Haumer | mailto:an*****@xss.co.at
*x Software + Systeme |
http://www.xss.co.at/
Karmarschgasse 51/2/20 | Tel: +43-1-6060114-0
A-1100 Vienna, Austria | Fax: +43-1-6060114-71
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla -
http://enigmail.mozdev.org
iD8DBQFBGdE4xJmyeGcXPhERAsbZAJ4rS3E8ng3D/Hx/ywsxKM5CVjRd3ACfcdwi
Dt5vUZsSVPbjDfjTMte/MzY=
=RAJ4
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html