PostgreSQL Trigger to generically trim fields of a INSERT/UPDATE record

Question

I'm trying to trim INSERT/UPDATE generic records on every table on the DB to remove any whitespace before and after each text value (i.e. change " value " in "value").

I took a look at Generic string trimming trigger for Postgresql, but this works only on INSERT records since ARGV exists only during that event.

What I thought to do was to convert NEW into a jsonb, that now I assume has the form of {"key1":"value1","key2":"value2"}, but I don't know how to cycle through its fields to trim its values.

I plan to use a

SELECT 
    column_name
FROM
    information_schema.columns
WHERE
    table_name = TG_TABLE_NAME AND table_schema = TG_TABLE_SCHEMA AND (data_type = 'text' OR data_type = 'character varying' )

to get only the "key1" (columns) having type string, so the script is something along the lines of

CREATE OR REPLACE FUNCTION a_trigger() RETURNS trigger AS $trim_trigger$ 
DECLARE
    j jsonb = to_jsonb(NEW);
    current_column text;
BEGIN
    FOR current_column IN 
      SELECT 
        column_name
      FROM
        information_schema.columns
      WHERE
        table_name = TG_TABLE_NAME AND table_schema = TG_TABLE_SCHEMA AND (data_type = 'text' OR data_type = 'character varying' )
    LOOP
      IF j ->> current_column IS NOT NULL THEN
        j = jsonb_set(j, j ->> current_column, to_jsonb(regexp_replace(j ->> current_column, '^\s*|\s*$', '', 'g')),false);
      END IF;
    END LOOP;

    NEW = jsonb_populate_record(NEW, j);
    RETURN NEW;
END;
$trim_trigger$ LANGUAGE plpgsql;

I'm having problems using jsonb_set; its signature is "jsonb, text[], jsonb, bool" but j ->> current_column is a text, and maybe I haven't fully understand the 'path' parameter.

What am I missing here?

---

In the end I managed with a

j = jsonb_set(j, concat('{',current_column,'}')::text[], to_jsonb(regexp_replace(j ->> current_column, '^\s*|\s*$', '', 'g')),false);

( I added a cast to text[] )

But it seems a bit dirty to me

Answer 1

Your statement "works only on INSERT records since ARGV exists only during that event" is false. The TG_ARGV[] exists for all triggered events including insert, update, delete and even truncate events, and others. See the following demonstration:

create or replace function argv_test_trigger_function()
 returns trigger
 language plpgsql
 as $$
 declare 
     l_action_msg text;
 begin 
     case tg_op 
          when 'INSERT' then l_action_msg = 'Adding '   || new.description;
          when 'DELETE' then l_action_msg = 'Removing ' || old.description;
          when 'UPDATE' then l_action_msg = 'Updating ' || old.description || ' to ' || new.description;
          else l_action_msg = 'Trunacting???';
     end case;  

     raise notice E'Trigger % fired on % of %, with % parameters value(S) %\n ... Taking action %',
                  tg_name, tg_op, tg_table_name, tg_nargs::text, tg_argv::text,l_action_msg;

     if tg_op = 'DELETE' 
     then 
         return old;
     else 
         return new;
     end if; 
end ; 
$$; 

drop table if exists argv_test cascade;
create table argv_test (id serial, description text);  

drop trigger if exists argv_test_bir on argv_test; 
create trigger argv_test_bir
       before insert on argv_test
       for each row execute procedure  argv_test_trigger_function('adding row.' );  

drop trigger if exists argv_test_bur on argv_test; 
create trigger argv_test_bur
       before update on argv_test
       for each row execute procedure  argv_test_trigger_function('Updating row.' );    

drop trigger if exists argv_test_bdr on argv_test;        
create trigger argv_test_bdr
       before delete on argv_test
       for each row execute procedure  argv_test_trigger_function('Deleting row.', 'That'' All folks');  



insert into argv_test(description) values( 'Initial insert'); 
update argv_test set description = description || ', and now it''s updated';
delete from argv_test;