Do I need to CREATE LANGUAGE plpgsql in PostgreSQL 9+
1
vote
1
answer
65
views
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?
Asked by François J.
(21 rep)
Mar 26, 2025, 05:42 PM
Last activity: Mar 26, 2025, 07:15 PM
Last activity: Mar 26, 2025, 07:15 PM