Re: Restart increment to each year = re-invent the - Mailing list pgsql-general

> Bruno Baguette said:
>> I have to design a table wich will store some action reports. Each
>> report have an ID like this 1/2004, 2/2004, ... and each years, they
>> restart to 1 (1/2004, 1/2005, 1/2006,...).
>>
>> So, I was thinking to split that in two fields : one with the
>> increment and one with the year. But I don't know how can I manage
>> the sequences since I have to restart to  each year...
>>
>> Do you think I should re-invent the sequences mecanism with a second
>> table and a stored procedure, only to manage theses 'home-made'
>> sequences ?
>>
>> Or should I create some sequences like myseq_2004, myseq_2004,
>> my_seq_2005,... and use a concatenation of the myseq_ string and the
>> current year when calling nextval and curvall ?
>

I use the "re-invent" option for compound sequences, as follows:

Each employee submits expense reports. Expense reports for each employee
are numbered as YYYY-NN, where "YYYY" is the current year, and "NN" is an
integer starting at one for each year for each employee.

CREATE TABLE employee
(
  employee_pk int4 serial,
  ...
  expense_report_seq int4 DEFAULT 0,
  CONSTRAINT employee_pkey PRIMARY KEY (employee_pk),
);


CREATE TABLE expense
(
  employee_pk int4 NOT NULL,
  expense_report_year int4 NOT NULL,
  expense_report_pk int4 NOT NULL,
  ...
  CONSTRAINT expense_report_pkey PRIMARY KEY (employee_pk,
expense_report_year, expense_report_pk),
  CONSTRAINT expense_fkey FOREIGN KEY (employee_pk) REFERENCES
paid.employee (employee_pk)
);


CREATE OR REPLACE FUNCTION expense_report_next(int4)
  RETURNS int4 AS
'
DECLARE
  l_employee_pk ALIAS FOR $1;
BEGIN
  UPDATE employee
    SET expense_report_seq = (expense_report_seq + 1)
    WHERE employee_pk = l_employee_pk;
RETURN (SELECT expense_report_seq FROM employee  WHERE employee_pk =
l_employee_pk) ;
END;'
  LANGUAGE 'plpgsql' VOLATILE;

/*
NOTE: I'm informed by good sources that the stored procedure defined
above handles concurrency issues correctly because 1) the UPDATE
statment locks the record until a COMMIT is invoked, so the subsequent
SELECT will return YOUR incremented value, not someone else's, and
2) since this procedure is called from within an ON INSERT trigger, it
therefore occurs within a transaction block (which is established
implicitly by the trigger).
*/

CREATE OR REPLACE FUNCTION expense_bit()
  RETURNS trigger AS
'
BEGIN
  IF NEW.expense_report_year IS NULL THEN
    SELECT INTO NEW.expense_report_year date_part(\'year\', current_date);
  END IF;
  IF NEW.expense_report_pk IS NULL THEN
    SELECT INTO NEW.expense_report_pk expense_report_next(new.employee_pk);
  END IF;
  RETURN new;
END;
'
  LANGUAGE 'plpgsql' VOLATILE;


CREATE TRIGGER expense_bit
  BEFORE INSERT
  ON expense
  FOR EACH ROW
  EXECUTE PROCEDURE paid.expense_bit();

-- Resetting the report sub-sequence values for each employee
-- at the start of a new year is left as a student exercise.

--Berend Tober