Re: View vs Stored Proc Performance

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 <mmoncure(at)gmail(dot)com> wrote:

> On Fri, Sep 11, 2009 at 2:56 PM, Jason Tesser <jasontesser(at)gmail(dot)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
>