From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Joining with result of a plpgsql function |
Date: | 2008-05-08 04:07:19 |
Message-ID: | 20080507205930.Q64098@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Wed, 7 May 2008, Matthew T. O'Connor wrote:
> I have a pl/pgsql function, defined as:
>
> CREATE FUNCTION tms.get_tms_summary(id integer)
> RETURNS tms.tms_summary
>
> get_tms_summary returns a composite type, tms_summary, which is
> comprised of several numerics.
>
> What I would like to do is something like:
>
> select f.id, f.name, tms.get_tms_summary(f.id) from foo f;
>
> However this returns only three columns, the third of which is the
> entire complex data type in one column.
>
> I can do: select * from tms.get_tms_summary(99);
>
> But I would really like to be able to combine it with other data and get
> a result set that looked like:
>
> f.id, f.name, tms_summary.col1, tms_summary.col2 ...
Well I think
select f.id, f.name, (tms.get_tms_summary(f.id)).* from foo f;
would expand it out into separate columns, but I think that might also
call it multiple times. You might have better luck combining that with a
subquery like
select id, name, (summary).col1, (summary).col2, ... from
(select id, name, tms.get_tms_summary(f.id) as summary from foo) f;
From | Date | Subject | |
---|---|---|---|
Next Message | Matthew T. O'Connor | 2008-05-08 05:21:52 | Re: Joining with result of a plpgsql function |
Previous Message | Matthew T. O'Connor | 2008-05-08 03:28:10 | Joining with result of a plpgsql function |