[GENERAL] Neat trick to make sure plpgsql is installed as part of a schema

[Tyler MacDonald]

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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match