Search Postgresql Archives

Re: join between a table and function.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux