Postgres Trigger function - Pg 9.1

Question

There is a table on my database that stores a path for video files used by my website. Those files are stored in a SSD disk.

path character varying(255)
id serial

The path will always be like this:

/files/client/\d+/(attachment|user)/\d+/(main|thumbnail)

Here are some examples of valid paths:

/files/client/743052/attachment/2123598/main
/files/client/743052/attachment/2123598/thumbnail
/files/client/1475296/user/3541234/main
/files/client/1475296/user/3541234/thumbnail

Question: How can I create a Trigger that checks the value WHEN INSERTED and, if the row is less than the minimal path (/files/client/\d+/(attachment|user)/\d+/(main|thumbnail)) then raise an exception?

I'm using PostgreSQL 9.1.

UPDATE:

Some more examples here:

/files/client/45345/user/3542341234/main -- PASS
/files/client/45345/user/3542341234/thumbnail -- PASS
/files/client/253623/attachment/35334/main -- PASS
/files/client/253623/attachment/35334/thumbnail -- PASS
/files/client/45312341245/users/12545/main -- WRONG!
/files/client/45312341245/users/12545/thumbnail -- WRONG!
/files/client/45345/attachment/1223545/mains -- WRONG!
/files/client/45345/attachment/1223545/thumbnails -- WRONG!

• Evan Carroll: What does that even mean, "if the row is less than the minimal path" how can that occur?
• Patrick B.: If for some reason the application inserts wrong path, I want the DB to be able to "block" it. Simple like that
• Evan Carroll: What is "the wrong path" how do you have a "less than the minimal path" can you give us an example?
• Patrick B.: @EvanCarroll I've already given it to u: Path should be always /files/client/\d+/(attachment|user)/\d+/(main|thumbnail). Anything different from that is wrong.
• Patrick B.: @EvanCarroll no problem.. please see my latest update

Answer 1

You can simply use a check constraint however I would personally restructure my schema. Essentially what you seem to be doing is serializing a lot of data into a path, and then you want to be sure it's correct. That's kind of yuck, from my perspective.

CREATE TABLE foo (
 id serial,
 path text
 CHECK ( path ~ '/files/client/\d+/(attachment|user)/\d+/(main|thumbnail)' )
);
INSERT INTO foo(path) VALUES 
 ('/files/client/743052/attachment/2123598/main'),
 ('/files/client/743052/attachment/2123598/thumbnail'),
 ('/files/client/1475296/user/3541234/main'),
 ('/files/client/1475296/user/3541234/thumbnail');
INSERT INTO foo(path) VALUES ('/STUD/EVAN/beefcake_hotstuff.jpg');
ERROR: new row for relation "foo" violates check constraint "foo_path_check"
DETAIL: Failing row contains (6, /STUD/EVAN/beefcake_hotstuff.jpg).

I would probably go the opposite direction and normalize to some degree.

Normalizing

Normalizing would look something like this,

CREATE SCHEMA aws;
CREATE TABLE aws.client (
 client_id serial PRIMARY KEY
);
CREATE TABLE aws.attachment (
 client_id int REFERENCES client,
 attachment_id int PRIMARY KEY,
 name text
);
CREATE TABLE aws.user (
 client_id int REFERENCES client,
 user_id int PRIMARY KEY,
 name text
);
SELECT '/files/client' || client_id || '/attachment' || attachment_id || '/' || a.name
FROM aws.attachment AS a
JOIN aws.client AS c USING (client_id);