Обсуждение: join between a table and function.

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

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

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

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

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

merlin

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.

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
>