Re: 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