Обсуждение: Setting variables equal to elements from an Array

Hello,

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

I tried to accomplish this with the following bit of code:

newVar := mnArray[i]

but for some reason that assigned NULL to newVar, then I tried:

newVar := mnArray[i][3:3]

and that appeared to have worked, however when I try to index an
element in the newVar array it produces NULL.  I'm wondering two
things, first, does mnArray[i][3:3] return an array or a string
representation of the element at that index (which in this case is an
array)? and secondly, how do I do what I want to do (make newVar an
array)?

Thanks,

Colin

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
beclearer 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/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy

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.