Re: View vs Stored Proc Performance

[Jason Tesser <jasontesser@xxxxxxxxx>]

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@xxxxxxxxx> wrote:

On Fri, Sep 11, 2009 at 2:56 PM, Jason Tesser <jasontesser@xxxxxxxxx> 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