Idempotent PostgreSQL DDL scripts

Question

Hi,

I'm looking for a way to script postgreSQL schema changes in an idempotent manner.

In MSSQL I could do something like this:

if(not exists(select * from information_schema.columns where table_name = 'x' and column_name = 'y'))
begin
    alter table x add y int
end
go

PostgreSQL doesn't seem to allow ad-hoc pl/pgsql in the same way MSSQL does with T-SQL so I can't use control structures in a SQL script and run it with psql -f x.sql.

I know PostgreSQL will throw an error if the object already exists but I don't want to have to ignore errors.

I could use some schema versioning technique like dbdeploy but I really like the simplicity of running a set of files through psql without incurring any unwanted side effects.

Is this possible?

Thanks, Mark

Answer 1

you might find this blogpost helpful: http://www.depesz.com/index.php/2008/06/18/conditional-ddl/

Answer 2

You should be able to use plpgsql:

create language plpgsql;
create function f() ... as $$
<plpgsql code>
$$language plpgsql;
select f();