join between a table and function.

From: Lauri Kajan <lauri(dot)kajan(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: join between a table and function.
Date: 2011-08-16 07:41:55
Message-ID: CAKWoFMJWZ3znXCj9rADn4ov+krsa-133968YvAg3L8M3W3zyQQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message sad@bestmx.ru 2011-08-16 07:45:59 Re: Using Postgresql as application server
Previous Message Abbas 2011-08-16 06:26:27 Re: idle in transaction process