Re: Join on virtual table

Rory Campbell-Lange wrote:
> Hi. I'd like to return a result set from a plpgsql function constructed
> out of a 'virtual table' joined to an actual table, and struggling to
> find a sane approach.
>
> I have a table 'recs' with records like this.
>
> day | nums
> -----------
> 2 | 1
> 5 | 3
> 2 | 2.5
>
> For a particular month in the year I would like to generate all the days
> in the month into a virtual table.
>
> 'virt'
>
> vday
> ---
> 1
> ... omitted ...
> 30
>
> I would like a result set something like this:
>
> day | nums
> -----------
> 1 | 0
> 2 | 3.5
> 3 | 0
> 4 | 0
> 5 | 3
> 6 | 0
> ... etc.

You mean like this?

create table recs (day int, nums float);
insert into recs values(2,1);
insert into recs values(5,3);
insert into recs values(2,2.5);

CREATE OR REPLACE FUNCTION generate_series(int, int) RETURNS setof int AS '
BEGIN
FOR i IN $1..$2 LOOP
RETURN NEXT i;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;

select f1, sum(coalesce(nums, 0))
from generate_series(1, 6) as t(f1) left join recs on f1 = day
group by f1;
f1 | sum
----+-----
1 | 0
2 | 3.5
3 | 0
4 | 0
5 | 3
6 | 0
(6 rows)

BTW, as of 8.0.0, generate_series() is built in.

HTH,

Joe