postgres - Creating a trigger to update a field with another field in new format

Question

I have a transactions table with a name field that has values like these:

TX-0000001306
TX-0000001307

They are currently created by a server-side script, with the below logic:

var zeros = '0000000000'+(Number(transactionCount) + 1);
return 'TX-'+zeros.substr(zeros.length - 10);

Because I pull the current number of transactions from the database every time I create this number, I no longer want to use it.

I now want to do this with postgres triggers. I have gotten very close to a solution, but I'm unable to figure out how to prepend leading zeros that is being offset by the number. For simplicity, I am using the id field which auto increments.

CREATE OR REPLACE FUNCTION getTransactionName()
RETURNS "trigger" AS
$BODY$
BEGIN
 NEW.name:= CONCAT('TX-', NEW.id);
 Return NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE; 

CREATE TRIGGER createTransactionName
  BEFORE INSERT ON transaction__c
  FOR EACH ROW
  EXECUTE PROCEDURE getTransactionName();

The above trigger produces this:

TX-1701
TX-1702

How can I get it into a 10-digit format?

TX-0000001701
TX-0000001702

score 0 · Accepted Answer · answered Nov 07 '17 at 23:23

Well, I managed to figure it out. The key thing I needed was to_char().

CREATE OR REPLACE FUNCTION getTransactionName()
RETURNS "trigger" AS
$BODY$
BEGIN
 NEW.name:= CONCAT('TX-', to_char(NEW.id, 'fm0000000000'));
 Return NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Thanks to the combined answers:

postgres - Creating a trigger to update a field with another field in new format

1 Answers1