How to concatenate arrays with user-defined types in pgsql?

Question

Background

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

The Problem

When trying to build the arrays, I have tried to use array_append, array_cat, and || (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 anyarray and 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).

Example

DB Fiddle here

Other Notes

I have tried to ask the PostgreSQL mail list, but the e-mail server doesn’t seem to like my e-mail address.

System

PostgreSQL v11.2

Fedora 29

Answer 1

array_append() works just fine if you use the correct syntax. Your fiddle doesn't work because you used “ instead of " and ‘ instead of '. The language for a function is an identifier and shouldn't be quoted to begin with.

String constants need to be enclosed in single quotes, not double quotes. The expression ROW("a","b")::udt would result in " column "a" does not exist" because "a" is an identifier. You need to use ROW('a','b')::udt to initialize a value of type udt

So the correct function would be:

CREATE OR REPLACE FUNCTION abc() RETURNS text AS $BODY$
DECLARE
  x udt;
  y udt[];
BEGIN
  x := ROW('a','b')::udt;
  return array_append(y,x);
END
$BODY$ LANGUAGE plpgsql
VOLATILE;

And select abc() returns:

abc      
---------
{"(a,b)"}

Working fiddle