create language if not exist
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.
Comments
Display comments as Linear | Threaded
Bernd Helmle on :
Andreas Scherbaum on :
Bernd Helmle on :
Magnus on :
Andreas Scherbaum on :
Andreas Scherbaum on :
Serge on :