-----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/developerworks/web/library/wa-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?
It works as expected:
log=# DROP TRIGGER "customers_update_log_t" on "customers";
DROP TRIGGER
log=# DROP FUNCTION "update_log"();
ERROR: function update_log() does not exist
log=# CREATE FUNCTION "update_log"(text) RETURNS trigger AS '
log'# BEGIN log'# insert into $1_log select new.*,''UPDATE'';
log'# return new;
log'# END;
log'# ' LANGUAGE 'plpgsql';
CREATE FUNCTION
log=# log=# CREATE TRIGGER "customers_update_log_t" after UPDATE on "customers" for each row execute procedure "customers_update_log"('customers');
CREATE TRIGGER
log=# select count(*) from customers_log;
count -------
18
(1 row)
log=# update customers set name='EDITED AND LOOGED GENERIC FUNCTION NEW' where customer_id=20003;
UPDATE 1
log=# select count(*) from customers_log;
count -------
19
(1 row)
And the row added to customers_log is absolutely correct. version is 7.4.3 (debian package)
Raph
PS: I also have a problem of quoting in a trigger (as you seem to have had from the pgsql-sql thread you refered to). I want this to be executed:
EXECUTE ''insert into ''|| TG_RELNAME||''_log select ''||new.*||'',''||TG_OP;
but I get this output:
NOTICE: table = customers
NOTICE: operation = UPDATE
ERROR: NEW used in query that is not in a rule
CONTEXT: PL/pgSQL function "activity_log" line 4 at execute statement
I posted a message to pgsql-novice, but maybe you can help me forward also? Thanks.
I tried with similar functions and the failed with syntax errors, so I had to use dynamically created SQL statements.
- - andreas
- -- Andreas Haumer | mailto:[EMAIL PROTECTED] *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 7: don't forget to increase your free space map settings
