Re: Arrays ... need clarification.... - Mailing list pgsql-general

Guy Fraser wrote:
> Is there a psudo data type that can be used as a function input type for
> any kind of array?

Not really in 7.3.x, but it works in cvs now.

> I have built some functions that take dims output and return an integer
> value {n, where bounds are from 1 to n} of elements in an integer array
> and a text array but if I could make one function that handles any type
> of array, that would be great.

Also in cvs is array_lower(array anyarray, dim int) and
array_upper(array anyarray, dim int), which will give you the '1' and
the 'n' respectively.

> Since you said you are improving the array features, could you make a
> function like dims, that outputs the bounds as a 2D array integers of
> integer sets?
> ie. {{1,3},{1,4}} rather than [1:3][1:4].

Same functions mentioned above:

regression=# SELECT array_lower(ARRAY[[1,2,3],[4,5,6]],1);
  array_lower
-------------
            1
(1 row)

regression=# SELECT array_lower(ARRAY[[1,2,3],[4,5,6]],2);
  array_lower
-------------
            1
(1 row)

regression=# SELECT array_upper(ARRAY[[1,2,3],[4,5,6]],1);
  array_upper
-------------
            2
(1 row)

regression=# SELECT array_upper(ARRAY[[1,2,3],[4,5,6]],2);
  array_upper
-------------
            3
(1 row)

But I guess returning all the array bounds as an array might be a nice
function to have too.

> Some of the other things I would like to see is :
> - the ability to populate an array from a set of data rows

This works in cvs also:

CREATE TEMP TABLE arrtest_f (f0 int, f1 text, f2 float8);
insert into arrtest_f values(1,'cat1',1.21);
insert into arrtest_f values(2,'cat1',1.24);
insert into arrtest_f values(3,'cat1',1.18);
insert into arrtest_f values(4,'cat1',1.26);
insert into arrtest_f values(5,'cat1',1.15);
insert into arrtest_f values(6,'cat2',1.15);
insert into arrtest_f values(7,'cat2',1.26);
insert into arrtest_f values(8,'cat2',1.32);
insert into arrtest_f values(9,'cat2',1.30);
regression=# SELECT ARRAY(select f2 from arrtest_f order by f2) AS "ARRAY";
                      ARRAY
-----------------------------------------------
  {1.15,1.15,1.18,1.21,1.24,1.26,1.26,1.3,1.32}
(1 row)


> - the ability to output an array as a set of data rows


I submitted a function that did this, but it was rejected but its
usefulness is limited by the current table function semantics, i.e. you
can do:

   select * from output_array_as_rows(array_constant);

but there is currently no way to do this (or something equiv):

   select output_array_as_rows(tbl.array_field) from tbl;

The resolution to this issue quickly started to look like a massive
project -- I might take it on one day, but not likely before a 7.4
feature freeze.

>  From these features alone, many new array functions could be possible
> using other standard features.
>
> Most of the arrays I deal with are text arrays, so the PL/R and
> int_array stuff doesn't help me.

You can certainly pass text arrays to PL/R, and R is inherently a vector
(array) processing language. You obviously wouldn't need all the
statistical processing power of R, but I think you'd find it makes most
manipulations or arrays fairly easy.

Joe