Thread: Database Upgrade scripts (AKA Conditional SQL execution)

Moving to Postgres from MS SQL server wherever I can.  When writing
database upgrade scripts, it is nice to construct the script so it will
run correctly even if run twice.  In MS-SQL's TSQL I would do something
like this:

IF( SELECT COUNT(*) FROM sysobjects WHERE name = 'foo' AND type ='U' ) <
1 THENCREATE TABLE foo()
END

Can I accomplish this with postgresql without involving an external
process (like say perl)?  I guess I could put the upgrade stuff into
PL/SQL functions and just drop the functions when I'm done, but I was
hoping for something a little "cleaner".

--
Richard Rowell <richard@bowmansystems.com>

Richard Rowell wrote:
> Can I accomplish this with postgresql without involving an external
> process (like say perl)?  I guess I could put the upgrade stuff into
> PL/SQL functions and just drop the functions when I'm done, but I was
> hoping for something a little "cleaner".
> 

There is no way (currently at least) to do this without some kind of 
function. I've done it in the past with PL/pgSQL. We've had brief 
discussions in the past about how this could be supported, but nothing 
conclusive, and no one has cared enough to scratch this particular itch.

Joe