Skip to content

create language if not exist

Posted by Andreas 'ads' Scherbaum on

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 so in consequence this language would 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.

So 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:

----- code -----

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 ();

----- code -----

First i create a helper function which actually will install the language, if called. 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.

  • Twitter
  • Bookmark create language if not exist at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg create language if not exist
  • Bloglines create language if not exist
  • Technorati create language if not exist
  • Fark this: create language if not exist
  • Bookmark create language if not exist at YahooMyWeb
  • Bookmark create language if not exist at Furl.net
  • Bookmark create language if not exist at reddit.com
  • Bookmark create language if not exist at blinklist.com
  • Bookmark create language if not exist at Spurl.net
  • Bookmark create language if not exist at Simpy.com
  • Bookmark create language if not exist at blogmarks
  • Bookmark create language if not exist with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Trackbacks

www.pg-forum.de on : PingBack

Show preview
Comments ()

Comments

Display comments as Linear | Threaded

Bernd Helmle on :

Maybe you've already tried this, but why not simply catching the error within an plpgsql EXCEPTION handler? You don't need to play with the CASE...WHEN statement then.
Comments ()

Andreas Scherbaum on :

Bernd: i'm just trying to install the plpgsql language - no way that i can use a plpgsql function if the language is not yet installed ;-)
Comments ()

Bernd Helmle on :

Aaargh, too early in the morning...need some coffee....forgive my foolness....where's the coffee ???
Comments ()

Magnus on :

So when will we see patch from you implementing an actual IF EXISTS clause in the database for CREATE LANGUAGE? ;-)
Comments ()

Andreas Scherbaum on :

Would be a nice idea, yes. Question is: why did nobody implement this back at the time all the DROP ... IF EXISTS extensions was added?
Comments ()

Andreas Scherbaum on :

I did it! Now let's see, if the patch get's accepted ;-)
Comments ()

Serge on :

Thank you very much
Comments ()

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options