Обсуждение: How to call table returning function with other table
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
2006/3/19, Svenne Krap <svenne@krap.dk>: > 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. You have to use something like SELECT (xxx(id)).* FROM othertable WHERE otherwhere = 't', I believe. Markus Bertheau
Perfect. Thanks.<br /><br /> Svenne<br /><br /> Markus Bertheau wrote: <blockquote cite="mid684362e10603190532p1903fca8p@mail.gmail.com"type="cite"><pre wrap="">2006/3/19, Svenne Krap <a class="moz-txt-link-rfc2396E"href="mailto:svenne@krap.dk"><svenne@krap.dk></a>: </pre><blockquote type="cite"><pre wrap="">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. </pre></blockquote><pre wrap=""> You have to use something like SELECT (xxx(id)).* FROM othertable WHERE otherwhere = 't', I believe. Markus Bertheau ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypesdo not match </pre></blockquote><br />