Recently, I was digging in the possibilities of using PostgreSQL’s set_config() and current_setting() functions when I stumbled upon a Stack Overflow question detailing pretty much the research I was doing for myself. My answer was rather elaborate, which is why I’m linking it here to find it back with more ease than through the SO interface.
In MSSQL, I needed to loop over a bunch of orders and execute a stored procedure, except in one case. This does that:
declare @orderid int; DECLARE @RC int declare ordercursor cursor FOR SELECT id FROM [ORDER]; open ordercursor fetch next FROM ordercursor INTO @orderid while @@fetch_status = 0 begin IF @orderid != 429 begin EXECUTE @RC = [DeleteOrderById] @orderid end fetch next FROM ordercursor INTO @orderid; end close ordercursor; deallocate ordercursor;
These are some random new PostgreSQL features that I’m interested in:
The EXECUTE USING feature in PostgreSQL 8.4 will allow the use of more secure dynamic SQL in PL/PgSQL.
User defined exceptions in PostgreSQL 8.4 are a very exciting feature to me. In our unit tests we often check if an exception is properly raised. However, for exception that we raise ourselves, we’ve never been able to check which exception is raised.
Another exciting possibility is that this will allow us to come up with a very clean validation scheme that produces exceptions that we’ll be able to use in the client web GUI.
Enumerated Types will make some of my table definitions slightly clearer.
-- This isn't too shabby (thanks to the CHECK constraint): CREATE TABLE persons ( the_gender CHAR(1) CHECK (gender = 'm' OR gender = 'f') ); -- But, I like this much better: CREATE TYPE gender AS ENUM ('male', 'female'); CREATE TABLE persons ( the_gender gender );
Extra kudos to the PostgreSQL development team for their accelerating pace! 😀
When I clone a production database to a development database, I always need to empty the development database first. I could never find an SQL command to completely purge a database, bur, recently, I learned something about MySQL that surprised me: MySQL doesn’t lose the user rights for a database when you drop it. So, purging a MySQL database (without losing any access data meta data about that DB) is as simple as issuing the following statements:
DROP DATABASE my_development_db; CREATE DATABASE my_development_db;
Happy cloning! 🙂