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