Postgres, trim all text field in all table, before insert and before update without trigger

Niki Gagliardi 0
Viewed 25

Is it possible to set the database (PostgreSQL v 14) that for each insert or update on the text fields execute a trim?

This action would save me from creating 2 triggers for each table (trigger before insert and before update) with trim control on all text fields.

trim triggers postgresql
1 Answers

add another generated column. Obviously it will take some storage.

CREATE TABLE test (
    src text,
    trim_src text GENERATED ALWAYS AS (regexp_replace(regexp_replace(src, '^\s+', ''), '\s+$', '')) STORED
);

INSERT INTO test (src)
    VALUES (' hello world   ')
RETURNING
    *;

INSERT INTO test (src)
    VALUES ('   hello world   ')
RETURNING
    *;

SELECT
    *,
    char_length(trim_src)
FROM
    test;

return:

        src        |  trim_src   | char_length
-------------------+-------------+-------------
  hello world      | hello world |          11
    hello world    | hello world |          11
(2 rows)

But you cannot directly update trim_src. you need update src, then trim_src will be automatically updated.

userBct310
Related