How to insert before update (Custom audit)

Question

I would like execute insert before I update some rows, insert would contain information about which table is updated, which column is updated, what query is executed etc..

And I wrote something like this:

DO $$
DECLARE _new_product_id bigint := 1;
        _update_by_product_id bigint:= 2;

-- BEGIN TRANSACTION
BEGIN

DECLARE product_sql_query text := 'UPDATE products  SET product_id =' + _new_product_id + 'WHERE product_id =' + _update_by_product_id + ';'

INSERT INTO products_audit (ordinal_number, table_name, column_name, action_applied, query_executed, value_before_update, value_after_update, created_date  ) 
                            VALUES (1, 'products', 'product_id', 'UPDATE', users_query, 1, 1, NOW());

-- END TRANSACTION
COMMIT;
END;
$$

But I'm receiving an syntax error which says:

ERROR: syntax error at or near "INSERT" LINE 9: INSERT INTO products_audit (ordinal_number, table...

What's wrong here, I guess product_sql_query value is not correct?

Answer 1

The DECLARE block needs to go before the first BEGIN of the PL/pgSQL - which is not the same as a BEGIN TRANSACTION. You can't control transactions in an anonymous PL/pgSQL block.

It's unclear to me what you intend with the product_sql_query variable, but removing all the syntax errors, the block should look like this:

DO $$
DECLARE 
  _new_product_id       bigint := 1;
  _update_by_product_id bigint:= 2;
  product_sql_query     text;
BEGIN

  product_sql_query := 'UPDATE products  SET product_id = ' || _new_product_id::text ||' WHERE product_id = ' || _update_by_product_id::text;

  INSERT INTO products_audit 
    (ordinal_number, table_name, column_name, action_applied, query_executed, value_before_update, value_after_update, created_date) 
  VALUES 
    (1, 'products', 'product_id', 'UPDATE', users_query, 1, 1, NOW());

END;
$$