Re: history tables with only one function?

Andreas Haumer wrote:
> -----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:andreas(at)xss(dot)co(dot)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-----
>