Mask to turn integer into string - Postgresql

Question

Good morning!

I am trying to implement a mask on an integer number, whose output will be a string with embedded characters.

I would like the user to enter only 12 integer digits and the database to copy this value automatically to a string:

User enters: 123456789123

Bank copy for field type Character: "12-345.678/91-23"

I have already managed to implement a Trigger associated to a function in the following table:

CREATE TABLE dmlu.teste_mascara3
(
    num numeric(12,0),
    num_proc character(16)
)

Trigger:

CREATE TRIGGER teste_trigger2
    BEFORE INSERT OR UPDATE 
    ON dmlu.teste_mascara3
    FOR EACH ROW
    EXECUTE PROCEDURE dmlu.teste();

Function:

create or replace function dmlu.teste()
returns trigger as $teste$
begin
new.num_proc := to_char(new.num,'99"-"999"."999"/"99"-"99');
return new;
end;
$teste$
language 'plpgsql';

The bank even accepts this syntax, but when it comes to inserting new data, it simply won’t let me save. If I remove the quotes, it can copy, with this syntax:

new.num_proc := to_char(new.num,'999999999999');

That is, the problem is not in the function, but in the mask that I am trying to insert.

How can I insert a dash, dot, and bar mask into an integer number in Postgresql?

Answer 1

The problem is in the size of your field num_proc.

SELECT char_length(to_char(123456789123,'99"-"999"."999"/"99"-"99'));
 char_length 
-------------
          17
(1 registro)

Increase the size of your field to character(17) and retest. Or use text.

Complementing: psql presented the error:

ERRO:  valor é muito longo para tipo character(16)

Of documentation: If no explicit provision is made for a sign in to_char()'s pattern, one column will be reserved for the sign, and it will be anchored to (appear just left of) the number. [https://www.postgresql.org/docs/current/functions-formatting.html]