Re: trouble selecting from array

Tom Lane wrote:
> brian <brian(at)zijn-digital(dot)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