What is the complete list of "set-returning functions" in PostgreSQL?

Question

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'.

Unrelated, but: I hope you are not upgrading to 10 as that is EOL in about 3 months as well — user330315
– user330315, Commented Aug 18, 2022 at 4:58

klin · Accepted Answer · 2022-08-18 00:12:17Z

1

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

answered Aug 18, 2022 at 0:12

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

What is the complete list of "set-returning functions" in PostgreSQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related