Re: join between a table and function.

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