create language if not exist

In a customer project I have to setup a database from a Makefile. Part of my problem: on Windows the installer may or may not install plpgsql into template1, therefore in consequence this language may or may not be activated in every new database. But that’s not predictable. This problem can appear on different Linux/Unix distributions too.

In contrary to some other opinions in #postgresql, I dislike filtering error messages and I prefer clean solutions.

CREATE LANGUAGE will spit an error message, if the language is already activated. A IF NOT EXIST option is not available. I need a workaround:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

CREATE OR REPLACE FUNCTION public.create_plpgsql_language ()
 RETURNS TEXT
 AS $$
 CREATE LANGUAGE plpgsql;

 SELECT 'language plpgsql created'::TEXT;
 $$
LANGUAGE 'sql';

SELECT CASE WHEN
 (SELECT true::BOOLEAN
 FROM pg_language
 WHERE lanname='plpgsql')
 THEN
 (SELECT 'language already installed'::TEXT)
 ELSE
 (SELECT public.create_plpgsql_language())
 END;

DROP FUNCTION public.create_plpgsql_language ();

First I create a helper function which will install the language, if executed. This step is needed because I cannot put the CREATE LANGUAGE statement into the CASE clause. The next statement looks into pg_languages if plpgsql is already installed - if yes, just a message is returned - if no, the previously created function is called. Last but not least the function is dropped, it’s not needed anymore.

I can execute this code from my Makefile and expect to get no error message even if the language already exist.