Search code examples
postgresqlpg

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

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
Solution

  • 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: