Re: join between a table and function.

On Tue, Aug 16, 2011 at 8:33 AM, Harald Fuchs <hari.fuchs@gmail.com> wrote:
> In article <CAKWoFMJWZ3znXCj9rADn4ov+krsa-133968YvAg3L8M3W3zyQQ@mail.gmail.com>,
> Lauri Kajan <lauri.kajan@gmail.com> writes:
>
>> I have also tried:
>> select
>> *, getAttributes(a.id)
>> from
>>   myTable a
>
>> That works almost. I'll get all the fields from myTable, but only a
>> one field from my function type of attributes.
>> myTable.id | myTable.name | getAttributes
>> integer      | character        | attributes
>> 123           | "record name" | (10,20)
>
>> What is the right way of doing this?
>
> If you want the attributes parts in extra columns, use
>
> SELECT *, (getAttributes(a.id)).* FROM myTable a

This is not generally a good way to go.  If the function is volatile,
you will generate many more function calls than you were expecting (at
minimum one per column per row).  The best way to do this IMO is the
CTE method (as david jnoted) or, if and when we get it, 'LATERAL'.

merlin