Re: Restart increment to each year = re-invent the

From: <btober(at)computer(dot)org>
To: <johnsw(at)wardbrook(dot)com>
Cc: <pgsql-ml(at)baguette(dot)net>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: Restart increment to each year = re-invent the
Date: 2004-04-26 13:19:55
Message-ID: 65415.216.238.112.88.1082985595.squirrel@$HOSTNAME
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John Sidney-Woollett 2004-04-26 13:34:23 Re: Restart increment to each year = re-invent the
Previous Message Bill Moran 2004-04-26 13:05:52 Re: Restart increment to 0 each year = re-invent the sequences