On Thu, Jun 4, 2009 at 11:15 AM, Radcon Entec <radconentec@xxxxxxxxx> wrote: > 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? make sure chargeneeds returns a registered composite type. do this by 1) returning an explicit composite type, or 2) out parameters in function definition. then you can have it return the composite type like so: select charge, (cn).* from (select chargeneeds(charge) from charge); don't be tempted to write your query like this: select charge, (select chargeneeds(charge)).* from charge; this will work, but because of the way .* works in postgresql, this will still execute the function three times. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
