Sanitize

Once you've configured data validation rules using constraints , it's safe to say the data in the tables follows a desired format. There are situations however when you want to be a bit more lenient with the format of user input. One option would be to sanitize user input in your frontend before sending the data to the api (and have the database constraints reject invalid input) however that is not always possible or desired.

One such example is storing phone numbers in a strict format but allowing some flexibility for users when entering them.

Let's consider we have the following table definition

create table client (
  id           serial primary key,
  name         text not null,
  phone        text not null,
  check (phone ~* '^\+[0-9]{1,3}[0-9]{4,14}$')
);

Notice that we have a regular expression check on the phone column that does not allow entering numbers with the following formats +49-89-636-48018, +1 (541) 754-3010 but we still would like our users to be able to do so.

The first step would be to create the actual function that formats the phone number.

create function format_phone_number(number text) returns text as $$
begin
    return regexp_replace(number,'[^0-9+]','','g');
end;
$$ language plpgsql immutable ;

The second step is to create a trigger function and call it for each row

create function sanitize_client_model() returns trigger as $$
begin
    new.phone := format_phone_number(new.phone);
    return new;
end;
$$ language plpgsql;

create trigger sanitize_client_model before insert or update on client
for each row execute procedure sanitize_client_model();

And that's all there is to it :)

Info

If you are not a fan PL/pgSQL PostgreSQL has other procedural languages available: PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, PL/V8 (javascript) and may others. Also, plain SQL can be used to write functions