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;
    SELECT TRUE;
$$ LANGUAGE SQL;

SELECT CASE WHEN NOT
    (
        SELECT  TRUE AS exists
        FROM    pg_language
        WHERE   lanname = 'plpgsql'
        UNION
        SELECT  FALSE AS exists
        ORDER BY exists DESC
        LIMIT 1
    )
THEN
    create_language_plpgsql()
ELSE
    FALSE
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.




  1. Mephiston says:

    We are going to implement this lines of code in our project (bulmages), to solve our problems with plpgsql and the versions. Thanks for sharing this great query.

  2. Simon Logan says:

    Thanks very much Tim, just what I was looking for.
    Simon

  3. lalo says:

    Or you can try this:

    CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

    because plpgsql is a postgresql extension :)