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

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
firstoption 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
onlyever return a single attribute... 

David J.

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Lauri Kajan
Sent: Tuesday, August 16, 2011 9:04 AM
To: pgsql-general@postgresql.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
getan 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.suttraway@enterprisedb.com> wrote:
>
>
> On Tue, Aug 16, 2011 at 1:11 PM, Lauri Kajan <lauri.kajan@gmail.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@postgresql.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@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general