Pls forgive the newbie question, but is there any elegant
way to get retrieve data out of a multidimensional array?
Let's say I'm say I create table called LabRawData:
CREATE TABLE LabRawData (
dataset int8,
datarray float8[][]
);
And then I put some stuff in the table:
INSERT INTO LabRawData(dataset, datarray)
VALUES(1, '{{1,2,3},{4,5,6},{7,8,9}}');
If I want to retrieve the first column from the array, I get
a bunch of braces and quotes:
SELECT datarray[1:3][1] as FOO from LabRawData
WHERE dataset=1;
foo
----------------
{{"1"},{"4"},{"7"}}
(1 row)
I'd prefer not to have to postprocess the data with <insert
your favorite programming language here>, since it would be
nice to have the option to operate on the data later from
within Posgtgres.
A less than elegant way to do this is to use a 1D array and
insert each row separately
CREATE TABLE LabDataInRows (
dataset int8,
rownum int8,
datarray float8[]
);
INSERT INTO LabDataInRows(dataset, rownum, datarray)
VALUES(1, 1, '{1,2,3}');
INSERT INTO LabDataInRows(dataset, rownum, datarray)
VALUES(1, 2, '{4,5,6}');
INSERT INTO LabDataInRows(dataset, rownum, datarray)
VALUES(1, 3, '{7,8,9}');
The output for this is much better:
SELECT datarray[1] as firstcol from LabDataInRows WHERE
dataset=1 ORDER BY rownum;
firstcol
--------------
1
4
7
(3 rows)
Now this looks pretty nice, but it's not very clean for very
large arrays.
Any suggestions? I've looked in the CONTRIB directories for
some of the array functions, but they don't seem to apply
here.
Bill Eaton
bille@npphotonics.com
