Array OUT columns in a record returned from a function - part deux

From: Ged <pgsql4gm(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Array OUT columns in a record returned from a function - part deux
Date: 2007-02-08 10:17:46
Message-ID: 65ca86740702080217y335273aay51d9c1b80ac1ea5d@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Fuhr 2007-02-08 10:18:43 Re: temp tables in functions?
Previous Message Malcolm McLean 2007-02-08 09:45:38 Re: Persistent dead rows