My first PL/PgSQL - ISRC codes
by Derek Sivers
I just made my first PL/PgSQL - it wasn't so hard! This one auto-generates ISRC codes for songs.
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
2 characters for the current year (05)
3 characters for our company code (PROBLEM! see below)
5 characters for a unique serial-ascending integer
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 '
SELECT INTO current_code, current_number code, number FROM isrcs ORDER BY id DESC LIMIT 1;
IF current_code IS NULL THEN
IF current_number = 9999 THEN
IF current_code = ''hm2'' THEN
current_code := ''hm8'';
ELSIF current_code = ''hm8'' THEN
current_code := ''hm9'';
SELECT INTO current_number setval(''isrcs_number_seq'', 1, false);
' LANGUAGE plpgsql;
-- USAGE: INSERT INTO isrcs (song_id) VALUES (12345);
-- It auto-creates the rest.
CREATE TABLE isrcs (
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)
Should have been an "on create" trigger
You've got some binding between the default values and the function. It'd be better to just have an "on create" trigger for your table, where if you provide NULL values, you stuff it with the next entries. That'd make more sense to me.