Related link: http://www.postgresql.org/docs/7.4/interactive/plpgsql.html

I just made my first PL/PgSQL - it wasn’t so hard! This one auto-generates ISRC codes for songs.

THE SITUATION:
We generate ISRC codes for musicians who request them, and have to make sure that (2) they fit the proper format (2) they are unique

THE FORMAT:
2 characters for the current year (05)
3 characters for our company code (PROBLEM! see below)
5 characters for a unique serial-ascending integer

THE PROBLEM:
Our company generates more than 100,000 ISRC codes a year, so our “company code” of HM2 filled up! They had to assign us a new company code: HM8. Then that filled up! So we got one more: HM9. Hopefully that will last us a while (300,000 songs a year)

So we have to start with HM2, and assign song-digits up to 99999. Then switch it to HM8, reset the counter to 0, and count up to 99999 again. Then again for HM9. I wanted all this to be done in the database automatically, without depending on my PHP or Ruby logic script. So… time to write my first PL/pgSQL script! I had heart it was pretty easy, so just put aside an hour of focused attention.

Here’s how it turned out. (NOTE: this will make more sense if you read the CREATE TABLE below, first, and then the function.)


CREATE OR REPLACE FUNCTION current_isrc_company_code() RETURNS char(3) AS '
DECLARE
current_code char(3);
current_number integer;
BEGIN
SELECT INTO current_code, current_number code, number FROM isrcs ORDER BY id DESC LIMIT 1;
IF current_code IS NULL THEN
return ''hm2'';
END IF;
IF current_number = 9999 THEN
IF current_code = ''hm2'' THEN
current_code := ''hm8'';
ELSIF current_code = ''hm8'' THEN
current_code := ''hm9'';
END IF;
SELECT INTO current_number setval(''isrcs_number_seq'', 1, false);
END IF;
RETURN current_code;
END;
' LANGUAGE plpgsql;



-- USAGE: INSERT INTO isrcs (song_id) VALUES (12345);
-- It auto-creates the rest.
CREATE TABLE isrcs (
id serial,
year char(2) not null DEFAULT SUBSTRING(CURRENT_DATE, 3, 2),
code char(3) not null DEFAULT current_isrc_company_code() CHECK (code='hm2' OR code='hm8' OR code='hm9'),
number serial not null CHECK (number < 10000),
song_id int not null REFERENCES songs(id) ON DELETE CASCADE,
CONSTRAINT unique_isrc PRIMARY KEY(year,code,number)
);