CREATE OR REPLACE LANGUAGE

Snippets

CREATE OR REPLACE LANGUAGE

Works with PostgreSQL

any

Written in

SQL

Depends on

Nothing

For PostgreSQL 9.0 and newer

In current releases of PostgreSQL "CREATE OR REPLACE LANGUAGE" is the native syntax for installing a procedural language with no error if it's already installed.

Also PL/pgSQL is installed in the template databases at install time, so will be included in all newly created databases by default, so there's usually no need to install it in schema installation scripts.

For PostgreSQL 8.4 and older

While there is no CREATE OR REPLACE for languages like there are for functions, you can simulate one for the common case where you want to add the pl/pgsql language to a database. Normally this will trigger an ERROR condition if the language is already installed:

$ psql -c "CREATE LANGUAGE plpgsql"
ERROR:  language "plpgsql" already exists

But the following snippet will add the language only if it doesn't already exist:

CREATE OR REPLACE FUNCTION make_plpgsql()
RETURNS VOID
LANGUAGE SQL
AS $$
CREATE LANGUAGE plpgsql;
$$;

SELECT
    CASE
    WHEN EXISTS(
        SELECT 1
        FROM pg_catalog.pg_language
        WHERE lanname='plpgsql'
    )
    THEN NULL
    ELSE make_plpgsql() END;

DROP FUNCTION make_plpgsql();

You can run this multiple times and it will never produce the error shown above. The DROP FUNCTION at the end is optional, if you want to re-use this snippet in other code you might keep the function around to be referenced later.

• Original code by David Fetter