Using a multi-valued function in a view

From: Radcon Entec <radconentec(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Using a multi-valued function in a view
Date: 2009-06-04 15:15:52
Message-ID: 16419.93748.qm@web43409.mail.sp1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Greetings!

Having received the answer I needed to my question about using functions with OUT parameters from this list (thanks very much!), I find myself confused about how to use the function in a view.  The function chargeneeds takes one input parameter, a charge number, and has 3 output parameters:  needsfs, needsdrygas and needsbigbase.  When I create a view, I always beginning by running the view's select statement in pgadmin query window.  I tried "select charge, (select * from chargeneeds(charge) from charge", and got a complaint that a subquery can only return one value.  If that is so, then all this effort has been wasted.  I can use my new function in a query like this:

select charge,
        (select needsfs from chargeneeds(charge)) as needsfs,
        (select needsdrygas from chargeneeds(charge)) as needsdrygas,
        (select needsbigbase from chargeneeds(charge)) as needsbigbase
from charge

But on the face of it, this appears to call chargeneeds(charge) three separate times, which not only defeats the purpose of combining the three calculations into one function, but is actually worse, because all three values will be calculated three times.

So should I just go back to separate functions, or is PostgreSQL going to be smart enough to optimize the three calls to chargeneeds() into a single call internally?

Thanks again!

RobR

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2009-06-04 15:17:39 Re: Division by zero
Previous Message Massa, Harald Armin 2009-06-04 15:15:17 Re: Accessing pg_controldata information from SQL