Is it possible to declare a serial field in Postgres (9.0) which will increment based on a pattern?
For example:
Pattern: YYYY-XXXXX
where YYYY is a year, and XXXXX increments from 00000 - 99999.
Or should I just use a trigger?
EDIT: I prefer the year to be auto-determined based, maybe, on server date. The XXXXX part does start with 00000 for each year and "resets" to 00000 then increments again to 99999 when the year part is modified.
I would create a separate SEQUENCE for each year, so that each sequence keeps track of one year - even after that year is over, should you need more unique IDs for that year later.
This function does it all:
Improved with input from @Igor and @Clodoaldo in the comments.
CREATE OR REPLACE FUNCTION f_year_id(y text = to_char(now(), 'YYYY'))
RETURNS text AS
$func$
BEGIN
LOOP
BEGIN
RETURN y ||'-'|| to_char(nextval('year_'|| y ||'_seq'), 'FM00000');
EXCEPTION WHEN undefined_table THEN -- error code 42P01
EXECUTE 'CREATE SEQUENCE year_' || y || '_seq MINVALUE 0 START 0';
END;
END LOOP;
END
$func$ LANGUAGE plpgsql VOLATILE;
Call:
SELECT f_year_id();
Returns:
2013-00000
Basically this returns a text of your requested pattern. Automatically tailored for the current year. If a sequence of the name year_<year>_seq does not exist yet, it is created automatically and nextval() is retried.
Note that you cannot have an overloaded function without parameter at the same time (like my previous example), or Postgres will not know which to pick and throw an exception in despair.
Use this function as DEFAULT value in your table definition:
CREATE TABLE tbl (id text DEFAULT f_year_id(), ...)
Or you can get the next value for a year of your choice:
SELECT f_year_id('2012');
Tested in Postgres 9.1. Should work in v9.0 or v9.2 just as well.
To understand what's going on here, read these chapters in the manual:
CREATE FUNCTION
CREATE SEQUENCE
39.6.3. Simple Loops
39.5.4. Executing Dynamic Commands
39.6.6. Trapping Errors
Appendix A. PostgreSQL Error Codes
Table 9-22. Template Pattern Modifiers for Date/Time Formatting
SELECT ? The whole EXECUTE .. RETURN; can be replaced with something like RETURN y ||'-'|| to_char(nextval('year_'|| y ||'_seq'), 'FM00000');
RETURN type to agree with it.
(id text DEFAULT f_year_id(extract(year from now())), ...). Then later he would not need to discover the sequence name nor how to operate it.You can create a function that will form this value (YYYY-XXXXX) and set this function as a default for a column.
Details here.
