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?
SELECT EXISTS(SELECT FROM pg_extension WHERE extname = 'plpgsql');