Thread: Joining with result of a plpgsql function

Hello,

I have a pl/pgsql function, defined as:

CREATE FUNCTION tms.get_tms_summary(id integer)  RETURNS tms.tms_summary

get_tms_summary returns a composite type, tms_summary, which is 
comprised of several numerics.

What I would like to do is something like:

select f.id, f.name, tms.get_tms_summary(f.id) from foo f;

However this returns only three columns, the third of which is the 
entire complex data type in one column.

I can do: select * from tms.get_tms_summary(99);

But I would really like to be able to combine it with other data and get 
a result set that looked like:

f.id, f.name, tms_summary.col1, tms_summary.col2 ...


Any thoughts or suggestions?


Thank you,

Matthew O'Connor

On Wed, 7 May 2008, Matthew T. O'Connor wrote:

> I have a pl/pgsql function, defined as:
>
> CREATE FUNCTION tms.get_tms_summary(id integer)
>    RETURNS tms.tms_summary
>
> get_tms_summary returns a composite type, tms_summary, which is
> comprised of several numerics.
>
> What I would like to do is something like:
>
> select f.id, f.name, tms.get_tms_summary(f.id) from foo f;
>
> However this returns only three columns, the third of which is the
> entire complex data type in one column.
>
> I can do: select * from tms.get_tms_summary(99);
>
> But I would really like to be able to combine it with other data and get
> a result set that looked like:
>
> f.id, f.name, tms_summary.col1, tms_summary.col2 ...

Well I thinkselect f.id, f.name, (tms.get_tms_summary(f.id)).* from foo f;
would expand it out into separate columns, but I think that might also
call it multiple times.  You might have better luck combining that with a
subquery likeselect id, name, (summary).col1, (summary).col2, ... from (select id, name, tms.get_tms_summary(f.id) as
summaryfrom foo) f;
 

Stephan Szabo wrote:
> On Wed, 7 May 2008, Matthew T. O'Connor wrote:
>   
>> But I would really like to be able to combine it with other data and get
>> a result set that looked like:
>>
>> f.id, f.name, tms_summary.col1, tms_summary.col2 ...
>>     
>
> Well I think
>  select f.id, f.name, (tms.get_tms_summary(f.id)).* from foo f;
> would expand it out into separate columns, but I think that might also
> call it multiple times.  You might have better luck combining that with a
> subquery like
>  select id, name, (summary).col1, (summary).col2, ... from
>   (select id, name, tms.get_tms_summary(f.id) as summary from foo) f;

Ah, I knew there was an easy way to do it, I totally forgot / missed / 
didn't know about the (composite type).* syntax.

Thank you!