One thing I love about (advanced) databases is that you can write functions. They speed up the query time quite a bit, and you can do fun stuff like IF … ELSE … THEN statements.
In working on getting portage into postgres, part of the problem I’m trying to solve is find out where QA issues are so they can be fixed. Unfortunately, in the early stages of my little script, it always assumes that the everything is working correctly across the board, so I’ll write my queries assuming the foreign keys won’t break. In reality, that doesn’t happen, and I end up killing a transaction with hundreds of thousands of statements because there’s 25 queries that break.
So, I had to write a postgres function to do the checking for me. This is going to be absolutely boring to those db gurus out there, but this is still slightly new to me, so I’m really enjoying it.
DECLARE use_id integer; BEGIN SELECT id FROM use WHERE name = $2 AND (package IS NULL OR package = $3) LIMIT 1 INTO use_id; IF use_id IS NOT NULL THEN INSERT INTO ebuild_use (ebuild, use) VALUES ($1, use_id); RETURN TRUE; ELSE RETURN FALSE; END IF; END;
A really simple function, I know. All it does is run a SELECT statement to see if my foreign key is going to break *before* running the INSERT statement. That way, I can continue on my happy way with my transaction, and at the same time, if I want to turn on ‘qa mode’ when inserting the data, I can check for a false return on the recordset, and know which ebuilds need attention.
Pretty cool stuff, I think. Also, for the record, pgaccess is a *great* little GUI tool to quickly and easily edit your functions.