Re: history tables with only one function?

-----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?
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@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-----