Setting variables equal to elements from an Array

cdecarlo <cdecarlo@gmail.com> writes:

I'm new to pl/pgsql and postgres and I need some help with a part of
my function. In the function I loop through a multidemensional array
( [n][3] ), once, while inside the loop, I find the index I want to
work with I would like to set a separate variable equal to the element
in the array at that index ( newVar = array[i] ).

The rest of your message suggests that what you want is not that at all,
but to set the other variable to an array that consists of one or more
elements from the original array. You need to be clearer in your own
mind about which it is you're doing --- an array of one element is
completely different from the element itself.

If you use subscripts that include a colon (:), then the result is a
sub-array and has to be assigned to a variable that's of the array
type. If you don't use a colon then the subscripting operation produces
a single value that's of the array element type, and has to be assigned
to a variable of that type. You have to use the correct number of
subscripts in either case, else you get a NULL, as you found out.

regards, tom lane

On Jan 8, 11:35 am, t...@sss.pgh.pa.us (Tom Lane) wrote:

cdecarlo <cdeca...@gmail.com> writes:

I'm new to pl/pgsql and postgres and I need some help with a part of
my function. In the function I loop through a multidemensional array
( [n][3] ), once, while inside the loop, I find the index I want to
work with I would like to set a separate variableequalto the element
in the array at that index ( newVar = array[i] ).

The rest of your message suggests that what you want is not that at all,
but to set the other variable to an array that consists of one or moreelementsfrom the original array. You need to be clearer in your own
mind about which it is you're doing --- an array of one element is
completely different from the element itself.

If you use subscripts that include a colon (:), then the result is a
sub-array and has to be assigned to a variable that's of the array
type. If you don't use a colon then the subscripting operation produces
a single value that's of the array element type, and has to be assigned
to a variable of that type. You have to use the correct number of
subscripts in either case, else you get a NULL, as you found out.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom,

Maybe it's the combination of it being early in the day and that I'm
not much of a morning person lately, but your reply really pissed me
off. It came across as though you were 'helping' me from your
sanctimonious, holier-than-thou high horse, which, and I hope this
comes across as being clear in your mind, doesn't help.

Maybe, an example will help you understand what I want to do:

Let myArray be {{1,2,3},{4,5,6},{7,8,9}} and suppose the element I'm
looking for has, in it's first index, an even number. I would loop
through myArray looking at the first index of each element and when I
got to index 2 of myArray I would have found an element which meets
that criteria. How would I set myVar equal to the second element of
myArray?

When i is 2, does myVar := myArray[i] set myVar equal to {4,5,6}?

OR

When i is 2, does myVar := myArray[i][3:3] set myVar equal to {4,5,6}?

OR

Are both incorrect?

-Colin

On Wed, Jan 09, 2008 at 06:14:10AM -0800, cdecarlo wrote:

Maybe, an example will help you understand what I want to do:

Let myArray be {{1,2,3},{4,5,6},{7,8,9}} and suppose the element I'm
looking for has, in it's first index, an even number. I would loop
through myArray looking at the first index of each element and when I
got to index 2 of myArray I would have found an element which meets
that criteria. How would I set myVar equal to the second element of
myArray?

Firstly, a 2-D is not an array of arrays. Think matlab not C.

When i is 2, does myVar := myArray[i] set myVar equal to {4,5,6}?

kleptog=# select '{{1,2,3},{4,5,6},{7,8,9}}'::_int4 as test into temp a;
SELECT
kleptog=# select * from a;
test
---------------------------
{{1,2,3},{4,5,6},{7,8,9}}
(1 row)

kleptog=# select test[2] from a;
test
------

(1 row)

So the answer is no.

OR

When i is 2, does myVar := myArray[i][3:3] set myVar equal to {4,5,6}?

kleptog=# select test[2][3:3] from a;
test
-----------
{{3},{6}}
(1 row)

So no again.

I think what you want is:
kleptog=# select test[2:2][1:3] from a;
test
-----------
{{4,5,6}}
(1 row)

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Jan 9, 2008 8:14 AM, cdecarlo <cdecarlo@gmail.com> wrote:

On Jan 8, 11:35 am, t...@sss.pgh.pa.us (Tom Lane) wrote:

The rest of your message suggests that what you want is not that at all,
but to set the other variable to an array that consists of one or moreelements
from the original array. You need to be clearer in your own
mind about which it is you're doing --- an array of one element is
completely different from the element itself.

If you use subscripts that include a colon (:), then the result is a
sub-array and has to be assigned to a variable that's of the array
type. If you don't use a colon then the subscripting operation produces
a single value that's of the array element type, and has to be assigned
to a variable of that type. You have to use the correct number of
subscripts in either case, else you get a NULL, as you found out.

regards, tom lane

Tom,

Maybe it's the combination of it being early in the day and that I'm
not much of a morning person lately, but your reply really pissed me
off. It came across as though you were 'helping' me from your
sanctimonious, holier-than-thou high horse, which, and I hope this
comes across as being clear in your mind, doesn't help.

Wow, I totally didn't get that from his message... It was short and
to the point. That's all I got.