> > BEGIN > EXECUTE IMMEDIATE 'DROP TABLE CONTAINER'; > EXCEPTION > WHEN OTHERS THEN > IF SQLCODE != -942 THEN > RAISE; > END IF; > END; >
Jim nailed it. In PostgreSQL, this is just DROP TABLE IF EXISTS CONTAINER; One line. No dynamic SQL, exception block, or even a block at all. It also issues a notice when the table does not exist, so if you're watching the messages, you'll know what happened. On top of that, you don't have the weird edge cases to think about that even make using an EXCEPTION block for conditional DROP a good idea in Oracle. The reason you use an EXCEPTION block in Oracle is because 1) there's no atomic way of both checking for existence and performing the drop, and 2) because DDL always emits an implicit commit, checking for existence and then DROPping creates a race condition if you could have multiple sessions performing the DROP. (Of course, I've always questioned the wisdom of setting your system up where you could have multiple sessions doing that at the same time.) In PostgreSQL, both these problems are solved. In PG, DDL does *not* automatically emit a commit. DDL happens inside a transaction (assuming a transaction is initialized). This means you can perform multiple DDL operations in a single transaction and even roll them back. And for a single command that does both, you have the IF EXISTS option. I love PostgreSQL. It makes the simple things simple, and the hard things possible and easier.
