Thread: Join on virtual table

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@campbell-lange.net>
<www.campbell-lange.net>

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