Re: join between a table and function.

Hi Chetan,

I checked out your link but still can't figure it out.
How could I pass the parameter to the function from another table. If
I try to join or select from the function I'll get an error told that
I cannot refer to other
relations of same query level.

-Lauri

On Tue, Aug 16, 2011 at 12:17 PM, Chetan Suttraway
<chetan(dot)suttraway(at)enterprisedb(dot)com> wrote:
>
>
> On Tue, Aug 16, 2011 at 1:11 PM, Lauri Kajan <lauri(dot)kajan(at)gmail(dot)com> wrote:
>>
>> Hi all,
>>
>> I have made a function returning a custom record type that contains two
>> fields.
>> Now I want to select from that function. Actually I want to make a
>> join with a table.
>>
>> Let me explain.
>>
>> Here is my function:
>> CREATE TYPE attributes AS (class integer, type integer);
>> CREATE OR REPLACE FUNCTION getAttributes(id integer) RETURNS attributes AS
>> $$
>> DECLARE
>>  returnRecord attributes;
>> BEGIN
>>  /*
>>  *
>>  */
>>  RETURN returnRecord;
>> END;
>> $$ LANGUAGE plpgsql;
>>
>> And I want to find attributes for one record in a table using my
>> function that gets a record id as a parameter.
>> I have tried following:
>> select
>>  *
>> from
>>  myTable a,
>>  getAttributes(a.id);
>>
>> I'll get ERROR:  function expression in FROM cannot refer to other
>> relations of same query level.
>> That is pretty obvious.
>>
>> 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?
>>
>>
>> Thanks
>>
>> -Lauri
>>
>> --
>> 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
>
> Are you looking for something similar to table functions?
>
> Please visit "7.2.1.4. Table Functions" section at:
> http://www.postgresql.org/docs/9.0/static/queries-table-expressions.html
>
>
> Regards,
> Chetan
>
> --
> EnterpriseDB Corporation
> The Enterprise PostgreSQL Company
> Website: www.enterprisedb.com
> EnterpriseDB Blog : http://blogs.enterprisedb.com
> Follow us on Twitter : http://www.twitter.com/enterprisedb
>
>
>
>