Join on virtual table

From: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
To: Postgresql General List <pgsql-general(at)postgresql(dot)org>
Subject: Join on virtual table
Date: 2004-12-10 22:48:38
Message-ID: 20041210224838.GA28195@campbell-lange.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)campbell-lange(dot)net>
<www.campbell-lange.net>

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Nilesh Doshi 2004-12-10 22:49:30 Best practice in postgres
Previous Message Ron St-Pierre 2004-12-10 22:31:48 Re: Reusable database design