6

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.

Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
Jeremy Holovacs
  • 1,271
  • 4
  • 20
  • 27

1 Answers1

8

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]
Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633