Search Postgresql Archives

Programmatic Trigger Create

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I would like to programmatically create audit trail functions which are called by triggers for custom user tables. This will be used for audit trail generation in our application. The user is able to define a custom table. Therefore, I need a custom audit trail function. The audit trail function outputs the row changes in human readable form (e.g., "process name changed from "my process" to "your process"  by user xxxx on host xxx".

The basic steps are as follows:

1. User defines the table
2. Create table via  EXEC SQL EXECUTE IMMEDIATE :my_sql_cmd
3. Programatically constructs the audit trail function for insert/update/delete.
4. Create the function via EXEC SQL EXECUTE IMMEDIATE :my_sql_cmd
5. Create the trigger via EXEC SQL EXECUTE IMMEDIATE :my_sql_cmd

Here is an example in function source which is passed to "EXEC SQL EXECUTE IMMEDIATE" via char*:


2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE FUNCTION name_changes_log()
  RETURNS TRIGGER AS
$BODY$
BEGIN
   IF NEW.name <> OLD.name THEN
       INSERT INTO kids_audit(kids_id,kids_name,modified_on)
       VALUES(OLD.id,OLD.name,now());
   END IF;

   RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

The "execute immediate" succeeds, but the function is not created. The application is connected to the database as a user which has permission to create functions.

I could output the function text to a file and then use "psql" to process the "create trigger", but this seems a bit hacky.

Thanks in advance,
Thomas




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux