PostgreSQL 8.4: How to tell if a procedural language is installed or not

functionsplpgsqlpostgresqlpostgresql-8.4postgresql-extensions

I have a setup program that requires plpgsql to install stored procedures in a PostgreSQL 8.4 database. I need to make sure the language is installed or the app will fail. I don't want to drop the language and re-add it, as that could screw some other things up.

Is there a way to install the language "gently"?
CREATE LANGUAGE IF NOT EXISTS does not appear to be valid.

Best Answer

In PostgreSQL 9.0 and later, PL/pgSQL is pre-installed by default.
Version 9.0 also introduced CREATE OR REPLACE LANGUAGE:

CREATE OR REPLACE LANGUAGE will either create a new language, or replace an existing definition. If the language already exists, its parameters are updated according to the values specified or taken from pg_pltemplate ...

To avoid raising an exception on older versions you can check the catalog table pg_language. I quote the manual once more:

The system catalog pg_language [...] records information about the currently installed languages.

SELECT EXISTS (
  SELECT 1
  FROM   pg_language
  WHERE  lanname = 'plpgsql');

Or use the client application createlang, which has an option to check for existing languages:

createlang -l [connection parameters]
Related Question