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(dot)klaver(at)aklaver(dot)com