On Tue, Aug 16, 2011 at 1:11 PM, Lauri Kajan <lauri.ka...@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
