Do I need to CREATE LANGUAGE plpgsql in PostgreSQL 9+

Question

I have a PHP web application using a PostgreSQL database.

Ideally, to install this web application should be easy and only require a database name and user.

Then, the web application loads an SQL file and imports the tables, indices, and functions.

Functions use the plpgsql language.

PostgreSQL 8.4+

At first, the minimum required PostgreSQL version was 8.4.

In order to avoid the #102 error language "plpgsql" does not exist, I was using this at the top of the SQL file:

--
-- Name: create_language_plpgsql(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION create_language_plpgsql()
RETURNS BOOLEAN AS $$
 CREATE LANGUAGE plpgsql;
 SELECT TRUE;
$$ LANGUAGE SQL;
SELECT CASE WHEN NOT (
 SELECT TRUE AS exists FROM pg_language
 WHERE lanname='plpgsql'
 UNION
 SELECT FALSE AS exists
 ORDER BY exists DESC
 LIMIT 1
) THEN
 create_language_plpgsql()
ELSE
 FALSE
END AS plpgsql_created;
DROP FUNCTION create_language_plpgsql();

This will create the plpgsql language if it does not exist.

PostgreSQL 9+

Now, I want to drop PostgreSQL 8.4 support.

Judging from the wiki: https://wiki.postgresql.org/wiki/CREATE_OR_REPLACE_LANGUAGE

For PostgreSQL 9.0 and newer [...] "CREATE OR REPLACE LANGUAGE" is the native syntax for installing a procedural language with no error if it's already installed.

So I thought I could replace the above SQL code with

CREATE OR REPLACE LANGUAGE plpgsql;

But when I run this SQL query on a database created on cPanel, I have the following error: ERROR: must be owner of language plpgsql.

Indeed, on cPanel, plpgsql exists, but the user is not the owner of the plpgsql language.

Question

Should I rollback to the old SQL code which was working fine on both PostgreSQL 8.4 and 9+?

Judging from this link, https://www.postgresql.org/docs/9.0/plpgsql-overview.html

In PostgreSQL 9.0 and later, PL/pgSQL is installed by default. However it is still a loadable module, so especially security-conscious administrators could choose to remove it.

Or can I be safe without checking if the plpgsql language exists at all? And assume plpgsql will always be available?

• Frank Heikens: Currently, only versions 13+ are supported by the PostgreSQL project. When your code depends on plpgsql, you should at least check for plpgsql: SELECT EXISTS(SELECT FROM pg_extension WHERE extname = 'plpgsql');

Answer 1

An answer could be the following SQL query:

CREATE EXTENSION IF NOT EXISTS plpgsql;

It is available since PostgreSQL 9.1, see https://www.postgresql.org/docs/9.1/sql-createextension.html

As of PostgreSQL 9.1, most procedural languages have been made into "extensions", and should therefore be installed with CREATE EXTENSION not CREATE LANGUAGE.

I have tested it on cPanel, no errors are thrown.