PGSQL GENERAL 44 JOIN ON VIRTUAL TABLE
From: rory@no-spam (Rory Campbell-Lange)
Subject: Join on virtual table
Date: Fri, 10 Dec 2004 22:48:38 +0000


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.

Below is a first attempt. It fails because it looks like one can't have two set returning loops defined in the same function, quite apart from any join oddities joining against 'dayom'.

Thanks! Rory
DROP TYPE dom CASCADE;
CREATE TYPE dom AS ( d INTEGER );

DROP TYPE comb CASCADE;
CREATE TYPE comb AS ( day INTEGER, val INTEGER );

CREATE OR REPLACE FUNCTION fn_test1 ( ) RETURNS setof comb AS '
DECLARE dayom dom%rowtype;
resulter comb%rowtype;
BEGIN FOR i IN 1..30 LOOP dayom.d = i;
RETURN NEXT dayom;
END LOOP;
FOR resulter IN SELECT
dayom.d as day,
recs.nums FROM dayom LEFT OUTER JOIN recs r ON dayom.d = recs.day ORDER BY dayom.d LOOP RETURN NEXT resulter;
END LOOP;

RETURN;
END;'
LANGUAGE plpgsql;

-- Rory Campbell-Lange <rory@no-spam>
<www.campbell-lange.net>

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

From: mail@no-spam (Joe Conway)
Subject: Re: Join on virtual table
Date: Fri, 10 Dec 2004 21:28:48 -0800

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