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@xxxxxxxxxxxxxxxx> wrote: > > > On Tue, Aug 16, 2011 at 1:11 PM, Lauri Kajan <lauri.kajan@xxxxxxxxx> 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@xxxxxxxxxxxxxx) >> 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 > > > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
