Multidimension Arrays
Picking up where I left off on yesterday’s post on PostgreSQL arrays, you can also write multidimensional arrays provided all the nested arrays are equal in size. You can’t use the CARDINALITY function to determine the length of nested arrays, you must use the ARRAY_LENGTH to determine the length of subordinate arrays.
Here’s an example file with a multidimensional array of integers:
DO $$ DECLARE /* Declare an array of integers with a subordinate array of integers. */ list int[][] = array[array[1,2,3,4] ,array[1,2,3,4] ,array[1,2,3,4] ,array[1,2,3,4] ,array[1,2,3,4]]; row varchar(20) = ''; BEGIN /* Loop through the first dimension of integers. */ <<Outer>> FOR i IN 1..ARRAY_LENGTH(list,1) LOOP row = ''; /* Loop through the second dimension of integers. */ <<Inner>> FOR j IN 1..ARRAY_LENGTH(list,2) LOOP IF LENGTH(row) = 0 THEN row = row || list[i][j]; ELSE row = row || ',' || list[i][j]; END IF; END LOOP; /* Exit outer loop. */ RAISE NOTICE 'Row [%][%]', i, row; END LOOP; END; $$; |
It prints:
NOTICE: Row [1][1,2,3,4] NOTICE: Row [2][1,2,3,4] NOTICE: Row [3][1,2,3,4] NOTICE: Row [4][1,2,3,4] NOTICE: Row [5][1,2,3,4] DO |
Multidimensional arrays are unique to PostgreSQL but you can have nested lists of tables or varrays inside an Oracle database. Oracle also supports nested lists that are asynchronous.
As always, I hope this helps those trying sort out the syntax.
Subscribe via RSS