OK I reread the array documentation and something that didn't stand out before now does :). "By default, the lower bound index value of an array's dimensions is set to one. If any of an array's dimensions has a lower bound index not equal to one, an additional decoration that indicates the actual array dimensions will precede the array structure decoration. This decoration consists of square brackets ([]) around each array dimension's lower and upper bounds, with a colon (:) delimiter character in between. The array dimension decoration is followed by an equal sign (=)." -- 8.10.6. Array Input and Output Syntax. Sorry to have been such a noob, I'll go and do what it says. Cheers, Ged. On 08/02/07, Ged <pgsql4gm@xxxxxxxxx> wrote:
I was on here a couple of days ago with problems getting relational data *into* some array variables (now solved thank you), but now I'm here with problems getting data *out* again. A week or so ago I did a little test page with a little test table pulled from some example about postgres arrays. Everything worked perfectly and you can still see the test page on my dev site at http://www.gedsguides.com/playpen/. So last night I started working with the real app, pulling the data out of the function and supposedly iterating through the array data using exactly the same technique as on the test page. Oh dear! JSP exception! Bad integer! So I switched to one of the text fields and it now displays, but the *first* item out of the array has an array bounds descriptor stuck on the front of it like so: "[0:1]". I had seen this before when looking at the function results in psql, but presumed that this was an artefact of the display process, and that it would go away when iterated through in jdbc. I didn't remember that it *hadn't* been there when looking at the array data in the little test table. You can see the current state of affairs at http://www.gedsguides.com/wow/quests/1. I'm iterating through the quest starters, but just blatting the array out in one for quest enders a.t.m. The only difference I can think of is that the playpen test page is looking at data from a table that has native array columns whereas the function is building arrays on the fly. All the data is in the same database (8.0.8), coming out of the same version of Tomcat (5.5.20) and using the same jdbc driver (dunno where they keep that, it's the ISP's driver not mine). I enclose some selects in psql. You can see that when I select the whole array field there's a descriptor present in the function data that isn't there in the table data. But when I specifically select element 0 the descriptor disappears, so psql is obviously doing something right that the driver isn't :-) : ggw_dev=> select * from sal_emp; name | pay_by_quarter | schedule ------+---------------------------+--------------------------------- Bill | {10000,10000,10000,10000} | {meeting,lunch} Ged | {10000,10000,11000,11000} | {training,presentation,clients} (2 rows) ggw_dev=> select id, name, start_id, start_name from get_quest_page_details(1); id | name | start_id | start_name ----+---------------------+-----------------+----------------------------------- 1 | Uncovering the Past | [0:1]={124,123} | [0:1]={Quae,"Prospector Whelgar"} (1 row) ggw_dev=> select id, name, start_id[0], start_name[0] from get_quest_page_details(1); id | name | start_id | start_name ----+---------------------+----------+------------ 1 | Uncovering the Past | 124 | Quae (1 row) Cheers, Ged.
