title: Custom auto-increment field in postgresql (Invoice/Order No.)
body: <p><strong>The baseline requirement is to create an order number in the format:</strong></p>
  
  <pre><code>(M)M-SSS
  </code></pre>
  
  <p>Where MM represents the current month and SSSS represents the order sequence for that month. For example 1-002 would represent the second order submitted in January.</p>
  
  <p>Using a TRIGGER I'd like the auto-increment and insert to work transparently.</p>
  
  <p>Unfortunately, it has been a long time since I have touched a stored procedure and this is my first foray into postgresql. Any help pointing in the right direction would be greatly appreciated.</p>
  
  <p><strong>Update: Here's the final implementation using @peterm's code</strong></p>
  
  <pre><code>-- The trigger
  CREATE TRIGGER add_order_number 
     BEFORE INSERT ON orders FOR EACH ROW
     EXECUTE PROCEDURE order_number_update();
  
  -- The trigger function
  CREATE FUNCTION order_number_update() RETURNS TRIGGER AS $$
  DECLARE
      next_order TEXT;
  BEGIN
      -- get the next order number
      SELECT INTO next_order CONCAT(CAST(DATE_PART('MONTH', CURRENT_DATE) AS VARCHAR(2)),
         '-', 
         LPAD(CAST(COALESCE(CAST(RIGHT(MAX(order_number), 3) AS INT), 0) + 1 AS VARCHAR(3)), 3, '0'))
      FROM orders
      WHERE CAST(LEFT(order_number, STRPOS(order_number, '-') - 1) AS INT) = DATE_PART('MONTH', CURRENT_DATE);
  
      -- update the field
      NEW.order_number = next_order;
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  </code></pre>
  
  <p><em>Note: By adding the update as an BEFORE INSERT trigger it functions in a completely transparent and predictable manner, much like a normal SERIAL or BIGSERIAL field would.</em></p>