pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to call table returning function with other table
3+ messages / 2 participants
[nested] [flat]
* How to call table returning function with other table
@ 2006-03-19 13:26 Svenne Krap <svenne@krap.dk>
0 siblings, 1 reply; 3+ messages in thread
From: Svenne Krap @ 2006-03-19 13:26 UTC (permalink / raw)
To: 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
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.2K) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to call table returning function with other table
@ 2006-03-19 13:32 Markus Bertheau <mbertheau.pg@googlemail.com>
parent: Svenne Krap <svenne@krap.dk>
0 siblings, 1 reply; 3+ messages in thread
From: Markus Bertheau @ 2006-03-19 13:32 UTC (permalink / raw)
To: Svenne Krap <svenne@krap.dk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to call table returning function with other table
@ 2006-03-19 13:39 Svenne Krap <svenne@krap.dk>
parent: Markus Bertheau <mbertheau.pg@googlemail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Svenne Krap @ 2006-03-19 13:39 UTC (permalink / raw)
To: Markus Bertheau <mbertheau.pg@googlemail.com>; +Cc: pgsql-sql
Perfect. Thanks.
Svenne
Markus Bertheau wrote:
> 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
>
> ---------------------------(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 datatypes do not
> match
>
>
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.2K) download
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-03-19 13:39 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-19 13:26 How to call table returning function with other table Svenne Krap <svenne@krap.dk>
2006-03-19 13:32 ` Markus Bertheau <mbertheau.pg@googlemail.com>
2006-03-19 13:39 ` Svenne Krap <svenne@krap.dk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox