If you’re writing functions in postgres then you’ll probably be using a language such as plpgsql. Let’s say you’re writing a script to to add all of these functions to a new database, but you don’t know whether that language has been created yet. You’ll probably want to do something like
CREATE LANGUAGE IF NOT EXISTS plpgsql. Unfortunately that’s not valid SQL. But doing something similar possible.
The solution is to use SQL to see if the language is already created, and create it if it’s not. This sounds very straight forward but, because SQL is a query language, the solution ends up quite verbose:
CREATE OR REPLACE FUNCTION create_language_plpgsql()
RETURNS BOOLEAN AS $$
CREATE LANGUAGE plpgsql;
$$ LANGUAGE SQL;
SELECT CASE WHEN NOT
SELECT TRUE AS exists
WHERE lanname = 'plpgsql'
SELECT FALSE AS exists
ORDER BY exists DESC
END AS plpgsql_created;
DROP FUNCTION create_language_plpgsql();
That looks pretty complex, but it’s quite simple really. The first part,
create_language_plpgsql(), is the function which creates the language. It’s the simplest way to run the
CREATE_LANGUAGE command from an SQL statement.
The next part, the
CASE statement, does the actual checking. That select statement checks if the language has been created and returns true if it’s there, or false if it’s not. If false, it calls the
create_language_plpgsql() function. This part of the code has been ‘dressed up’ a bit to return
plpgsql_created=TRUE if the language was created, or
FALSE if it was already there. Could be useful for logging, debugging, etc.
The last part simply deletes the function. We don’t need it any more.
And that’s it. If you run this code before setting up your functions you can be sure that plpgsql had been set up regardless of the existing configuration.