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

Ah ha.

Now I see the reson for the lower bound in dims(...) output, I did not
know that it was possible to expand an array from both ends.

Is there a psudo data type that can be used as a function input type for
any kind of array?

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.

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].

This was going to be the next step of my array_size() functions, but
what I have already works for what I need.

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

 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.

I have hacked together some functions and sql procedures that help me do
some of these things, but I think builtin features would be more
efficient than anything I could do with PL/pgSQL. I have not written any
'C' functions yet, but do write programs in 'C' and 'PHP' that use
string based queries to work with PostgreSQL.

Joe Conway wrote:
...snip...

>
> I agree that the documentation on arrays is pretty limited. Again, I
> hope to improve that before 7.4 is released. In your original question:
>
> > method-1:
> > insert into test ( name , grades) values ( 'user1', '{}');
> > select * from test where name = 'user1';
> > name | id
> > --------+--------
> > user1   | {}
> > update test set grades[1] = 10 where name = 'user1';
> > ERROR:  Invalid array subscripts
>
> I think you need to do this instead if the array is not null, but empty:
>
> update test set grades = '{10}' where name = 'user1';
>
> At that point you can add elements by specifying an index of plus or
> minus one from the array lower bound or upper bound:
>
> regression=# create table test ( name varchar(20) , grades integer[]);
> CREATE TABLE
> regression=# insert into test ( name , grades) values ( 'user1', '{}');
> INSERT 2466139 1
> regression=# update test set grades = '{10}' where name = 'user1';
> UPDATE 1
> regression=# update test set grades[0] = 9 where name = 'user1';
> UPDATE 1
> regression=# update test set grades[-1] = 9 where name = 'user1';
> UPDATE 1
> regression=# update test set grades[2] = 9 where name = 'user1';
> UPDATE 1
> regression=# update test set grades[3] = 9 where name = 'user1';
> UPDATE 1
> regression=# select * from test;
>  name  |    grades
> -------+--------------
>  user1 | {9,9,10,9,9}
> (1 row)
> regression=# select array_dims(grades) from test;
>  array_dims
> ------------
>  [-1:3]
> (1 row)
>
> Arrays default to a lower bound of 1, but you can change that by
> adding elements as shown.
>
...snip...