Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 5 down vote accepted

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]
share|improve this answer
    
Thanks; exactly what I was looking for. –  Jeremy Holovacs Feb 24 '13 at 16:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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