Re: join of array - Mailing list pgsql-general

Tom Lane wrote:
> That's about as clear as mud :-( ... but I found a clearer statement
> in SQL99 6.31:
>
> 2) If <array concatenation> is specified, then:
>
> a) Let AV1 be the value of <array value expression 1> and let AV2 be
> the value of <array value expression 2>.
>
> b) If either AV1 or AV2 is the null value, then the result of the
> <array concatenate function> is the null value.
>
> c) Otherwise, the result is the array comprising every element of AV1
> followed by every element of AV2.
>
> (c) seems to be pretty clearly what Pavel wants for the 1-D case, but
>  it's not immediately clear how to apply it to multidimensional
> arrays.
>

Thanks -- I found the corresponding paragraph in SQL200x (6.35) and it
pretty much reads the same.

> Probably.  AFAICS this doesn't affect the data copying at all, only
> the way in which the result's dimension values are computed, right?

Looks that way to me.


> Also, we might want to take another look at the rules for selecting
> the lower-bounds of the result array.  In the cases where we're
> joining N+1-D to N-D (including 1-D to scalar) it still seems to make
> sense to preserve the subscripts of the higher-dimensional object, so
> the lower- dimensional one is "pushed" onto one end or the other.

This is mostly the way it currently works:

regression=# create table arr(f1 int[]);
CREATE TABLE
regression=# insert into arr values ('{}');
INSERT 2498103 1
regression=# update arr set f1[-2] = 1;
UPDATE 1
regression=# select array_lower(f1,1) from arr;
  array_lower
-------------
           -2
(1 row)

regression=# select array_lower(f1 || 2, 1) from arr;
  array_lower
-------------
           -2
(1 row)

regression=# select array_lower(0 || f1, 1) from arr;
  array_lower
-------------
           -3
(1 row)
regression=# update arr set f1 = ARRAY[[1,2],[3,4]];
UPDATE 1
regression=# select array_lower(f1,1) from arr;
  array_lower
-------------
            1
(1 row)

regression=# select array_lower(f1 || ARRAY[5,6], 1) from arr;
  array_lower
-------------
            1
(1 row)

regression=# select array_lower(ARRAY[-1,0] || f1, 1) from arr;
  array_lower
-------------
            1
(1 row)


It looks like the only "wrong" case is the last one. Will fix.

> In the N-D to N-D case I can't see any really principled way to do
> it; for lack of a better idea, I suggest preserving the subscripts of
> the lefthand input (ie, using its lower-bound).

OK, will do.

Thanks,

Joe