How to call table returning function with other table

From: Svenne Krap <svenne(at)krap(dot)dk>
To: pgsql-sql(at)postgresql(dot)org
Subject: How to call table returning function with other table
Date: 2006-03-19 13:26:44
Message-ID: 441D5C14.8010204@krap.dk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi.

I have a function that is defined like (which works, sorry for the
pseudocode)
create type xxx as (id,...);
create function calcuate_xxx (integer) returns xxx as $$ select $1,
(select sum(amount) from bigtable where something) as a, (select
sum(amount) from bigtable where some-other-thing) as b $$ language sql
stable;

What I cannot figure out is, if I have another query that returns a
single column of those id's, how do I get a table back with the function
applied to all ids (hence multiple rows).

So it is something like "select xxx(id) from othertable where otherwhere
= 't'" except that it mangles the columns into an array. I have tried to
move the function-call into the fromlist (as it is usually done) but I
seem unsuccessful in getting the ID's into it.

Thanks in advance

Svenne

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Markus Bertheau 2006-03-19 13:32:43 Re: How to call table returning function with other table
Previous Message Bruno Wolff III 2006-03-19 04:56:52 Re: rowcount for all tables