Programmatic Trigger Create

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