My first PL/PgSQL - ISRC codes

by Derek Sivers

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)
);

1 Comments

merlyn
2005-01-02 15:36:24
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.