Is there a function in postgres to return the length of an array field ? I have seen array_dims(array) but this returns a character value. Ideally, I'd like something numeric returned.
Afraid not in 7.3.x or earlier. In 7.3.x (but not earlier) you *can* do this (for a one-dimensional array at least):
SELECT replace(split_part(array_dims(array_fld),':',1),'[','')::int as low FROM tbl;
SELECT replace(split_part(array_dims(array_fld),':',2),']','')::int as high FROM tbl;
In 7.4 (now in beta) there are two new functions, array_lower() and array_upper() that do what you're looking for:
regression=# select array_lower(array_fld, 1) from tbl;
array_lower
-------------
1
(1 row)regression=# select array_upper(array_fld, 1) from tbl;
array_upper
-------------
2
(1 row)See the following links for more on 7.4's array support: http://developer.postgresql.org/docs/postgres/arrays.html http://developer.postgresql.org/docs/postgres/functions-array.html http://developer.postgresql.org/docs/postgres/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
HTH,
Joe
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
