Re: join between a table and function.

On Aug 16, 2011, at 14:29, Merlin Moncure <mmoncure@gmail.com> wrote:

> 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'.
>

From your statement is it correct to infer that a function defined as "stable" does not exhibit this effect?  More
specificallywould the function only be evaluated once for each set of distinct parameters and the resulting records(s)
implicitlycached just like the CTE does explicitly? 

David J.