Re: join between a table and function. - Mailing list pgsql-general

From Lauri Kajan
Subject Re: join between a table and function.
Date
Msg-id CAKWoFML1t+9h=3kh-8Cbu9os3be=+NXtZT3avA-w2q6gmJcESA@mail.gmail.com
Whole thread Raw
In response to Re: join between a table and function.  (David Johnston <polobo@yahoo.com>)
List pgsql-general
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@yahoo.com> wrote:
> 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.
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

pgsql-general by date:

Previous
From: Craig Ringer
Date:
Subject: Re: Query regarding PostGre database
Next
From: Siva Palanisamy
Date:
Subject: How to access tables using a superuser