Accessing NEW.* keys dynamically in trigger in PostgreSQL

Question

How you can access the NEW.* RECORD keys dynamically?

For example here I'm trying to trim spaces:

CREATE OR REPLACE FUNCTION trim_spaces() RETURNS TRIGGER AS
$$
DECLARE
  _a HSTORE = hstore(NEW);
  _k TEXT;
BEGIN

  FOREACH _k IN ARRAY akeys(_a) LOOP
    NEW.[_k] := trim(BOTH ' ' FROM NEW.[_k]);
  END LOOP;

  RETURN NEW;
END $$ language plpgsql;

Then I have for example:

CREATE TRIGGER test_trim_spaces
  BEFORE INSERT OR UPDATE OF 
    txt, some_other_txt -- TEXT NOT NULL cols
  ON test
  FOR EACH ROW
EXECUTE FUNCTION trim_spaces();

Fiddle

PostgreSQL version 15.

Answer 1

For a quick test this seems to work:

CREATE OR REPLACE FUNCTION trim_spaces() RETURNS TRIGGER AS
$$
DECLARE
  _a HSTORE = hstore(NEW);
  _k TEXT;
BEGIN

  FOREACH _k IN ARRAY akeys(_a) LOOP
    _a[_k] := trim(BOTH ' ' FROM _a[_k]);
  END LOOP;

  SELECT * FROM populate_record(NEW, _a) INTO NEW;

  RETURN NEW;
END $$ language plpgsql;