PostgreSQL newbie here trying to put together a transactional DDL script to atomically create a database and its schema in Postgres 9.3: in a transaction, create a few tables; if any errors, roll the whole thing back; otherwise commit. I'm having a hard time getting the syntax right, and I suspect my problems might stem from conflating SQL DDL with PL/pgSQL and/or not grokking the transaction semantics.
Long story short, what's the PL/pgSQL boilerplate for a script to do something like this?
My attempts have gone something like
BEGIN; CREATE TABLE IF NOT EXISTS blah ( ... ); -- much DDL EXCEPTION WHEN OTHERS THEN ROLLBACK; -- this, I gather, is PL/pgSQL COMMIT;
\i schema.sql at the psql prompt produces a syntax error at or near
EXCEPTION. OK, so
EXCEPTION must be PL/pgSQL, and all this needs to go in a PL/pgSQL declaration. Let's try again:
Per the grammar at http://www.postgresql.org/docs/9.3/static/plpgsql-structure.html, this looks like:
[ <<label>> ] [ DECLARE declarations ] BEGIN statements END [ label ];
I don't need to name this, it's a one-off provisioning script, not declaring a function I'm going to use again. So skip the declarations and expand
statements to what I had before:
BEGIN BEGIN; CREATE TABLE IF NOT EXISTS blah ( ... ); -- much DDL EXCEPTION WHEN OTHERS THEN ROLLBACK; COMMIT; END;
This blows up in even worse fashion, with syntax errors on
EXCEPTION, and then it keeps running the rest of the script anyways, complaining about not being in a transaction.
What's the misunderstanding I have here?