Re: join between a table and function.

The easiest way is to put the function in the SELECT list: Note the use of a CTE to avoid executing getAttributes twice - the (result.attrs) refers to the composite typed column and the ".*" expands it into it component parts.

WITH result AS (
SELECT myTable.*, getAttributes(myTable.a) AS attrs
FROM myTable
)
SELECT *, (result.attrs).*
FROM result;

That said, your particular example is a poor candidate for a function (given the limited info you have provided). The first option would be to code a VIEW with "id, class, type" output columns and join that against myTable.

One last minor point; you named the function "getAttributes" but your return signature is not SETOF/TABLE and thus can only ever return a single attribute...

David J.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Lauri Kajan
Sent: Tuesday, August 16, 2011 9:04 AM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 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.ht
> ml
>
>
> 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
>
>
>
>

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