0
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?
try "int_to_string", maybe it works
– Felipe Gregio
Which version of Postgresql are you using? In version 9.6 it worked perfectly.
– anonimo