Re: Array OUT columns in a record returned from a function - part deux - Mailing list pgsql-general

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@gmail.com> 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.
>