Get last order number with PostgreSQL trigger

postgresqlIn multi user applications it is not easy to serve a unique order or invoice numbers for each save process. You should check carefully several times on every database insert. But with database triggers you can get last order or invoice number easily as described below.

I have used PostgreSQL 9.3.5 version on Ubuntu for this trigger.

Create order table:

-- order table sequence
DROP SEQUENCE IF EXISTS order_order_id_seq CASCADE;
CREATE SEQUENCE order_order_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

ALTER TABLE public.order_order_id_seq OWNER TO postgres;

-- order table
DROP TABLE IF EXISTS order CASCADE;
CREATE TABLE order (
  order_id bigint DEFAULT nextval('order_order_id_seq'::regclass) NOT NULL,
  order_number bigint,
  order_date timestamp without time zone NOT NULL
  -- add other fields
);

ALTER TABLE public.order OWNER TO postgres;

Create params table. The parameters table has last order number;

-- params table sequence
DROP SEQUENCE IF EXISTS params_params_id_seq CASCADE;
CREATE SEQUENCE params_params_id_seq
  INCREMENT BY 1
  NO MAXVALUE
  NO MINVALUE
  CACHE 1;

ALTER TABLE public.params_params_id_seq OWNER TO postgres;

-- params table
DROP TABLE IF EXISTS params CASCADE;
CREATE TABLE params (
  params_id bigint DEFAULT nextval('params_params_id_seq'::regclass) NOT NULL,
  last_order_number bigint
  -- add other fields
);

ALTER TABLE public.params OWNER TO postgres;

Create trigger:

-- get last order number function
CREATE OR REPLACE FUNCTION get_last_order_number()
    RETURNS TRIGGER AS
$BODY$
DECLARE
    v_last_order_number bigint;
BEGIN
  SELECT last_order_number INTO v_last_order_number FROM params WHERE params_id = 1;
  UPDATE params set last_order_number = v_last_order_number + 1 WHERE params_id = 1;
  NEW.order_number = v_last_order_number + 1;
  RETURN NEW;
END $BODY$
    LANGUAGE plpgsql;

ALTER FUNCTION public.get_last_order_number() OWNER TO postgres;

-- last order number trigger
CREATE TRIGGER last_order_number_trigger BEFORE INSERT ON order
  FOR EACH ROW EXECUTE PROCEDURE get_last_order_number();