PostgreSQL – How to Concatenate Arrays with User-Defined Types in PGSQL

plpgsqlpostgresqlpostgresql-11

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

Best Answer

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

Related Solutions

Postgresql – Array of template type in PL/pgSQL function using %TYPE

The %TYPE construct can only copy the exact type. But there is a simple workaround to register the according array type: Create a (temporary) table or view with the desired array type.

Template table:

CREATE TABLE foo (i int);

(Temporary) view to register the array type, optionally empty (LIMIT 0):

CREATE TEMP VIEW v_foo_i_arr AS
SELECT ARRAY (SELECT i FROM foo LIMIT 0) AS i_arr;

Or a (temporary) table:

CREATE TEMP TABLE foo_i_arr AS
SELECT ARRAY (SELECT i FROM foo LIMIT 0) AS i_arr;

Now your function works:

CREATE OR REPLACE FUNCTION f_arr_test(v_foo_i_arr.i_arr%TYPE)
  RETURNS foo.i%TYPE AS  -- example: return element type
$func$
BEGIN
   RETURN $1[2];
END
$func$  LANGUAGE plpgsql;

You might as well make that a temporary view or table, since the type in the function signature is converted to the underlying type immediately. No persisted connection to the used template. So it's no problem that temp objects are dropped at the end of the session.

Polymorphic type

You might be better off with a polymorphic type to begin with. This works for any array type:

CREATE OR REPLACE FUNCTION f_arr_test_polymorphic(ANYARRAY)
  RETURNS ANYELEMENT AS  -- derived from input type
$func$
BEGIN
   RETURN $1[2];
END
$func$  LANGUAGE plpgsql;

SQL Fiddle.

Note that using a %TYPE declaration inside the function body actually works dynamically in that it looks up the type in the system catalog at the first call of every session.

Postgresql – How to create a custom view (using lookup) from aggregated data

To disaggregate

SELECT id, 
   json_array_elements(array_to_json(string_to_array(csv_user_ids, ',')))
FROM groups

string_to_array converts the CSV string into ARRAY
array_to_json converts ARRAY into JSON
json_array_elements each element into a separate row

next I just need to aggregate again.