Thread: 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