Re: oracle to postgres

>
> 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.