Working on upgrading a PostgreSQL 9.6 database to 10 , I am encountering the breaking change that set-returning functions, such as jsonb_object_keys and json_array_elements_text, can no longer be used in CASE expressions.

Where can the definitive list of set-returning functions (including built-ins) be found in PostgreSQL - it doesn't appear that there is a reference in the PostgreSQL documentation, and the result set of SELECT * FROM information_schema.routines does not appear to have an obvious column that indicates 'result type is a set'.

CodePudding user response：

Query the system catalog pg_proc.

select *
from pg_proc
where proretset

According to the documentation the column proretset contains this information.

proretset bool

Function returns a set (i.e., multiple values of the specified data type)

See also PostgreSQL error: CASE with JSON set-returning functions