Thread: using a function

I have a function that's working for what I needed it to do, but now I
need to call it for every id in a different table... and I'm not sure
what the syntax should be.

Here is an example:

create or replace function test(uid integer, out vhrs integer, out phrs
integer, out fhrs integer)
returns setof record as $$
begin
   vhrs := uid + 1;
   phrs := uid + 2;
   fhrs := uid + 3;
   return next;
end;
$$ language 'plpgsql';


I currently use it once, I know the id, and just call:

select * from test(42);

all is well.


But now I need to call it for every record in my employee table.

I tried:

select id, vhrs, phrs, fhrs
from employee, test(id)

I also tried an inner join, but neither work.  Any hints how I might do
this?

Thanks

-Andy

On 1/6/2010 2:45 AM, Filip Rembiałkowski wrote:
> 2010/1/5 Andy Colson <andy@camavision.com <mailto:andy@camavision.com>>
>
>     I have a function that's working for what I needed it to do, but now
>     I need to call it for every id in a different table... and I'm not
>     sure what the syntax should be.
>
>     Here is an example:
>
>     create or replace function test(uid integer, out vhrs integer, out
>     phrs integer, out fhrs integer)
>     returns setof record as $$
>     begin
>       vhrs := uid + 1;
>       phrs := uid + 2;
>       fhrs := uid + 3;
>       return next;
>     end;
>     $$ language 'plpgsql';
>
>
>     I currently use it once, I know the id, and just call:
>
>     select * from test(42);
>
>     all is well.
>
>
>     But now I need to call it for every record in my employee table.
>
>     I tried:
>
>     select id, vhrs, phrs, fhrs
>     from employee, test(id)
>
>     I also tried an inner join, but neither work.  Any hints how I might
>     do this?
>
> # select id, test(id) from ids;
>   id |  test
> ----+---------
>    1 | (2,3,4)
>    2 | (3,4,5)
>    3 | (4,5,6)
> (3 rows)
>
> is this what you want? if not, maybe
> # select id, (select vhrs from test(id)) as vhrs, (select phrs from
> test(id)) as phrs, (select fhrs from test(id)) as fhrs from ids;
>
>
> note: declare your function volatility - see
> http://www.postgresql.org/docs/8.4/static/xfunc-volatility.html
> note: in above example, a VIEW would be enough.
>
>
> --
> Filip Rembiałkowski
> JID,mailto:filip.rembialkowski@gmail.com
> <mailto:filip.rembialkowski@gmail.com>
> http://filip.rembialkowski.net/

Ah, yes.  That works.  I don't like the array output so much, but the
sub-select works.

Thank you.

-Andy