5

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.

7

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]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.