Обсуждение: trouble selecting from array

Using 8.3

I have a table which has a column (tdr_tags) defined as integer[][]. The
table description shows the column as integer[]. I understand from the
docs that this is normal ("declaring number of dimensions or sizes in
CREATE TABLE is simply documentation").

If I select the column as tdr_tags I get:

{{161377},{32}}
{{206507},{39}}
{{232972,292831},{45,51}}
...

But, wanting just the 2nd inner array, if I try tdr_tags[2] I get NULL.
In fact, if use any array index at all I get back NULL. What I was
aiming to do was to select into a record in pl/pgsql and loop over the
content of the 2nd array.

Also, is there anything special required to load array data using COPY?
Does it require quoting? If so, is there any reason why pg_dump wouldn't
quote each array?

I've been searching online and haven't come across anything at all
related to this. I've never used arrays in Postgres before and this
particular instance will be a one-off.

brian <brian@zijn-digital.com> writes:
> If I select the column as tdr_tags I get:

> {{161377},{32}}
> {{206507},{39}}
> {{232972,292831},{45,51}}
> ...

> But, wanting just the 2nd inner array, if I try tdr_tags[2] I get NULL.

If you want a sub-array you need to use the slice notation, eg
tdr_tags[2:2][1:2]

            regards, tom lane

Tom Lane wrote:
> brian <brian@zijn-digital.com> writes:
>> If I select the column as tdr_tags I get:
>
>> {{161377},{32}}
>> {{206507},{39}}
>> {{232972,292831},{45,51}}
>> ...
>
>> But, wanting just the 2nd inner array, if I try tdr_tags[2] I get NULL.
>
> If you want a sub-array you need to use the slice notation, eg
> tdr_tags[2:2][1:2]
>


That's precisely it. I'd already tried what Blazej suggested but wanted
the entire 2nd array, not just an element. With your suggestion, the
problem was that I was selecting tdr_tags[2][1:2]. I knew that the lower
bound would be assumed for the first '2' but couldn't figure out how to
do it properly. The solution is obvious.

CREATE OR REPLACE FUNCTION setBusinessTDRs()
RETURNS VOID AS $$
DECLARE

rec RECORD;
t_ids INT[];
b_id INT;

BEGIN
   FOR rec IN
     SELECT tdr_tags[2:2][1:array_upper(tdr_tags, 2)], ...
   LOOP
     t_ids := rec.tdr_tags;

     -- other stuff ...

     FOR i IN 1 .. array_upper(t_ids, 1) LOOP

       INSERT INTO businesses_tdrs (business_id, tdr_id)
       VALUES (b_id, t_ids[1][i]);

     END LOOP;
   END LOOP;
END;
$$ LANGUAGE plpgsql;

Thanks a bunch!

b

On Thu, Mar 27, 2008 at 8:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>  If you want a sub-array you need to use the slice notation, eg
>  tdr_tags[2:2][1:2]

The slice approach is not a general solution...in fact there seems to
be no way to convert an array of N dimensions to N-1 dimensions except
in the special case of N=1.  One side effect of this is that the
_pg_expand_array approaches which I was about to suggest to the OP
only work for one dimensional arrays.

reading the archives, you wrote:
"Because it isn't a slice expression --- you used colon nowhere, so the
result type is going to be text not text[].  (Remember that the parser
must determine the expression's result type at parse time, so whether
there are enough subscripts can't enter into this.)  Our alternatives
here are to throw a subscripting error or return NULL.  I'd personally
have gone with throwing an error, I think, but it seems far too late to
revisit that decision."

is there not enough information available to the parser to reduce the
expression dimensions by one?

merlin

On Fri, Mar 28, 2008 at 2:19 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>  reading the archives, you wrote:
>  "Because it isn't a slice expression --- you used colon nowhere, so the
>  result type is going to be text not text[].  (Remember that the parser
>  must determine the expression's result type at parse time, so whether
>  there are enough subscripts can't enter into this.)  Our alternatives
>  here are to throw a subscripting error or return NULL.  I'd personally
>  have gone with throwing an error, I think, but it seems far too late to
>  revisit that decision."
>
>  is there not enough information available to the parser to reduce the
>  expression dimensions by one?

ugh, I think I see the problem.  You have some function that returns
an int[], which does not have a defined number of dimensions, so:

select func()[] is not known to return an int or an int[] at parse
time, so the path of least resistance was to assume 'int'.   sorry for
the noise.

merlin