Обсуждение: Programmatic Trigger Create
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
On 3/20/21 10:03 AM, Niko Ware wrote: > 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*: In what program is: EXEC SQL EXECUTE IMMEDIATE :my_sql_cmd being done? > > > 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 > > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes: > In what program is: > EXEC SQL EXECUTE IMMEDIATE :my_sql_cmd > being done? Presumably ECPG, which if memory serves defaults to not-auto-commit. Maybe Niko is failing to commit the transaction? If all else fails, turning on log_statements on the server and examining the log might help debug what the program is doing wrong. regards, tom lane