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!
Best Answer
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.
I would probably go the opposite direction and normalize to some degree.
Normalizing
Normalizing would look something like this,