Postgres Pro
Downloads
Home   >   mailing lists

Thread: Programmatic Trigger Create

Programmatic Trigger Create

From
Niko Ware
Date:
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



Re: Programmatic Trigger Create

From
Adrian Klaver
Date:
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

Re: Programmatic Trigger Create

From
Tom Lane
Date:
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