join between a table and function.

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

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:

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 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@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 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.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 specifically would the function only be evaluated once for each set of distinct parameters and the resulting records(s) implicitly cached 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