Thread: Using a multi-valued function in a view

On Thu, Jun 4, 2009 at 11:15 AM, Radcon Entec <radconentec@yahoo.com> 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

On Thu, Jun 4, 2009 at 4:40 PM, Radcon Entec <radconentec@yahoo.com> wrote:
> Merlin,
>
> Thank you for your reply.  Unfortunately, I was not able to get it to work.
> The SQL statements you gave me generated syntax errors.
>
> I tried to follow your thoughts, though.  I created the following type:
>
> CREATE TYPE chargeneedstype AS
>    (needsfs int2,
>     needsdrygas int2,
>     needsbigbase int2);
> ALTER TYPE chargeneedstype OWNER TO postgres;
> Then, I created a test function:
>
> CREATE OR REPLACE FUNCTION chargeneeds2(chargenumber int4)
>   RETURNS chargeneedstype AS
> $BODY$
> declare
>    CoilID varchar;
>    InventoryRec record;
>    result chargeneedstype;
> begin
>  result.needsFS := 1;
>  result.needsBigBase := 2;
>  result.needsDryGas := 3;
>  return result;
> end;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE;

I made a typo. try:
select charge, (cn).* from (select chargeneeds(charge) as cn from charge) q;

I think usually it's better to use 'out' parameters to functions than
to create a composite type.

merlin