PostgreSQL 11 error: column p.proisagg does not exist

Question

Using phpPgAdmin v5.6 and PostgreSQL v11.2 on CentOS v7, when I try to access the Functions tab within the public schema, I get the following error:

ERROR:  column p.proisagg does not exist
LINE 18:    WHERE NOT p.proisagg
                      ^
HINT:  Perhaps you meant to reference the column "p.prolang".
Dans l'instruction :

            SELECT
                p.oid AS prooid,
                p.proname,
                p.proretset,
                pg_catalog.format_type(p.prorettype, NULL) AS proresult,
                pg_catalog.oidvectortypes(p.proargtypes) AS proarguments,
                pl.lanname AS prolanguage,
                pg_catalog.obj_description(p.oid, 'pg_proc') AS procomment,
                p.proname || ' (' || pg_catalog.oidvectortypes(p.proargtypes) || ')' AS proproto,
                CASE WHEN p.proretset THEN 'setof ' ELSE '' END || pg_catalog.format_type(p.prorettype, NULL) AS proreturns,
                u.usename AS proowner
            FROM pg_catalog.pg_proc p
                INNER JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
                INNER JOIN pg_catalog.pg_language pl ON pl.oid = p.prolang
                LEFT JOIN pg_catalog.pg_user u ON u.usesysid = p.proowner
            WHERE NOT p.proisagg
                AND n.nspname = 'public'
            ORDER BY p.proname, proresult

Fatal error: Call to a member function recordCount() on integer in /mnt/webdata/websites/applications/pga/classes/Misc.php on line 1949

I get the same error using the \df meta-command in psql (version 10.1 as it turns out):

ts_d=> \df
ERROR:  column p.proisagg does not exist
LIGNE 6 :   WHEN p.proisagg THEN 'agg'
                 ^
ASTUCE : Perhaps you meant to reference the column "p.prolang".
ts_d=>

The error in those queries seems to be due to a reference to proisagg, a column originally from the pg_proc table in the pg_catalog schema, but which no longer exists in PostgreSQL 11. See: https://www.postgresql.org/docs/11/release-11.html

Any way to resolve this?

Answer 1

In Postgres 11 proisagg was replaced with prokind in the system catalog pg_proc:

prokind | char | f for a normal function, p for a procedure, a for an aggregate function, or w for a window function

The query needs to be adapted. Like:

SELECT ...
FROM pg_catalog.pg_proc p
 ...
WHERE p.prokind = 'f'   -- to only get plain functions
...

Related:

• How to drop all of my functions in PostgreSQL?

• PostgreSQL: How to list all stored functions that access specific table

The error you observed from the \df meta-command in psql is most likely due to using an outdated version of psql. psql 11 and up are updated to deal with this change, of course.

Answer 2

In the case of phpPgAdmin, you must modify the faulty queries within \classes\database\Postgres.php.

pg_proc.proisagg (PostgreSQL 10 and before) is a boolean set at TRUE when function is an aggregate function.

pg_proc.prokind (PostgreSQL 11) is a char taking the values 'f', 'p', 'a' or 'w' (see Erwin's answer above).

Thus, for every occurence of WHERE NOT p.proisagg (function is not an aggregate function), replace with WHERE p.prokind <> 'a'.

Answer 3

I had the same issue and ran these 2 commands to fix it

sed -i "s/NOT pp.proisagg/pp.prokind='f'/g" /usr/share/phpPgAdmin/classes/database/Postgres.php
sed -i "s/NOT p.proisagg/p.prokind='f'/g" /usr/share/phpPgAdmin/classes/database/Postgres.php