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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
