Sample Header Ad - 728x90

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