I’m at a loss for how to append a set of values to a two-dimensional array. I’ve tried both the concat operator (||) as well as array_append(). Both seem to work fine when appending a single value to the end of a one-dimensional array, but I’ve spent several hours searching and trying different ways to append.
I’m trying to write my own function that will receive a delimited string, use the STRING_TO_ARRAY() function, for which I subsequently need the positional index of each element. I have not been able to find a BIF that does that, so I’ve resorted to creating my own. So the return value will be a two-dimensional array where the first element is the array index, and the second will be the value. So I’m looping through the array, and trying so append the index and value on each iteration of the loop.
The closest I can find is the article https://popsql.com/learn-sql/postgresql/how-to-modify-arrays-in-postgresql
Where the author states, "You can even add an array to a 2-dimensional array:", by the method,
select array[1, 2] || array[[4, 5],[6, 7]] as concat_2d_array;
My basic block of code to mimic that is:
DECLARE
x int DEFAULT 0;
data_arr varchar[];
return_array varchar[][];
BEGIN
data_arr := string_to_array(IN_ARRAY, IN_DELIMITER, '');
FOR x in array_lower(data_arr, 1)..array_upper(data_arr, 1) LOOP
return_array := ARRAY[return_array] || ARRAY[to_char(x,'fm00000')::varchar, data_arr[x]::varchar];
END LOOP;
RETURN return_array;
That seems to work on the first and second iterations, but by the third, I get ,"PG_EXCEPTION_DETAIL: Arrays of 3 and 1 dimensions are not compatible for concatenation."
Some posts indicate the array elements should be enclosed in curly braces. But that won’t even compile.
There’s gotta be a simple solution. What am I missing?
2
Answers
Thank you. I was finally able to append to my array. I think I was missing the second set of curly brackets, as well as the casting while performing the append.
However, the comment about ORDINALITY intrigued me as it seemed like using built-in functionality would be preferable to building my own. But after playing with it for a while, I realized it may make for some pretty messy code to implement in my particular situation. I am working on a process to transfer data from a UniData database, as exported with field and subfield delimiters. For those not familiar with UniData, it has the concept of multi-value fields (essentially arrays), but which can also have multi-value data. So you have a set (or several sets) of array fields whose only connection is their position within the array. So in order to implement the unnest/ordinality logic, it seems I need to create subqueries for each field to be unnested, with lateral joins to the primary table in every subquery, then joining the data in each subquery by the primary id and the ordinal position returned by each unnest in each of the subqueries.
When you have potentially a dozen fields that all need to be made into subqueries, and performing this across potentially millions of records, it seems the coding could get pretty unruly, and TBD on the performance. I may have to do some load tests to see if the messiness of the code makes up for any potential performance gains.
You obviously can append to a 2D array
But you go on to say you want the index of the array elements (hence 2D).
Although it isn’t clear what you want to do next, this is probably how I would approach the problem.