Conditional DDL?

Every now and then I see people ask the question – how to create table if it doesn't exist yet, how to drop it, but only if it does exist and so on.

Well, starting from 8.2 dropping should be not a problem anymore. But what about create? Alter?

Let's try to do it…

First let's state the goal:

  • we want to have the ability to create object that will not raise exception/rollback in case the object already exists
  • we want to have the ability to alter object (add column to table for example) that will not raise exception/rollback in case the object doesn't exist

Now, having the goal defined, let's do some work.

First thing to be done is function that will make adding conditions possible:

CREATE OR REPLACE FUNCTION EXECUTE(TEXT) RETURNS VOID AS $$
BEGIN EXECUTE $1; END;
$$ LANGUAGE plpgsql STRICT;

Whoa, that was a big piece of code.

What this function does? Well, it basically evaluates given string as SQL. Test:

# SELECT EXECUTE('create table depesz (x int4)');
 EXECUTE
---------
 
(1 ROW)
# \d depesz
    TABLE "public.depesz"
 COLUMN |  TYPE   | Modifiers
--------+---------+-----------
 x      | INTEGER |

OK. Remember that SQL can contain literal new-line-characters, so if your .sql file contains:

CREATE TABLE some_important_table (
    id              SERIAL,
    important_field TEXT NOT NULL DEFAULT '',
    some_date       DATE NOT NULL DEFAULT now(),
    client_id       INT4 NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
);

You can simply add “SELECT execute($$" in the line before, and “$$);" afterwards:

SELECT EXECUTE($$
CREATE TABLE some_important_table (
    id              SERIAL,
    important_field TEXT NOT NULL DEFAULT '',
    some_date       DATE NOT NULL DEFAULT now(),
    client_id       INT4 NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
);
$$);

And it will work.

OK. So, now we have a way to wrap our DDL into function call. But what good it is? It's simple – now I can add “WHERE" to the function call:

# SELECT EXECUTE($$CREATE TABLE xxx (i INT4)$$) WHERE NOT EXISTS (SELECT * FROM information_schema.tables WHERE TABLE_NAME = 'xxx');
 EXECUTE
---------
 
(1 ROW)
# \d xxx
      TABLE "public.xxx"
 COLUMN |  TYPE   | Modifiers
--------+---------+-----------
 i      | INTEGER |
# SELECT EXECUTE($$CREATE TABLE xxx (i INT4)$$) WHERE NOT EXISTS (SELECT * FROM information_schema.tables WHERE TABLE_NAME = 'xxx');
 EXECUTE
---------
(0 ROWS)

You should notice that the second time I called execute – it returned 0 rows. So there is a way to distinguish whether it did the job (create table in this example) or not. But the exception was not raised.

Now, that's pretty cool, but writing these “where not exists (select ..)" gets old pretty fast. So, let's make it a bit simpler. Let's add some helper functions:

CREATE OR REPLACE FUNCTION schema_exists(TEXT) RETURNS bool AS $$
    SELECT EXISTS(SELECT 1 FROM information_schema.schemata WHERE schema_name = $1);
$$ LANGUAGE SQL STRICT;
CREATE OR REPLACE FUNCTION table_exists(TEXT, TEXT) RETURNS bool AS $$
    SELECT EXISTS(SELECT 1 FROM information_schema.tables WHERE (table_schema, TABLE_NAME, table_type) = ($1, $2, 'BASE TABLE'));
$$ LANGUAGE SQL STRICT;
CREATE OR REPLACE FUNCTION view_exists(TEXT, TEXT) RETURNS bool AS $$
    SELECT EXISTS(SELECT 1 FROM information_schema.views WHERE (table_schema, TABLE_NAME) = ($1, $2));
$$ LANGUAGE SQL STRICT;
CREATE OR REPLACE FUNCTION column_exists(TEXT, TEXT, TEXT) RETURNS bool AS $$
    SELECT EXISTS(SELECT 1 FROM information_schema.columns WHERE (table_schema, TABLE_NAME, column_name) = ($1, $2, $3));
$$ LANGUAGE SQL STRICT;

I think the functions don't need big descriptions – what they do is rather simple. So simple, that you can easily modify them to suit your needs better, or simply add new ones if you will find something missing (like index_exists).

And how do I use it now?

# SELECT EXECUTE($$CREATE TABLE yyy (i INT4)$$) WHERE NOT table_exists('public', 'yyy');
 EXECUTE
---------
 
(1 ROW)
# SELECT EXECUTE($$CREATE TABLE yyy (i INT4)$$) WHERE NOT table_exists('public', 'yyy');
 EXECUTE
---------
(0 ROWS)

Of course it's not perfect. Built-in syntax to handle it would be much better, but I think it is pretty acceptable for a quick hack 🙂

7 thoughts on “Conditional DDL?”

  2. @David Fetter:
    Well, I’m not really MySQL user, so it’s hard for me to say it’s something I got from it.

  3. You could also wrap the EXECUTE in a BEGIN / EXCEPTION WHEN OTHERS NULL; END block and call your creates and alters through that.

  4. @wulczer:
    technically – yes. but in this way it would silently ignore other errors as well – like “bad data type”, bad syntax and so on.

  5. OK, so instead of EXCEPTION WHEN OTHERS you’d use WHEN duplicate_table OR duplicate_function OR …

  6. David, so what’s wrong with “NOT NULL DEFAULT ” “?? I don’t get it.

Comments are closed.