Thread: Neat trick to make sure plpgsql is installed as part of a schema

Hey,

    I was puzzling over how to make sure a database has plpgsql
installed in it in pure SQL. I felt this would simplify the schema's
installation process since calling of extra binaries is no longer
neccessary. Here's what I came up with:


CREATE OR REPLACE FUNCTION make_plpgsql () RETURNS bool AS
'
    CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler"; --
    SELECT true; --
' LANGUAGE SQL;

SELECT CASE WHEN
    (SELECT COUNT(oid) > 0 FROM pg_language WHERE lanname = 'plpgsql')
    THEN true ELSE
    (SELECT make_plpgsql())
    END
;


    Cheers,
        Tyler