Right what I was wondering is is this better done in a view? or a stored proc? I am guessing based on your initial response the view is better performance. These are the types of queries I will be doing though.
On Fri, Sep 11, 2009 at 5:01 PM, Merlin Moncure <mmonc...@gmail.com> wrote: > On Fri, Sep 11, 2009 at 2:56 PM, Jason Tesser <jasontes...@gmail.com> > wrote: > > OK so in my case I have a Person, Email, Phone and Address table. I want > to > > return the Person and an Array of the others. so my return type would be > > something like Person, Email[], Phone[], Address[] > > > > When passed a personId. > > > > Are you saying this is better in a view. Create a view that can return > that > > as oppessed to 1. defining a type for a function to return or 2. a > function > > that returns 4 out parameters (Person, Address[] ,....) > > if you are using 8.3+ and are wiling to make a composite type: > > create table person_t(email text, phone text, address text); > > select person_id, array_agg((email, phone, address)::person_t) from > person group by 1; > > or, detail fields are in another table: > > select person_id, (select array(select (email, phone, > address)::person_t) from detail where person_id = p.person_id) from > person_t; > > merlin >
