Join on virtual table - Mailing list pgsql-general

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>