PostgreSQL 11 error: column p.proisagg does not exist

centos-7functionspostgresql

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?

Best Answer

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:

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.

Related Question