I am using
pgsql to build database tables, and have historically defined the tables in static arrays (eg
ARRAY[ROW(a,b,...)::user_defined_type,...]). With the upgrade to PostgreSQL 11.2, I’d like to manipulate dynamic arrays (specifically, to generate new arrays with a number of elements determined at runtime).
When trying to build the arrays, I have tried to use
|| (for both array-to-array and array-to-element concatenation), as documented in §9.17. While it works with built-in types, it does not work with the user-defined types (created using
CREATE TYPE). The error is:
No [function | operator] matches the given name and argument types. You might need to add explicit type casts.
However, since the function definitions of the aforementioned array functions use
anyelement as their parameters, I’m not certain why the array functions do not operate on the user-defined types (note that the error does show that an array of user-defined types appears where an
anyarray parameter expected and the properly- cast user-defined type appears where an
anyelement should appear).
DB Fiddle here
I have tried to ask the PostgreSQL mail list, but the e-mail server doesn’t seem to like my e-mail address.