Re: array variables

I, too, am trying to get info on arrays and have so far gotten few responses
to my questions. What I have determined from the responses I did get along
with bits of documentation and testing is (as pertains to use as a column
type in a table - I don't know how much is identical in arrays used in other
context):

1. Arrays start at 1 by default but they don't have to.

2. There is really no such thing as a fixed length array. You can define the
column as (foo text[4]) but it's not a problem putting 5, 6, or 4000 elements
into it.

3. References to an element outside the array bounds return NULL. I cannot
find this documented anywhere and am desperately trying to find out if this
is documented and likely to continue. I can live with nulls being returned
in] a "select foo[6] from bar;" type of statement but having the select fail
would be bad.

4. You cannot have SQL nulls "within" the array. In other words "insert into
bar (foo) values ('{,,,,three}'); will give you a bunch of empty strings, not
nulls, in the first position. This appears to be on a back-burner todo list
according to a note in the docs.

5. Similarly, you cannot insert elements not adjacent to the contiguous
current elements in the array like "insert into bar (foo[5])..." works if
there is a foo[4] or a foo[6] but not otherwise. Note: you can therefore also
have foo[0], foo[-1] etc. There is no apparent limitation on where arrays
start and end, just that they must be contiguous.

6. You can get the array dimensions using array_dims(foo) but array_min and
array_max do not exist (yet - on a todo list).

Hope it helps. As I said, this is what I found for array column types. I'm
just starting to play with plpsql so I don't know if the arrays behavior is
the same there.

Cheers,
Steve


On Monday 16 December 2002 8:45 am, James F. wrote:
> I am writing a pl/pgsql function that needs to use an array. I can find
> precious little documentation of the use of arrays, especially in
> postgresql's official documentation. Is there any comprehensive
> documentation for plpgsql syntax (not just arrays but all areas)? For
> example, I have found code examples that use the _arrayvar syntax, but I
> want to know where these programmers figured this out as I can't find it on
> postgresql.org.
>
> In the meanwhile, how do I declare an array of fixed length and then later
> reference it to assign a value to an array item?
>
> James F.