Re: join between a table and function.

Thanks for every one for help.
I got it to work.

The reason i used a function is that it calculates the
values/attributes from several tables in a pretty complex way. I tried
to do this by a view first but couldn't do it. I think it's
impossible. The function is always supposed to return only one record
with many columns. These columns are used as attributes to the table
rows.

I know that I have a lot to learn in postgresql. Perhaps I someday
figure out a better way to achieve this.

Thanks

-Lauri

On Wed, Aug 17, 2011 at 5:57 AM, David Johnston <polobo(at)yahoo(dot)com> wrote:
> On Aug 16, 2011, at 14:29, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
>> On Tue, Aug 16, 2011 at 8:33 AM, Harald Fuchs <hari(dot)fuchs(at)gmail(dot)com> wrote:
>>> In article <CAKWoFMJWZ3znXCj9rADn4ov+krsa-133968YvAg3L8M3W3zyQQ(at)mail(dot)gmail(dot)com>,
>>> Lauri Kajan <lauri(dot)kajan(at)gmail(dot)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 specifically would the function only be evaluated once for each set of distinct parameters and the resulting records(s) implicitly cached just like the CTE does explicitly?
>
> David J.
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>