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

• Philᵀᴹ: You will get more help if you edit your question with a minimal example/test case that demonstrates the problem.
• Whee: @PhilTM you beat me to it - updated question with fiddle

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

• Whee: Thank you (and apologies for the syntax errors — done out of haste while mobile). The quote syntax around the function language is helpful (assumed the fiddle engine just didn’t support plpgsql). While revisiting in light of your answer, I discovered the original error was a blunder in mixing user-defined types. I’ll mark as answer, but will delete the question in a little bit (since a little misleading due to the blunder).
• user1822: @Whee that's fine, go ahead and delete it - less noise ;) Not sure what the function was intended for, but this kind of simple "wrapper" functions should be implemented with language sql as they have substantially less overhead than PL/pgSQL
• Whee: Turns out I can’t delete after all, so my blunder will live on. Note the fiddle was a trivial proof of concept. The real world function is a bit more extensive — great point on using language sql (and will likely prompt a review of some legacy PL/pgSQL functions)