I'm having troube doing a left outer self join on a table. The sent
column shows the number of items sent to each recipient from each
source. The received column (generated by the outer join) is incorrect
because although it is summing the number of messages by recipient,
these need to be filtered by source too.
How can I do a join on two columns in the master table?
Thanks for any help.
Rory
recipient | source | sent | received | outstanding
-----------+--------+------+----------+-------------
22 | 1 | 3 | 2 | 1
23 | 1 | 1 | 1 | 0
25 | 1 | 1 | 2 | -1
25 | 2 | 1 | 2 | -1
26 | 2 | 2 | 0 |
27 | 2 | 3 | 0 |
(6 rows)
----------------------- function definition -----------------------
DROP TYPE dlr_report CASCADE;
CREATE TYPE dlr_report as (
recipient VARCHAR,
source VARCHAR,
sent INTEGER,
received INTEGER,
outstanding INTEGER
);
CREATE OR REPLACE FUNCTION report_on_dlr ()
RETURNS SETOF dlr_report
AS '
DECLARE
resulter dlr_report%rowtype;
BEGIN
FOR resulter IN
SELECT
dd.t_to as recipient,
dd.t_from as source,
count(dd.id) as sent,
CASE
WHEN received_ok is NULL THEN 0
ELSE received_ok
END as received,
count(dd.id) - received_ok as outstanding
FROM
dlr dd
LEFT OUTER JOIN (
SELECT
t_to as target, count(id) as received_ok
FROM
dlr
WHERE
dlr = 1 and t_from = source
GROUP BY
target
) AS ok
ON t_to = ok.target
GROUP BY
dd.t_to, dd.t_from, received, received_ok
ORDER BY
dd.t_to, dd.t_from
LOOP
RETURN NEXT
resulter;
END LOOP;
RETURN;
END;'
LANGUAGE plpgsql;
------------------------- data definition -------------------------
CREATE TABLE dlr (
id serial NOT NULL,
t_to character varying(30),
t_from character varying(30),
dlr smallint
);
COPY dlr (id, t_to, t_from, dlr) FROM stdin;
1 22 1 \N
2 22 1 1
3 22 1 1
4 23 1 1
5 25 1 1
6 25 2 1
7 26 2 \N
8 26 2 0
9 27 2 0
10 27 2 0
11 27 2 0
\.
SELECT pg_catalog.setval ('dlr_id_seq', 11, true);
--
Rory Campbell-Lange
<[EMAIL PROTECTED]>
<www.campbell-lange.net>
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
