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)