Thread: array variables
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.
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.